填充时间间隙和插补缺失值
重要
Azure SQL Edge 将于 2025 年 9 月 30 日停用。 有关详细信息和迁移选项，请参阅停用通知。
注意
Azure SQL Edge 不再支持 ARM64 平台。
处理时序数据时，时序数据可能经常会缺少属性值。 由于数据的本性，或者由于数据收集过程中出现中断情况，数据集中可能会存在时间间隙。
例如，收集智能设备的能源使用情况统计信息时，如果设备不运行，则使用情况统计信息中会出现间隙。 同样，在计算机遥测数据收集方案中，可能会将不同的传感器配置为以不同的频率发出数据，从而导致传感器缺少值。 例如，如果有两个传感器（电压和压力），分别配置为 100 Hz 和 10 Hz 频率，则电压传感器将每隔 1/100 秒发出一次数据，而压力传感器将每隔 1/10 秒发出一次数据。
下表描述了按一秒的时间间隔收集的计算机遥测数据集。
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() 函数中添加了一个新的语法，这些函数提供了相关机制，可以根据数据集中前面或后面的值来插补缺失值。
新语法在 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
注意
上面的查询使用 FIRST_VALUE() 函数将缺失值替换为下一个观察到的值。 可以通过将 LAST_VALUE() 函数与 ORDER BY <ordering_column> DESC 子句结合使用来获得相同的结果。