Данные временных рядов — это набор значений, упорядоченных так, как они появляются и поступают для обработки. В отличие от транзакционных данных SQL Server, которые не зависят от времени и могут часто обновляться, данные временных рядов обычно записываются один раз и, если обновляются, то крайне редко.

Вот несколько примеров данных временных рядов: цены на акции, телеметрия от датчиков, счётчики производительности SQL Server (например, утилизация ЦПУ, памяти, устройств ввода‑вывода и сети).

Данные временных рядов часто используются для сравнений в истории наблюдений, обнаружения аномалий и уведомления о достижении заданного порога, прогнозного анализа и составления отчетов, где время является осью значений при просмотре или анализе данных.

Возможности временных рядов в SQL Server были вначале представлены в Azure SQL Edge, версии Microsoft SQL Server для Интернета вещей (IoT), которая сочетает в себе такие возможности как потоковая передача данных и временные ряды, со встроенными функциями машинного обучения и построения графиков.

В SQL Server 2022 и Azure SQL временные ряды были добавлены во все версии SQL Server. Внесены изменения в существующие функции T‑SQL и алгоритмы работы функций адаптированы для NULL значений. Также добавлены две новые функции, призванные упростить работу с данными временных рядов.

Создание непрерывного диапазона с помощью GENERATE_SERIES

Обычно для анализа данных временных рядов создается непрерывный набор значений дат и времени (datetime). Значения в ряд вставляются через равные промежутки времени (например, каждую секунду) и находятся в пределах заданного диапазона. Один из способов добиться этого — создать таблицу чисел, также известную как таблица подсчета (tally table), которая содержит набор последовательных чисел между нижней и верхней границей. Затем числа в таблице можно использовать в сочетании с функцией DATEADD для создания диапазона значений дат и времени.

До SQL Server 2022 создание таблицы чисел обычно сопровождалось использованием CTE, CROSS JOIN системных объектов, циклов или некоторых других выкрутасов в T-SQL. Эти решения не являются ни элегантными, ни эффективными, ни масштабируемыми, зато были сопряжены с дополнительной сложностью, особенно, когда шаг между значениями в интервале должен был быть больше единицы.

Реляционный оператор GENERATE_SERIES в SQL Server 2022 упрощает создание таблицы чисел, возвращая таблицу чисел в виде столбца, и находящихся между начальным и конечным значениями с необязательным параметром, определяющим величину для увеличения/уменьшения значений между шагами в серии:

GENERATE_SERIES (start, stop [, step ])

В этом примере создается ряд чисел от 1 до 50 с шагом 5:

SELECT value
FROM GENERATE_SERIES(1, 50, 5);

value
-----------
1
6
11
16
21
26
31
36
41
46

Следующим шагом в развитии этой концепции рассмотрим пример, в котором показано, как в GENERATE_SERIES можно использовать с DATEADD для создания набора значений между 13:00 и 14:00 с интервалом в 1 минуту:

SELECT DATEADD(minute, s.value, 'Apr 10, 2023 1:00 PM') AS [Interval]
FROM GENERATE_SERIES(0, 59, 1) AS s;

Если аргумент для шага опущен, при вычислении очередного значения в интервале используется значение по умолчанию, равное единице. Оператор GENEATE_SERIES также может работать и с десятичными значениями, важно только, чтобы аргументы start, stop и step были одного типа. Если start больше, чем stop, а step является отрицательным значением, то в результате мы получим ряд убывающих значений. Если start больше stop и step положительный, мы получим пустую таблицу.

Наконец, для работы с оператором GENERATE_SERIES требуется уровень совместимости базы данных не ниже 160.

Группировка данных в интервалы с DATE_BUCKET

Для задач аналитики бывает нужно данные во временных рядах сгруппировать в виде фиксированных интервалов/сегментов. Например, проводимые каждую минуту измерения датчика можно усреднить за 15-минутные или даже часовые интервалы. Хотя для создания интервалов/сегментов могут использоваться операторы GENERATE_SERIES и DATEADD, нужен способ определить, к какому из них относится каждое конкретное измерение.

Функция DATE_BUCKET возвращает значение даты и времени, соответствующее началу каждого интервала/сегмента даты и времени для произвольного интервала, и с необязательным параметром для определения точки отсчёта (источника/origin), из которого вычисляется каждый интервал. Если источник не указан, в качестве даты точки отсчёта будет использоваться значение по умолчанию 1 января 1900 года:

DATE_BUCKET (datepart, number, date, origin)

В следующем примере показаны интервалы на 10 декабря 2022 г. для нескольких частей даты с размером интервала 1 и с точкой отсчёта на 1 января 2022 г.:

DECLARE @date DATETIME = 'Dec 10, 2022 12:05 PM';
DECLARE @origin DATETIME = 'Jan 1, 2022 12:00 AM';

