Заполнение пробелов во времени и вменение отсутствующих значений
Важно!
Azure SQL Edge больше не поддерживает платформу ARM64.
В данных временных рядов часто могут отсутствовать значения атрибутов. Кроме того, ввиду специфики этих данных и прерывания их сбора в наборе данных образуются временные разрывы.
Например, при сборе статистики использования энергии для смарт-устройства всякий раз, когда устройство не работает, в статистике использования отсутствуют пробелы. Аналогичным образом в сценарии сбора данных телеметрии оборудования различные датчики могут быть настроены на выдачу данных с различной частотой, что приведет к отсутствию значений датчиков. Например, если есть два датчика, напряжения и давления, настроенные на 100 Гц и 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, в показаниях напряжения и давления.
Заполнение разрывов
Заполнение разрывов — это метод, позволяющий создать непрерывный, упорядоченный набор меток времени для упрощения анализа данных временных рядов. В SQL Azure для пограничных вычислений самым простым способом заполнения разрывов в наборе данных временных рядов является определение временной таблицы с требуемым распределением времени и выполнением операции 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
Аппроксимация отсутствующих значений
Предыдущий запрос создал отсутствующие метки времени для анализа данных, однако он не заменил отсутствующие значения (представленные как null) для voltage и pressure чтения. В SQL Azure для пограничных вычислений добавлен новый синтаксис LAST_VALUE() в T-SQL и 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. Для выходного столбца последнее наблюдаемое значение заменяет отсутствующие значения, а для выходного столбца ImputedVoltageImputedPressure отсутствующие значения заменяются следующим наблюдаемым значением в наборе данных.
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.
Следующие шаги
Обратная связь
Ожидается в ближайшее время: в течение 2024 года мы постепенно откажемся от GitHub Issues как механизма обратной связи для контента и заменим его новой системой обратной связи. Дополнительные сведения см. в разделе https://aka.ms/ContentUserFeedback.
Отправить и просмотреть отзыв по