Генерация числовой последовательности

Иногда возникает необходимость получить в запросе числовую последовательность. Это может быть самоцелью или же промежуточным результатом для получения, скажем, последовательности дат. Пусть, например, требуется получить последовательность целых чисел от 1 до 100 с шагом 1. Можно, конечно, строить такую последовательность в «лоб», то есть:

SELECT 1 AS num
UNION ALL
SELECT 2
...
UNION ALL
SELECT 100;

А если потребуется 1000 чисел или больше? Помимо затрат времени на написание такого количества операторов, мы будем неэффективно использовать сетевой трафик, передавая на выполнение серверу запросы такого размера.

Помочь нам уменьшить размер запроса может декартово произведение (CROSS JOIN), которое редко когда используется непосредственно, но часто является промежуточным результатом в различных алгоритмах получения осмысленных данных. Существенной особенностью декартового произведения является то, что мощность результата (количество строк) равно произведению мощностей участвующих в декартовом произведении таблиц. Например, если нам нужно получить последовательность 100 чисел, мы можем использовать декартово произведение таблиц, каждая из которых содержит всего по 10 записей. Итак:

SELECT *
FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
) x CROSS JOIN
(SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4
UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7
UNION ALL SELECT 8 UNION ALL SELECT 9 UNION ALL SELECT 10
) y;

Результатом здесь является двухстолбцовая таблица, содержащая 100 строк. При этом каждое значение из первого подзапроса (числа от 1 до 10) сочетается с каждым значением из второго (аналогичного) подзапроса:

Теперь осталось только вычислить сами значения. Будем считать, что число в первом столбце представляет собой десятки –1, а второй — единицы. Тогда вместо SELECT * FROM… в нашем запросе напишем:

SELECT 10*(a-1)+b
FROM …

что и даст требуемый результат.

А почему бы не взять три таблицы (подзапроса)? Чем больше размер генерируемой последовательности, тем больше таблиц следует взять, чтобы получить более короткий запрос. Аналогично рассуждая и, исходя из того, что 5 * 5 * 5 = 125, получим:

SELECT 5*5*(a-1)+5*(b-1) + c AS num
FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
) x CROSS JOIN
(SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
) y CROSS JOIN
(SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
) z
WHERE 5*5*(a-1)+5*(b-1) + c <= 100
ORDER BY 1;

Условие

WHERE 5*5*(a-1)+5*(b-1) + c <= 100

использовано для того, чтобы ограничить последовательность значением 100, а не 125.

Рассмотрим «практический» пример. Пусть требуется получить 100 последовательных незанятых номеров моделей, идущих за последним номером модели в таблице Product. Идея такова: находим максимальный номер модели и далее, используя генерацию последовательности, 100 последующих значений с шагом 1.

SELECT (SELECT MAX(model)
FROM Product
) + 5*5*(a-1)+5*(b-1) + c AS num
FROM (SELECT 1 a UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
) x CROSS JOIN
(SELECT 1 b UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
) y CROSS JOIN
(SELECT 1 c UNION ALL SELECT 2 UNION ALL SELECT 3
UNION ALL SELECT 4 UNION ALL SELECT 5
) z
WHERE 5*5*(a-1)+5*(b-1) + c <= 100
ORDER BY 1;

Результат выполнения этого запроса мы не будем здесь приводить из экономии места. Проверьте самостоятельно, щелкнув по кнопке “Выполнить”.

Если ваш сервер поддерживает CTE, то получение числовой последовательности существенно упрощается. Вы можете использовать Консоль учебника, чтобы решить рассмотренную здесь задачу  этим способом. За примерами вы можете обратиться к главе, посвященной рекурсивным CTE, и где рассматривается несколько числовых последовательностей.

Рекомендуемые упражнения: 66, 94, 104, 118, 143

Вероятно, ввиду часто возникающей потребности в числовых последовательностях, в PostgreSQL имеется функция, которая возвращает такую последовательность:

generate_series(start, stop [, step])

Здесь
start - начальное значение последовательности,
stop - конечное значение последовательности,
step - шаг последовательности (по умолчанию равен 1).

Применение данной функции проще показать на примерах. Начнем с задачи, которая рассматривалась на предыдущей странице:

Решение для PostgreSQL можно записать очень лаконично:

select cast(MAX(model) AS INT) + generate_series(1,100) as num from Product;

Преобразование типа здесь необходимо, поскольку номер модели (model) имеет тип VARCHAR.

Следующий пример - это вывод алфавита, который мы получали с помощью SQL-рекурсии. Применим тот же алгоритм, а именно, к коду первой буквы будем последовательно добавлять единицы, после чего преобразуем полученные коды к символам:

select CHR(ASCII('A') + generate_series(0,25)) as letter order by 1;

Наконец, рассмотрим довольно часто возникающую необходимость получения последовательности дат. В связи с этим отметим, что третий параметр (step) может быть не только типа INT, но и типа INTERVAL. Последнее позволит нам непосредственно работать с последовательностями дат, минуя преобразование последовательности чисел к последовательности дат. Итак,

SELECT generate_series(MIN(date), MAX(date), '1 day')
from pass_in_trip where id_psg = 5;

Поскольку пока на внутренних страницах учебника есть возможность выполнять скрипты только под SQL Server, вы можете для выполнения приведенных здесь запросов воспользоваться консолью, выбрав PostgreSQL в списке поддерживаемых СУБД.

Функция generate_series появилась в SQL Server 2022. Ее реализация несколько отличается от используемой в PostgreSQL. Вот, например, как эту же задачу можно решить в SQL Server, используя функцию generate_series:

WITH gs AS   
(SELECT MIN(date) mind, DATEDIFF(day,MIN(date), MAX(date)) cnt  
FROM pass_in_trip WHERE id_psg = 5)  
SELECT DATEADD( DAY, value, mind) _date_  
FROM gs CROSS APPLY GENERATE_SERIES(0, gs.cnt, 1);