SQL-Ex blog

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

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

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

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

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

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

GENERATE_SERIES (start, stop [, step ])

Этот пример создает ряд чисел между 1 и 100 с шагом 5:

SELECT value

FROM GENERATE_SERIES(1, 100, 5);

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

SELECT DATEADD(minute, s.value, 'Dec 10, 2022 1:00 PM') AS [Interval]

FROM GENERATE_SERIES(0, 59, 1) AS s;

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

Наконец, GENEATE_SERIES требует уровень совместимости 160 и выше.

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

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

Функция DATE_BUCKET возвращает значение datetime, которое соответствует начальной точке каждого сегмента datetime для произвольного размера сегмента, при этом имеется необязательный параметр для определения исходной точки (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)

Обратите внимание, что значением сегмента даты для компоненты Week (неделя) является 10 декабря 2022, что является субботой. Это так, поскольку исходная дата также является субботой (1 января 2022). (Заметьте, что значением по умолчанию для исходной даты является 1 января 1900, т.е. понедельник). Следовательно, при работе с компонентой даты Week, если вы хотите, чтобы сегмент Week начинался с субботы, убедитесь, что исходная дата попадает на субботу.

DATE_BUCKET становится особенно полезной для размеров сегментов больше 1, например, при группировке данных в пятиминутных или 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; новым здесь является то, как обрабатываются 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];

В SQL Server в синтаксисе FIRST_VALUE и LAST_VALUE появились новые предложения (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 позволяют решить, как следует трактовать NULL-значения при анализе данных.

Ссылки по теме

1. Мои любимые улучшения T-SQL в SQL Server 2022

2. Изменения языка T-SQL в SQL Server 2022. Часть 2