Я продолжаю находить или слышать о весьма полезных функциях, которые я просто упустил. Планирую перечитать все записи о «новых возможностях» для SQL Server (ну, по крайней мере, разделы Transact-SQL!) и посмотреть, что ещё я пропустил, а также другие функции, которые я использовал недостаточно, но которые кажутся полезными.
Узнал я об этой функции несколько недель назад из публикации Джована Поповича в LinkedIn.
И я мгновенно понял, что он имел в виду, говоря о том, насколько полезна будет функция DATE_BUCKET (появившаяся в SQL Server 2022)!
Используя эту функцию, вы можете легко группировать данные в различные временные интервалы, такие как год, месяц, день (что, конечно, достаточно стандартно), но также и в интервалы вроде 2 дней, 6,4 недель и т.д. Я не считаю, что это должно заставить вас отказаться от измерения дат в вашем хранилище данных, но она великолепна, когда вы просто исследуете данные и хотите легко поэкспериментировать с разными интервалами.
Основы
Функция DATE_BUCKET — это, по сути, инструмент для округления значений дат до нижней границы. Допустим, у вас есть дата 2025-12-30. Если вы хотите поместить её в интервал «Декабрь 2025», вы можете использовать следующие параметры:
DATE_BUCKET(<временной период>, <количество периодов>, <значение даты>, [<начальная дата>])
Так, например, если вы хотите создать относительно простой временной интервал на уровне месяца, можно использовать что-то вроде этого (в первом примере показано только одно значение даты):
SELECT DATE_BUCKET(month, 1, CAST('2025-12-30' AS DATE));
Это возвращает:
------------ 2025-12-01
Разумеется, то же самое верно для любого другого литерала даты, начинающегося с '2025-12-', если это корректная дата. Для временных значений это можно делать вплоть до миллисекунд. Здесь я группирую данные по секундам:
SELECT DATE_BUCKET(second, 5, CAST('2025-12-30 03:45:16.145' AS DATETIME2(3)));
Это возвращает:
2025-12-30 03:45:15.000
Мы поговорим об этом подробнее далее в блоге, а пока просто примите к сведению, что все группировки начинаются с 1900-01-01 00:00:00.000, что был понедельник. Это станет важно позже, когда мы начнём создавать интервалы, у которых нет очевидной начальной точки. Например, интервалы в 3 недели. С какой недели начинается отсчёт?
Примечание: DATE_BUCKET не работает напрямую с литеральными значениями, вероятно, по соображениям производительности и потому, что она явно предназначена для работы с большими объёмами строк. Вызов SELECT DATE_BUCKET(month,1,'2025-12-30'); вызовет ошибку:
Msg 8116, Level 16, State 1, Line 16 Argument data type varchar is invalid for argument 3 of Date_Bucket function.
Примеры
Я знаю, что в блоги приходят за примерами, так что приступим. Я создал таблицу со строкой на каждую секунду, чтобы показать, как мы можем их группировать.
CREATE TABLE #ExampleData
(
ExampleDataId int NOT NULL,
TimeValue datetime2(0) --данные с точностью до минуты
)
INSERT INTO #ExampleData(ExampleDataId, TimeValue)
SELECT VALUE AS ExampleDataId, DATEADD(MINUTE, VALUE - 1,'2025-01-01')
FROM GENERATE_SERIES(1,525600);
В примерах я начну с очень высокого уровня и буду двигаться к группировке по минутам.
Группировка по годам и месяцам
Для начала вот количество всех данных в таблице:
SELECT COUNT(*)
FROM #ExampleData
Это возвращает:
----------- 525600
Не углубляясь в философию, но если задуматься, это не так уж много минут.
Другой способ получить тот же ответ:
SELECT DATE_BUCKET(month, 12, TimeValue) AS DateGroup, COUNT(*) AS GroupCount
FROM #ExampleData
GROUP BY DATE_BUCKET(month, 12, TimeValue);
Это возвращает:
DateGroup GroupCount --------------------------- ----------- 2025-01-01 00:00:00 525600
Группируя по 12-месячным интервалам, вы видите, что всё округляется до начала 12-месячного периода (это аналогично году):
SELECT DATE_BUCKET(year, 1, TimeValue) AS DateGroup, COUNT(*) AS GroupCount
FROM #ExampleData
GROUP BY DATE_BUCKET(year, 1, TimeValue);
Тот же результат. Поскольку это своего рода функция округления, вы не можете (по крайней мере, я пока не видел) создавать действительно сложные группировки, не укладывающиеся в «обычный» календарь. Например, вы не можете задать пользовательские временные диапазоны вроде «первая неделя и половина месяца, а затем остаток недели». Для этого по-прежнему нужно создавать таблицу дат. Но всё, что можно основывать на интервалах равного размера, кратных целому числу периодов.
Например, если попытаться использовать дробное число:
SELECT DATE_BUCKET(year, .5, TimeValue) AS DateGroup, COUNT(*) AS GroupCount
FROM #ExampleData
GROUP BY DATE_BUCKET(year, .5, TimeValue);
Вы получите довольно запутанное сообщение об ошибке, ведь 0.5 всё-таки положительное значение!
Msg 9834, Level 16, State 1, Line 75 Invalid bucket width value passed to date_bucket function. Only positive values are allowed.
Месяцы
SELECT DATE_BUCKET(month, 1, TimeValue) AS DateGroup, COUNT(*) AS GroupCount
FROM #ExampleData
GROUP BY DATE_BUCKET(month, 1, TimeValue)
ORDER BY DateGroup;
Это возвращает:
DateGroup GroupCount --------------------------- ----------- 2025-01-01 00:00:00 44640 2025-02-01 00:00:00 40320 2025-03-01 00:00:00 44640 2025-04-01 00:00:00 43200 2025-05-01 00:00:00 44640 2025-06-01 00:00:00 43200 2025-07-01 00:00:00 44640 2025-08-01 00:00:00 44640 2025-09-01 00:00:00 43200 2025-10-01 00:00:00 44640 2025-11-01 00:00:00 43200 2025-12-01 00:00:00 44640
Заметьте, что, конечно, группы не равны по количеству строк/размеру, потому что не каждый месяц имеет одинаковое количество дней. Полугодия:
SELECT DATE_BUCKET(month, 6, TimeValue) AS DateGroup, COUNT(*) AS GroupCount
FROM #ExampleData
GROUP BY DATE_BUCKET(month, 6, TimeValue)
ORDER BY DateGroup;
Это возвращает:
DateGroup GroupCount --------------------------- ----------- 2025-01-01 00:00:00 260640 2025-07-01 00:00:00 264960
Кварталы
SELECT DATE_BUCKET(month, 3, TimeValue) AS DateGroup, COUNT(*) AS GroupCount
FROM #ExampleData
GROUP BY DATE_BUCKET(month, 3, TimeValue)
ORDER BY DateGroup;
Теперь у нас 4 группы:
DateGroup GroupCount --------------------------- ----------- 2025-01-01 00:00:00 129600 2025-04-01 00:00:00 131040 2025-07-01 00:00:00 132480 2025-10-01 00:00:00 132480
Недели
Если вы хотите группировать по неделям, это достаточно просто. Например, некоторые организации работают по 13-месячной системе, где в каждом «месяце» 4 недели.
SELECT DATE_BUCKET(Week, 4, TimeValue) AS DateGroup, COUNT(*) AS GroupCount
FROM #ExampleData
GROUP BY DATE_BUCKET(Week, 4, TimeValue)
ORDER BY DateGroup;
Теперь мы получаем 14 строк (частичные группы в начале и конце списка, которые, как видите, содержат меньше строк, потому что данные в таблице только за 2025 год, но строки в начале года группируются к дате в 2024):
DateGroup GroupCount --------------------------- ----------- 2024-12-16 00:00:00 17280 2025-01-13 00:00:00 40320 2025-02-10 00:00:00 40320 2025-03-10 00:00:00 40320 2025-04-07 00:00:00 40320 2025-05-05 00:00:00 40320 2025-06-02 00:00:00 40320 2025-06-30 00:00:00 40320 2025-07-28 00:00:00 40320 2025-08-25 00:00:00 40320 2025-09-22 00:00:00 40320 2025-10-20 00:00:00 40320 2025-11-17 00:00:00 40320 2025-12-15 00:00:00 24480
Начальная точка
Стоит отметить для предыдущего примера, что понедельник считается началом недели, и, как кратко упоминалось ранее в статье, начальная точка для недельных интервалов — понедельник, 1900-01-01 (что также означает, что годы начинаются 1 января, и месяцы тоже).
Что интересно, это не соответствует значению @@DATEFIRST на моём сервере, поэтому его нужно задавать в вызове функции с помощью другого параметра. Например, на моём сервере:
SELECT @@DATEFIRST;
Возвращает 7 (воскресенье), а не понедельник. Но вы видите, что первая группировка в предыдущем запросе была 2024-12-16, и это понедельник:
WITH BaseRows AS (
SELECT DATE_BUCKET(Week, 4, TimeValue) AS DateGroup, COUNT(*) AS GroupCount
FROM #ExampleData
GROUP BY DATE_BUCKET(Week, 4, TimeValue)
)
SELECT *, DATENAME(weekday, DateGroup) AS DayOfTheWeek
FROM BaseRows
ORDER BY DateGroup;
Это возвращает:
DateGroup GroupCount DayOfTheWeek --------------------------- ----------- ------------------------------ 2024-12-16 00:00:00 17280 Monday 2025-01-13 00:00:00 40320 Monday 2025-02-10 00:00:00 40320 Monday 2025-03-10 00:00:00 40320 Monday 2025-04-07 00:00:00 40320 Monday 2025-05-05 00:00:00 40320 Monday 2025-06-02 00:00:00 40320 Monday 2025-06-30 00:00:00 40320 Monday 2025-07-28 00:00:00 40320 Monday 2025-08-25 00:00:00 40320 Monday 2025-09-22 00:00:00 40320 Monday 2025-10-20 00:00:00 40320 Monday 2025-11-17 00:00:00 40320 Monday 2025-12-15 00:00:00 24480 Monday
Если вы хотите изменить это, можно сделать что-то вроде этого, задав начальную дату самостоятельно (о чём кратко упоминалось ранее), используя вызов вида: DATE_BUCKET(Week,4,TimeValue,CAST('2025-01-01' AS DATETIME2)). Начальная дата также не принимает строковый литерал, что имеет куда меньше смысла, чем для значения даты, но что есть, то есть:
WITH BaseRows AS (
SELECT DATE_BUCKET(Week, 4, TimeValue, CAST('2025-01-01' AS DATETIME2)) AS DateGroup,
COUNT(*) AS GroupCount
FROM #ExampleData
GROUP BY DATE_BUCKET(Week, 4, TimeValue, CAST('2025-01-01' AS DATETIME2))
)
SELECT *, DATENAME(weekday, DateGroup) AS DayOfTheWeek
FROM BaseRows
ORDER BY DateGroup;
Теперь в выводе вы увидите, что каждая группа привязана к среде, поскольку это был первый день 2025 года.
DateGroup GroupCount DayOfTheWeek --------------------------- ----------- ------------------------------ 2025-01-01 00:00:00.0000000 40320 Wednesday 2025-01-29 00:00:00.0000000 40320 Wednesday 2025-02-26 00:00:00.0000000 40320 Wednesday 2025-03-26 00:00:00.0000000 40320 Wednesday 2025-04-23 00:00:00.0000000 40320 Wednesday 2025-05-21 00:00:00.0000000 40320 Wednesday 2025-06-18 00:00:00.0000000 40320 Wednesday 2025-07-16 00:00:00.0000000 40320 Wednesday 2025-08-13 00:00:00.0000000 40320 Wednesday 2025-09-10 00:00:00.0000000 40320 Wednesday 2025-10-08 00:00:00.0000000 40320 Wednesday 2025-11-05 00:00:00.0000000 40320 Wednesday 2025-12-03 00:00:00.0000000 40320 Wednesday 2025-12-31 00:00:00.0000000 1440 Wednesday
Очень крутая штука, но, возможно, немного хитрая, когда только начинаешь с ней работать.
Краткий пример с детализацией меньше дня
Помните, что наши данные имеют точность до минуты:
SELECT TOP 10 *
FROM #ExampleData
ORDER BY TimeValue ASC;
Это возвращает:
ExampleDataId TimeValue ------------- --------------------------- 1 2025-01-01 00:00:00 2 2025-01-01 00:01:00 3 2025-01-01 00:02:00 4 2025-01-01 00:03:00 5 2025-01-01 00:04:00 6 2025-01-01 00:05:00 7 2025-01-01 00:06:00 8 2025-01-01 00:07:00 9 2025-01-01 00:08:00 10 2025-01-01 00:09:00
Вы можете использовать различные временные единицы для ваших данных, вплоть до миллисекунд, но для моего краткого примера я возьму 10-минутные интервалы (по-прежнему начиная с начала года для группировки):
WITH BaseRows AS (
SELECT DATE_BUCKET(Minute, 10, TimeValue, CAST('2025-01-01' AS DATETIME2))
AS DateGroup,
COUNT(*) AS GroupCount
FROM #ExampleData
GROUP BY DATE_BUCKET(Minute, 10, TimeValue, CAST('2025-01-01' AS DATETIME2))
)
SELECT TOP 10 *, DATENAME(weekday, DateGroup) AS DayOfTheWeek
FROM BaseRows
ORDER BY DateGroup;
Вывод здесь следующий:
DateGroup GroupCount DayOfTheWeek --------------------------- ----------- ------------------------------ 2025-01-01 00:00:00.0000000 10 Wednesday 2025-01-01 00:10:00.0000000 10 Wednesday 2025-01-01 00:20:00.0000000 10 Wednesday 2025-01-01 00:30:00.0000000 10 Wednesday 2025-01-01 00:40:00.0000000 10 Wednesday 2025-01-01 00:50:00.0000000 10 Wednesday 2025-01-01 01:00:00.0000000 10 Wednesday 2025-01-01 01:10:00.0000000 10 Wednesday 2025-01-01 01:20:00.0000000 10 Wednesday 2025-01-01 01:30:00.0000000 10 Wednesday
И мы можем даже сдвинуть начало отсчёта на несколько секунд:
WITH BaseRows AS (
SELECT DATE_BUCKET(Minute, 10, TimeValue, CAST('2025-01-01 00:02'
AS DATETIME2)) AS DateGroup,
COUNT(*) AS GroupCount
FROM #ExampleData
GROUP BY DATE_BUCKET(Minute, 10, TimeValue, CAST('2025-01-01 00:02' AS DATETIME2))
)
SELECT TOP 10 *, DATENAME(weekday, DateGroup) AS DayOfTheWeek
FROM BaseRows
ORDER BY DateGroup;
Теперь группа начинается на 2 минуты раньше, и первые 2 секунды года попадают в группу, которая началась во вторник, 31 декабря.
DateGroup GroupCount DayOfTheWeek --------------------------- ----------- ------------------------------ 2024-12-31 23:52:00.0000000 2 Tuesday 2025-01-01 00:02:00.0000000 10 Wednesday 2025-01-01 00:12:00.0000000 10 Wednesday 2025-01-01 00:22:00.0000000 10 Wednesday 2025-01-01 00:32:00.0000000 10 Wednesday 2025-01-01 00:42:00.0000000 10 Wednesday 2025-01-01 00:52:00.0000000 10 Wednesday 2025-01-01 01:02:00.0000000 10 Wednesday 2025-01-01 01:12:00.0000000 10 Wednesday 2025-01-01 01:22:00.0000000 10 Wednesday
Итоги
В SQL Server 2022 добавлена функция DATE_BUCKET, которая позволяет очень легко группировать данные на основе равных временных интервалов без необходимости в специальной таблице или другой функции для определения группировки. Это, безусловно, синтаксический сахар, ведь то же самое можно сделать с помощью существующих функций, но с ней это выполняется гораздо быстрее и проще.
Вы можете группировать по столетиям или миллисекундам, в зависимости от потребности. Что довольно круто.
Комментариев нет:
Отправить комментарий