時間のギャップを埋め、欠損値を補完する

[アーティクル]
12/04/2024

重要

Azure SQL Edge の提供は、2025 年 9 月 30 日に終了する予定です。詳細な情報と移行オプションについては、提供終了に関する通知を参照してください。

Note

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 句と共に使用すると、同じ結果を得ることができます。

次の方法で共有

時間のギャップを埋め、欠損値を補完する

ギャップを埋める

欠損値の補完

フィードバック

その他のリソース

次の方法で共有

時間のギャップを埋め、欠損値を補完する

ギャップを埋める

欠損値の補完

関連するコンテンツ

フィードバック

その他のリソース