填滿時間間距並插補遺漏值
重要
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.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 資料為例，下列查詢可用來產生連續、已排序的時間戳記集以供分析。
注意
下列查詢會產生遺漏的資料列，其中包含屬性的時間戳記值和 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
插補遺漏的值
上述查詢已產生遺漏的時間戳記以供資料分析，但未取代 voltage 和 pressure 讀數的任何遺漏值 (以 null 表示)。 在 Azure SQL Edge 中，新的語法已新增至 T-SQL LAST_VALUE() 和 FIRST_VALUE() 函式，以根據資料集中前方或後方的值來提供插補遺漏值的機制。
新語法會將 IGNORE NULLS 和 RESPECT 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 子句來達成相同的結果。