Коротко про generate_series в PostgreSQL

Kate · Воскресенье в 15:32

Есть такая функция в PostgreSQL — generate_series. На первый взгляд, она кажется скучной. Ну подумаешь, генерирует ряды чисел. Но не тут-то было! Этот инструмент спасает от десятков скучных запросов и сотен часов рутины.

generate_series — это функция PostgreSQL, которая создаёт ряды значений на лету.

Синтаксис до боли простой:

generate_series(start, stop [, step])

start — начало ряда. Например, 1.
stop — конец ряда. Например, 10.
step — шаг. По умолчанию 1, но можно указать любое значение, включая дробные или отрицательные.

Базовые примеры

Генерация чисел от 1 до 10 — это SQL-эквивалент «Привет, мир!»:

SELECT generate_series(1, 10);
Результат:

generate_series
1
2
...
10

Пример поинтереснее с генерацией дат:

SELECT generate_series('2025-01-01'::date, '2025-01-10'::date, '1 day');
Результат:

generate_series
2025-01-01
2025-01-02
...
2025-01-10

Возможные ошибки

Если вы случайно передали step = 0, то вы сломаете запрос:

SELECT generate_series(1, 10, 0);
-- ERROR: step size cannot equal zero
Всегда проверяйте, что шаг ненулевой.

Может быть слишком большой запрос. Запрос вида:

SELECT generate_series(1, 1000000000);
Не удивляйтесь, если сервер решит взять отпуск.

Ограничивайте размер диапазона:

DO $$
BEGIN
IF abs(1000000000 - 1) / 1 > 100000 THEN
RAISE EXCEPTION 'Слишком большой диапазон!';
END IF;
END $$;
Если хотя бы один из параметров NULL, результат будет пустым:

SELECT generate_series(NULL, 10);
-- (0 rows)
Валидируйте параметры до вызова функции.

Прочие функции

Можно генерировать временные интервалы. Хочется расписание котиковых событий каждые 2 часа? Легко!

SELECT generate_series(
'2025-01-01 00:00'::timestamp,
'2025-01-02 00:00'::timestamp,
'2 hours'
) AS event_time;
Результат:

event_time
2025-01-01 00:00:00
2025-01-01 02:00:00
...
2025-01-02 00:00:00

А если у вас есть массив с котиками, и вы хотите получить каждого, используйте generate_subscripts:

SELECT
generate_subscripts('{Барсик, Мурзик, Рыжик}'::text[], 1) AS idx;
Результат:

idx
1
2
3

Нужно протестировать цены на котиков? Генерируем случайные значения:

SELECT
generate_series(1, 10) AS id,
round(random() * 100, 2) AS price
FROM generate_series(1, 10);

Результат:

id	price
1	43.12
2	78.45
...	...

generate_series умеет работать не только с числами, но и сtimestamp или интервалами.

Допустим, нужно составить расписание уборки котиковых домиков каждые 6 часов:

SELECT
generate_series(
'2025-01-01 00:00'::timestamp,
'2025-01-02 00:00'::timestamp,
'6 hours'
) AS cleaning_schedule;
Результат:

cleaning_schedule
2025-01-01 00:00:00
2025-01-01 06:00:00
2025-01-01 12:00:00
2025-01-01 18:00:00
2025-01-02 00:00:00

Это полезно для расписаний, временных интервалов и аналитики, где временные ряды являются основой.

Если вы хотите добавить порядковый номер каждому значению, просто используйте WITH ORDINALITY. Например, для генерации индексов чисел:

SELECT * FROM generate_series(1, 5) WITH ORDINALITY;
Результат:

generate_series	ordinality
1	1
2	2
3	3
4	4
5	5

Это удобно для случаев, когда нужно сохранить порядок в результирующих данных, особенно при объединении с другими таблицами.

Работа с массивами часто вызывает раздражение, но с generate_subscripts это весьма удобно. Например, есть массив котиков:

SELECT
generate_subscripts('{Барсик, Мурзик, Рыжик}'::text[], 1) AS idx,
('{Барсик, Мурзик, Рыжик}'::text[])[idx] AS cat_name;
Результат:

idx	cat_name
1	Барсик
2	Мурзик
3	Рыжик

Теперь можно легко обращаться к элементам массива без циклов и других изысков.

Иногда нужно динамически определять шаг. Например, нужно генерировать события каждые 2 часа только в рабочее время (9:00–18:00):

SELECT
gs AS event_time
FROM
generate_series(
'2025-01-01 09:00'::timestamp,
'2025-01-01 18:00'::timestamp,
'2 hours'
) gs;

Результат:

event_time
2025-01-01 09:00:00
2025-01-01 11:00:00
2025-01-01 13:00:00
2025-01-01 15:00:00
2025-01-01 17:00:00

Такой запрос позволяет сочетать аналитику и временные ряды в одном шаге.

Пример аналитического запроса

Предположим, есть база данных интернет-магазина котиков. Нужно проанилизировать, как часто клиенты делают покупки в определённые дни недели за последние 3 месяца, включая дни, когда покупок не было.

Для этого:

Генерируем диапазон всех дат за последние 3 месяца с помощью generate_series.
Присоединяем таблицу продаж к этим датам.
Считаем количество покупок по дням недели.

Таблица продаж sales:

CREATE TABLE sales (
sale_id SERIAL PRIMARY KEY,
sale_date DATE NOT NULL,
amount NUMERIC(10, 2) NOT NULL
);

-- Пример данных
INSERT INTO sales (sale_date, amount) VALUES
('2025-01-01', 500),
('2025-01-03', 300),
('2025-01-05', 800),
('2025-01-06', 150),
('2025-01-10', 400);
Сначала создаём диапазон дат за последние 3 месяца:

SELECT generate_series(
date_trunc('month', CURRENT_DATE) - INTERVAL '3 months',
CURRENT_DATE,
'1 day'
) AS report_date;
Теперь объединяем этот диапазон с таблицей продаж, добавляем день недели и считаем количество покупок:

WITH date_series AS (
SELECT
generate_series(
date_trunc('month', CURRENT_DATE) - INTERVAL '3 months',
CURRENT_DATE,
'1 day'
) AS report_date
),
sales_data AS (
SELECT
ds.report_date,
COALESCE(COUNT(s.sale_id), 0) AS sale_count,
TO_CHAR(ds.report_date, 'Day') AS day_of_week
FROM
date_series ds
LEFT JOIN
sales s ON s.sale_date = ds.report_date
GROUP BY
ds.report_date
)
SELECT
day_of_week,
SUM(sale_count) AS total_sales_per_day
FROM
sales_data
GROUP BY
day_of_week
ORDER BY
total_sales_per_day DESC;
date_series генерирует все даты за последние 3 месяца. На этом основании sales_data соединяет таблицу продаж с каждой датой, подсчитывает количество продаж и добавляет название дня недели с помощью TO_CHAR. Итоговый запрос группирует данные по дням недели, суммирует продажи и сортирует их по популярности.

Результат:

day_of_week	total_sales_per_day
Friday	12
Monday	10
Saturday	8
...	...

Этот запрос показывает, какие дни недели являются самыми популярными для покупок, включая дни, когда их не было.