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

Привет, Хабр!

Есть такая функция в 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);

Результат:

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

SELECT generate_series('2025-01-01'::date, '2025-01-10'::date, '1 day');

Результат:

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

Если вы случайно передали 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;

Результат:

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

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

Результат:

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

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

Результат:

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

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

SELECT      generate_series(         '2025-01-01 00:00'::timestamp,         '2025-01-02 00:00'::timestamp,         '6 hours'     ) AS cleaning_schedule;

Результат:

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

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

SELECT * FROM generate_series(1, 5) WITH ORDINALITY;

Результат:

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

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

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

Результат:

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

Иногда нужно динамически определять шаг. Например, нужно генерировать события каждые 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; 

Результат:

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

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

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

Для этого:

1. Генерируем диапазон всех дат за последние 3 месяца с помощью generate_series.

2. Присоединяем таблицу продаж к этим датам.

3. Считаем количество покупок по дням недели.

Таблица продаж 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. Итоговый запрос группирует данные по дням недели, суммирует продажи и сортирует их по популярности.

Результат:

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

---

В заключение хочу обратить ваше внимание на открытые уроки по системному анализу:

• 23 января — «Пользовательские истории (User Stories): как превратить бизнес‑требования заказчика в задачи на разработку». Записаться

• 11 февраля — «От запроса к решению: как разобраться в потребностях заказчика и не упустить главное». Записаться