Заполнение пробелов во времени и вменение отсутствующих значений

Статья
09/21/2024

Внимание

Azure SQL Edge будет прекращена 30 сентября 2025 г. Дополнительные сведения и параметры миграции см. в уведомлении о выходе на пенсию.

Примечание.

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.

Поделиться через

Заполнение пробелов во времени и вменение отсутствующих значений

Заполнение разрывов

Аппроксимация отсутствующих значений

Обратная связь

Дополнительные ресурсы

Поделиться через

Заполнение пробелов во времени и вменение отсутствующих значений

Заполнение разрывов

Аппроксимация отсутствующих значений

Связанный контент

Обратная связь

Дополнительные ресурсы