時間のギャップを埋め、欠損値を補完する
重要
Azure SQL Edge では、ARM64 プラットフォームがサポートされなくなりました。
時系列データを処理する場合、時系列データの属性に欠損値がある可能性があります。 また、データの性質によって、またはデータ収集が中断しているために、データセットに時間のギャップがある可能性もあります。
たとえば、スマート デバイスのエネルギー使用状況の統計情報を収集する場合、デバイスが動作していないときは常に、使用状況の統計にギャップが生じます。 同様に、マシンの利用統計情報の収集シナリオでは、さまざまなセンサーがさまざまな周波数でデータを出力するように構成されていて、その結果、センサーの値が欠損する可能性があります。 たとえば、電圧と圧力の 2 つのセンサーがあり、それぞれ 100 Hz と 10 Hz の頻度で構成されている場合、電圧センサーは 100 分の 1 秒ごとにデータを出力しますが、圧力センサーは 10 分の 1 秒ごとにのみデータを出力します。
次の表で、1 秒間隔で収集されたマシンの利用統計データセットについて説明します。
timestamp VoltageReading PressureReading
----------------------- --------------- ----------------
2020-09-07 06:14:41.000 164.990400 97.223600
2020-09-07 06:14:42.000 162.241300 93.992800
2020-09-07 06:14:43.000 163.271200 NULL
2020-09-07 06:14:44.000 161.368100 93.403700
2020-09-07 06:14:45.000 NULL NULL
2020-09-07 06:14:46.000 NULL 98.364800
2020-09-07 06:14:49.000 NULL 94.098300
2020-09-07 06:14:51.000 157.695700 103.359100
2020-09-07 06:14:52.000 157.019200 NULL
2020-09-07 06:14:54.000 NULL 95.352000
2020-09-07 06:14:56.000 159.183500 100.748200
上記のデータセットには、2 つの重要な特徴があります。
- このデータセットには、いくつかのタイムスタンプ (
2020-09-07 06:14:47.000、2020-09-07 06:14:48.000、2020-09-07 06:14:50.000、2020-09-07 06:14:53.000、および2020-09-07 06:14:55.000) に関連するデータ ポイントが含まれていません。 これらのタイムスタンプがデータセット内のギャップです。 - 電圧と圧力の測定値に、
nullとして表される欠損値があります。
ギャップを埋める
ギャップを埋めることは、連続した順序付けられたタイムスタンプのセットを作成して、時系列データの分析を容易にするのに役立つ手法です。 Azure SQL Edge では、時系列データセットのギャップを埋める最も簡単な方法は、目的の時間分布を含む一時テーブルを定義し、データセット テーブルで LEFT OUTER JOIN または RIGHT OUTER JOIN 操作を実行することです。
前に示した MachineTelemetry のデータを例にすると、次のクエリを使って、分析用に連続したタイムスタンプの順序付けされたセットを生成できます。
Note
次のクエリでは、不足している行が、属性のタイムスタンプ値と null 値を含めて生成されます。
CREATE TABLE #SeriesGenerate (dt DATETIME PRIMARY KEY CLUSTERED)
GO
DECLARE @startdate DATETIME = '2020-09-07 06:14:41.000',
@endtime DATETIME = '2020-09-07 06:14:56.000'
WHILE (@startdate <= @endtime)
BEGIN
INSERT INTO #SeriesGenerate
VALUES (@startdate)
SET @startdate = DATEADD(SECOND, 1, @startdate)
END
SELECT a.dt AS TIMESTAMP,
b.VoltageReading,
b.PressureReading
FROM #SeriesGenerate a
LEFT JOIN MachineTelemetry b
ON a.dt = b.[timestamp];
上記のクエリでは、指定された範囲内のすべての 1 秒のタイムスタンプを含む次の出力が生成されます。
結果セットは次のとおりです。
timestamp VoltageReading PressureReading
----------------------- ----------------- ----------------
2020-09-07 06:14:41.000 164.990400 97.223600
2020-09-07 06:14:42.000 162.241300 93.992800
2020-09-07 06:14:43.000 163.271200 NULL
2020-09-07 06:14:44.000 161.368100 93.403700
2020-09-07 06:14:45.000 NULL NULL
2020-09-07 06:14:46.000 NULL 98.364800
2020-09-07 06:14:47.000 NULL NULL
2020-09-07 06:14:48.000 NULL NULL
2020-09-07 06:14:49.000 NULL 94.098300
2020-09-07 06:14:50.000 NULL NULL
2020-09-07 06:14:51.000 157.695700 103.359100
2020-09-07 06:14:52.000 157.019200 NULL
2020-09-07 06:14:53.000 NULL NULL
2020-09-07 06:14:54.000 NULL 95.352000
2020-09-07 06:14:55.000 NULL NULL
2020-09-07 06:14:56.000 159.183500 100.748200
欠損値の補完
上記のクエリでは、データ分析用の不足しているタイムスタンプが生成されましたが、voltage と pressure の測定値については、いずれの欠損値 (null として表されるもの) も置き換えられませんでした。 Azure SQL Edge では、T-SQL の LAST_VALUE() および FIRST_VALUE() 関数に新しい構文が追加されました。これらは、データセット内の前または次の値に基づいて欠損値を補完するためのメカニズムを提供します。
新しい構文では、LAST_VALUE() および FIRST_VALUE() 関数に IGNORE NULLS および RESPECT NULLS 句が追加されます。 MachineTelemetry データセットに対する次のクエリでは、LAST_VALUE 関数を使って欠損値が計算されます。ここで、欠損値は、データセットで最後に観測された値に置き換えられます。
SELECT timestamp,
VoltageReading AS OriginalVoltageValues,
LAST_VALUE(VoltageReading) IGNORE NULLS OVER (
ORDER BY timestamp
) AS ImputedUsingLastValue,
PressureReading AS OriginalPressureValues,
LAST_VALUE(PressureReading) IGNORE NULLS OVER (
ORDER BY timestamp
) AS ImputedUsingLastValue
FROM MachineTelemetry
ORDER BY timestamp;
結果セットは次のとおりです。
timestamp OrigVoltageVals ImputedVoltage OrigPressureVals ImputedPressure
----------------------- ---------------- -------------- ----------------- ----------------
2020-09-07 06:14:41.000 164.990400 164.990400 97.223600 97.223600
2020-09-07 06:14:42.000 162.241300 162.241300 93.992800 93.992800
2020-09-07 06:14:43.000 163.271200 163.271200 NULL 93.992800
2020-09-07 06:14:44.000 161.368100 161.368100 93.403700 93.403700
2020-09-07 06:14:45.000 NULL 161.368100 NULL 93.403700
2020-09-07 06:14:46.000 NULL 161.368100 98.364800 98.364800
2020-09-07 06:14:49.000 NULL 161.368100 94.098300 94.098300
2020-09-07 06:14:51.000 157.695700 157.695700 103.359100 103.359100
2020-09-07 06:14:52.000 157.019200 157.019200 NULL 103.359100
2020-09-07 06:14:54.000 NULL 157.019200 95.352000 95.352000
2020-09-07 06:14:56.000 159.183500 159.183500 100.748200 100.748200
次のクエリでは、LAST_VALUE() および FIRST_VALUE 関数の両方を使用して欠損値を補完します。 出力列 ImputedVoltage では、欠損値は最後の観測値に置き換えられますが、出力列 ImputedPressure では、欠損値はデータセット内の次の観測値に置き換えられます。
SELECT dt AS [timestamp],
VoltageReading AS OrigVoltageVals,
LAST_VALUE(VoltageReading) IGNORE NULLS OVER (
ORDER BY dt
) AS ImputedVoltage,
PressureReading AS OrigPressureVals,
FIRST_VALUE(PressureReading) IGNORE NULLS OVER (
ORDER BY dt ROWS BETWEEN CURRENT ROW
AND UNBOUNDED FOLLOWING
) AS ImputedPressure
FROM (
SELECT a.dt,
b.VoltageReading,
b.PressureReading
FROM #SeriesGenerate a
LEFT JOIN MachineTelemetry b
ON a.dt = b.[timestamp]
) A
ORDER BY timestamp;
結果セットは次のとおりです。
timestamp OrigVoltageVals ImputedVoltage OrigPressureVals ImputedPressure
----------------------- ---------------- --------------- ----------------- ---------------
2020-09-07 06:14:41.000 164.990400 164.990400 97.223600 97.223600
2020-09-07 06:14:42.000 162.241300 162.241300 93.992800 93.992800
2020-09-07 06:14:43.000 163.271200 163.271200 NULL 93.403700
2020-09-07 06:14:44.000 161.368100 161.368100 93.403700 93.403700
2020-09-07 06:14:45.000 NULL 161.368100 NULL 98.364800
2020-09-07 06:14:46.000 NULL 161.368100 98.364800 98.364800
2020-09-07 06:14:47.000 NULL 161.368100 NULL 94.098300
2020-09-07 06:14:48.000 NULL 161.368100 NULL 94.098300
2020-09-07 06:14:49.000 NULL 161.368100 94.098300 94.098300
2020-09-07 06:14:50.000 NULL 161.368100 NULL 103.359100
2020-09-07 06:14:51.000 157.695700 157.695700 103.359100 103.359100
2020-09-07 06:14:52.000 157.019200 157.019200 NULL 95.352000
2020-09-07 06:14:53.000 NULL 157.019200 NULL 95.352000
2020-09-07 06:14:54.000 NULL 157.019200 95.352000 95.352000
2020-09-07 06:14:55.000 NULL 157.019200 NULL 100.748200
2020-09-07 06:14:56.000 159.183500 159.183500 100.748200 100.748200
Note
上記のクエリでは、FIRST_VALUE() 関数を使用して、欠損値を次の観測値に置き換えます。 LAST_VALUE() 関数を ORDER BY <ordering_column> DESC 句と共に使用すると、同じ結果を得ることができます。