Коротко про generate_series в PostgreSQL17.01.2025 10:30

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

Есть такая функция в 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 февраля — «От запроса к решению: как разобраться в потребностях заказчика и не упустить главное». Записаться

© Habrahabr.ru