Вот несколько примеров данных временных рядов: цены на акции, телеметрия от датчиков, счётчики производительности 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
Дополнительно:
Комментариев нет:
Отправить комментарий