SELECT 'Now' AS [BucketName], @date AS [DateBucketValue]
UNION ALL
SELECT 'Year', DATE_BUCKET (YEAR, 1, @date, @origin)
UNION ALL
SELECT 'Quarter', DATE_BUCKET (QUARTER, 1, @date, @origin)
UNION ALL
SELECT 'Month', DATE_BUCKET (MONTH, 1, @date, @origin)
UNION ALL
SELECT 'Week', DATE_BUCKET (WEEK, 1, @date, @origin)
UNION ALL
SELECT 'Day', DATE_BUCKET (DAY, 1, @date, @origin)
UNION ALL
SELECT 'Hour', DATE_BUCKET (HOUR, 1, @date, @origin)
UNION ALL
SELECT 'Minutes', DATE_BUCKET (MINUTE, 1, @date, @origin)
UNION ALL
SELECT 'Seconds', DATE_BUCKET (SECOND, 1, @date, @origin)

Обратите внимание, что значение интервала дат для недели: даты SELECT 'Week', DATE_BUCKET (WEEK, 1, @date, @origin) будет приходиться на 10 декабря 2022 г., то есть на субботу. Это связано с тем, что указанная дата точки отсчёта (1 января 2022 г.) также суббота (особо отметим, что дата начала отсчёта по умолчанию = 1 января 1900 г. — это понедельник). Поэтому при работе с недельными интервалами, если вы хотите, чтобы ваши недельные интервалы начиналась в воскресенье, обязательно используйте в качестве даты точки отсчёта такую дату, которая приходится на воскресенье.

DATE_BUCKET бывает особенно полезным когда размер интервала должен быть больше 1. Например, при группировке данных в 5-минутные или 15-минутные интервалы.

SELECT 'Now' AS [BucketName], GETDATE() AS [BucketDate]
UNION ALL
SELECT '5 Minute Buckets', DATE_BUCKET (MINUTE, 5, GETDATE())
UNION ALL
SELECT 'Quarter Hour', DATE_BUCKET (MINUTE, 15, GETDATE());

DATE_BUCKET является простым способом определения того, к какому интервалу времени принадлежит заданное меткой времени измерение. При этом, можно использовать любой интервал произвольного размера.

Анализ пропусков с FIRST_VALUE и LAST_VALUE

FIRST_VALUE и LAST_VALUE не новые функции в SQL Server 2022; что нового, так это то, как теперь обрабатываются значения NULL. В предыдущих версиях SQL Server значения NULL сохранялись.

При работе с временными рядами между измерениями могут быть пропуски. В идеале они заполняются заранее указанными значением. При использовании функций FIRST_VALUE и LAST_VALUE для вычисления значения, соответствующего интервалу, заполнение пропусков значениями NULL не является идеальным решением.

В примере ниже показания датчика, снятые с 15-секундными интервалами, имеют несколько пропусков. При анализе данных с интервалом в 1 минуту (с использованием DATE_BUCKET), по умолчанию, возвращаемые функцией FIRST_VALUE значения будет включать NULL:

SELECT [timestamp]
   , DATE_BUCKET(MINUTE, 1, [timestamp]) AS [timestamp_bucket]
   , SensorReading
   , FIRST_VALUE (SensorReading) OVER ( 
PARTITION BY DATE_BUCKET(MINUTE, 1, [timestamp]) 
ORDER BY [timestamp] 
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS [Default (RESPECT NULLS)]
FROM MachineTelemetry
ORDER BY [timestamp];

Теперь функции FIRST_VALUE и LAST_VALUE получили в SQL Server 2022 новый синтаксис (IGNORE NULLS или RESPECT NULLS), который позволяет указать, каким образом следует обрабатывать значения NULL:

FIRST_VALUE ( [scalar_expression ]   )  [ IGNORE NULLS | RESPECT NULLS ]
    OVER ( [   partition_by_clause ] order_by_clause [ rows_range_clause ] )

RESPECT NULLS будет использоваться по умолчанию и обеспечивает включение значений NULL в результат при вычислении первого или последнего значения в секции. Указание IGNORE NULLS приведет к исключению значений NULL.

SELECT [timestamp]
   , DATE_BUCKET(MINUTE, 1, [timestamp]) AS [timestamp_bucket]
   , SensorReading
   , FIRST_VALUE (SensorReading) IGNORE NULLS OVER ( 
PARTITION BY DATE_BUCKET(MINUTE, 1, [timestamp]) 
ORDER BY [timestamp] 
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS [Default (RESPECT NULLS)]
FROM MachineTelemetry
ORDER BY [timestamp];

Таким образом, новые параметры IGNORE NULLS и RESPECT NULLS позволяют выбрать способ обработки неизвестных значений.

Microsoft Learn

Примеры из GitHub

Time Series T-SQL functions in SQL Server 2022

Новое в SQL Server 2022: работа с time series (временные ряды)