SQL Server 2022 - GENERATE_SERIES

Конструкция GENERATE_SERIES - это новая функция в T-SQL 2022, которая может использоваться для генерации последовательностей значений в запросах. Эта функция подобна аналогичной функции в других языках программирования, например, в PostgreSQL.

Одной из основных применений функции GENERATE_SERIES в T-SQL является генерация последовательности чисел. Вот пример использования этой функции для генерации последовательности чисел от 1 до 10:

SELECT value FROM GENERATE_SERIES(1, 10)

Этот запрос вернет следующий результат:

Функция GENERATE_SERIES может принимать два или три аргумента: начальное значение, конечное значение и опциональный шаг. В приведенном выше примере начальным значением было 1, конечным значением - 10, а шаг был опущен, поэтому значения генерировались с шагом 1.

Функция GENERATE_SERIES также может использоваться для генерации последовательностей букв и даже дат. Вот пример использования этой функции для генерации последовательности букв от 'A' до 'Z':

SELECT CHAR(value + 64) AS letter FROM GENERATE_SERIES(1, 26)

Этот запрос вернет следующий результат:

letter
A
B
C
D
E
F
G
H
I
J
K
L
M
N
O
P
Q
R
S
T
U
V
W
X
Y
Z

К сожалению, GENERATE_SERIES не может непосредственно генерировать последовательности дат, как это возможно в других языках программирования. Однако, последовательность дат можно сгенерировать, используя функцию DATEADD для преобразования последовательности чисел в даты. Вот пример использования этой техники для генерации последовательности дат с 1 января 2022 года по 10 января 2022 года:

SELECT DATEADD(day, number-1, '2022-01-01') AS Date
FROM GENERATE_SERIES(1, DATEDIFF(day, '2022-01-01', '2022-01-10') + 1)

Date
2022-01-01
2022-01-02
2022-01-03
2022-01-04
2022-01-05
2022-01-06
2022-01-07
2022-01-08
2022-01-09
2022-01-10

Вы можете протестировать эти у другие запросы на сайте SQLize.online

В качестве практического применения данной функции приведу следующий пример. Допустим мы имеем таблицу заказов Orders (для простоты ограничимся двумя столбцами Date и Amount) добавим несколько строк тестовых данных и посчитаем дневную выручку:

CREATE TABLE Orders (
    Date date,
    Amount int
);

INSERT INTO Orders VALUES 
('2023-01-02', 500), ('2023-01-02', 400), ('2023-01-05', 400);

SELECT Date, SUM(Amount) AS DailyAmount
FROM Orders
GROUP BY Date;

Как Вы видите в результате дни в которых не было продаж просто отсутствуют в результате:

+============+=============+
| Date       | DailyAmount |
+============+=============+
| 2023-01-02 | 900         |
| 2023-01-05 | 400         |
+------------+-------------+

Если нам нужно получить результаты по всем числам за первую декаду января то GENERATE_SERIES то что сможет нам помочь:

WITH Decade AS (
    SELECT DATEADD(day, value-1, '2023-01-01') AS Date
    FROM GENERATE_SERIES(1, DATEDIFF(day, '2023-01-01', '2023-01-10') + 1)
) SELECT Decade.Date, COALESCE(SUM(Amount), 0) AS DailyAmount
FROM Decade
LEFT JOIN Orders ON Decade.Date = Orders.Date
GROUP BY Decade.Date;

Здесь мы используем CTE для генерации периода дат, LEFT JOIN с таблицей Orders и суммирование по датам. Для изящности используем функцию COALESCE для отобращения нулей вместо NULL

Всё это доступно онлайн на сайте SQLize.online