共用方式為


填滿時間間距並插補遺漏值

重要

Azure SQL Edge 將於 2025 年 9 月 30 日淘汰。 如需詳細資訊和移轉選項,請參閱淘汰通知

注意

Azure SQL Edge 不再支援 ARM64 平台。

處理時間序列資料時,時間序列資料往往可能會有屬性的遺漏值。 也可能是因為資料的本質,或因為資料收集中斷,資料集中會有時間間距

例如,收集智慧型裝置的能源使用量統計資料時,只要裝置無法運作,使用量統計資料就會有落差。 同樣地,在機器遙測資料收集案例中,可能會將不同的感應器設定為以不同頻率發出資料,進而產生感應器的遺漏值。 例如,如果有兩個感應器,即電壓和壓力,分別設定為 100 Hz 和 10 Hz 頻率,則電壓感應器會每隔一百分之一秒發出資料,而壓力感應器只會每隔十分之一秒發出資料。

下表描述以一秒間隔收集的機器遙測資料集。

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

上述資料集有兩個重要特性。

  • 資料集不包含與數個時間戳記 2020-09-07 06:14:47.0002020-09-07 06:14:48.0002020-09-07 06:14:50.0002020-09-07 06:14:53.0002020-09-07 06:14:55.000 相關的任何資料點。 這些時間戳記是資料集中的間距
  • 在電壓和壓力讀數中,遺漏的值以 null 表示。

間距填滿

間距填滿是一種技術,可協助建立連續、已排序的一組時間戳記,以利時間序列資料的分析。 在 Azure SQL Edge 中,填滿時間序列資料集中間距的最簡單方式是使用所需的時間分佈來定義暫存資料表,然後在資料集資料表上執行 LEFT OUTER JOINRIGHT OUTER JOIN 作業。

以先前呈現的 MachineTelemetry 資料為例,下列查詢可用來產生連續、已排序的時間戳記集以供分析。

注意

下列查詢會產生遺漏的資料列,其中包含屬性的時間戳記值和 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];

上述查詢會產生下列輸出,其中包含指定範圍中的所有一秒時間戳記。

結果集如下:

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

插補遺漏的值

上述查詢已產生遺漏的時間戳記以供資料分析,但未取代 voltagepressure 讀數的任何遺漏值 (以 null 表示)。 在 Azure SQL Edge 中,新的語法已新增至 T-SQL LAST_VALUE()FIRST_VALUE() 函式,以根據資料集中前方或後方的值來提供插補遺漏值的機制。

新語法會將 IGNORE NULLSRESPECT NULLS 子句新增至 LAST_VALUE()FIRST_VALUE() 函式。 下列針對 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

注意

以上查詢會使用 FIRST_VALUE() 函式,將遺漏值以下一個觀察到的值取代。 您可以使用 LAST_VALUE() 函式搭配 ORDER BY <ordering_column> DESC 子句來達成相同的結果。