sqlpostgresqlgenerate-seriestime-series

SQL generate_series: Calendars, Number Ranges and Filling Gaps with Zeros

How generate_series builds a continuous run of integers, dates and timestamps, and why it is the cleanest way to get a gap-free axis.

9 min branjaReferencesql · postgresql · generate-series · time-series · mysql · calendar
Ta članek je trenutno v ruščini — angleški prevod je v pripravi.

generate_series — одна из самых удобных табличных функций PostgreSQL.

Она создаёт набор строк по правилу:

от начального значения
до конечного значения
с заданным шагом

Например:

1, 2, 3, 4, 5

или:

2026-01-01
2026-01-02
2026-01-03

или:

00:00
00:15
00:30
00:45
01:00

generate_series особенно полезна, когда нужно построить непрерывную ось:

  • числа от 1 до 100;
  • календарь по дням;
  • почасовую сетку;
  • интервалы по 15 минут;
  • месяцы для отчёта;
  • контрольные точки после регистрации;
  • пустые дни с нулевой выручкой.

Главная идея:

сначала создаём полную ось через generate_series, потом через LEFT JOIN приклеиваем к ней реальные данные.

Так в отчёте не пропадают дни, часы и периоды, где не было событий.

Что делает generate_series

generate_series возвращает строки.

Например:

SELECT n
FROM generate_series(1, 5) AS s(n);

Результат:

n
-
1
2
3
4
5

Это не одна строка с массивом, а именно несколько строк.

Поэтому generate_series обычно используют в секции FROM.

FROM generate_series(...) AS s(column_name)

Здесь:

AS s(n)

означает:

  • s — имя временной таблицы;
  • n — имя колонки.

После этого с n можно работать как с обычной колонкой.

Числовой ряд от 1 до 5

Самый простой вариант — числовой ряд.

SELECT n
FROM generate_series(1, 5) AS s(n);

Результат:

n
-
1
2
3
4
5

Границы включены с обеих сторон.

То есть generate_series(1, 5) возвращает и 1, и 5.

Это важная особенность.

generate_series(1, 5) = 1, 2, 3, 4, 5

А не:

1, 2, 3, 4

Числовой ряд с шагом

Третий аргумент — это шаг.

Например:

SELECT n
FROM generate_series(0, 100, 10) AS s(n);

Результат:

n
---
0
10
20
30
40
50
60
70
80
90
100

Здесь:

generate_series(0, 100, 10)

означает:

начни с 0
иди до 100
прибавляй по 10

Шаг тоже включается в логику попадания в конец. Если конечное значение достижимо этим шагом, оно попадёт в результат.

Например:

SELECT n
FROM generate_series(0, 10, 3) AS s(n);

Результат:

n
-
0
3
6
9

Число 10 не появилось, потому что шаг 3 не попал ровно в 10.

Отрицательный шаг

generate_series умеет идти в обратную сторону.

SELECT n
FROM generate_series(10, 1, -1) AS s(n);

Результат:

n
--
10
9
8
7
6
5
4
3
2
1

Отрицательный шаг нужен, когда вы хотите получить ряд от большего значения к меньшему.

Например:

SELECT n
FROM generate_series(100, 0, -20) AS s(n);

Результат:

n
---
100
80
60
40
20
0

Если шаг направлен не туда

Есть важная ловушка.

Если начало меньше конца, а шаг отрицательный, функция вернёт ноль строк.

SELECT n
FROM generate_series(1, 10, -1) AS s(n);

Результат будет пустой.

Почему?

Потому что вы сказали:

начни с 1
иди до 10
но каждый раз вычитай 1

Так до 10 добраться невозможно.

То же самое в обратную сторону:

SELECT n
FROM generate_series(10, 1, 1) AS s(n);

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

Главное правило:

Направление шага должно соответствовать направлению от start к stop.

Шаг не может быть нулевым

Так писать нельзя:

SELECT n
FROM generate_series(1, 10, 0) AS s(n);

Нулевой шаг не имеет смысла: функция никогда не сдвинется с места.

PostgreSQL вернёт ошибку.

Поэтому шаг должен быть:

  • положительным, если идём вверх;
  • отрицательным, если идём вниз;
  • не равным нулю.

Дробные значения через numeric

generate_series можно использовать не только с целыми числами.

Для типа numeric можно задать дробный шаг.

SELECT n
FROM generate_series(0::numeric, 1::numeric, 0.25::numeric) AS s(n);

Результат:

n
----
0
0.25
0.50
0.75
1.00

Это удобно для тестовых данных, шкал, коэффициентов и учебных примеров.

Но в аналитике чаще всего generate_series используют для целых чисел и дат.

Календарная ось по дням

Главный практический сценарий — создать календарь.

Например, получить все дни января:

SELECT day::date AS day
FROM generate_series(
  '2024-01-01'::timestamp,
  '2024-01-31'::timestamp,
  interval '1 day'
) AS s(day);

Результат:

day
----------
2024-01-01
2024-01-02
2024-01-03
...
2024-01-31

Здесь:

'2024-01-01'::timestamp

начало ряда.

'2024-01-31'::timestamp

конец ряда.

interval '1 day'

шаг в один день.

Мы приводим результат к date:

day::date

чтобы получить именно дату без времени.

Границы включены

У generate_series границы включены с обеих сторон.

Например:

SELECT day::date
FROM generate_series(
  '2024-01-01'::timestamp,
  '2024-01-03'::timestamp,
  interval '1 day'
) AS s(day);

Результат:

day
----------
2024-01-01
2024-01-02
2024-01-03

И начало, и конец попали в результат.

Это важно для календарных отчётов.

Если вы генерируете дни с 1 по 31 января, получите 31 строку.

Если нужна полуоткрытая логика:

[start, end)

то есть начало включено, конец не включён, верхнюю границу нужно задавать аккуратно.

Например, для января можно генерировать до 31 января включительно:

generate_series(
  '2024-01-01'::timestamp,
  '2024-01-31'::timestamp,
  interval '1 day'
)

А можно мыслить через «первое число следующего месяца минус один день»:

generate_series(
  '2024-01-01'::timestamp,
  '2024-02-01'::timestamp - interval '1 day',
  interval '1 day'
)

Ось по часам

generate_series работает и с часами.

Например, получить все часы одного дня:

SELECT ts
FROM generate_series(
  '2024-01-01 00:00'::timestamp,
  '2024-01-01 23:00'::timestamp,
  interval '1 hour'
) AS s(ts);

Результат:

ts
-------------------
2024-01-01 00:00:00
2024-01-01 01:00:00
2024-01-01 02:00:00
...
2024-01-01 23:00:00

Здесь будет 24 строки.

Если поставить конец:

'2024-01-02 00:00'::timestamp

то в результат попадёт и полночь следующего дня, потому что правая граница включена.

Это частая ошибка.

Для почасового отчёта за один день обычно нужно:

'2024-01-01 00:00'

до:

'2024-01-01 23:00'

или использовать полуоткрытую логику при соединении с фактами.

Ось по 15 минут

Шаг может быть любым интервалом.

Например, сетка по 15 минут:

SELECT slot
FROM generate_series(
  '2024-01-01 09:00'::timestamp,
  '2024-01-01 10:00'::timestamp,
  interval '15 minutes'
) AS s(slot);

Результат:

slot
-------------------
2024-01-01 09:00:00
2024-01-01 09:15:00
2024-01-01 09:30:00
2024-01-01 09:45:00
2024-01-01 10:00:00

Такие сетки полезны для:

  • слотов бронирования;
  • расписаний;
  • мониторинга;
  • графиков нагрузки;
  • отчётов по тайм-бакетам.

Ось по месяцам

Можно генерировать месяцы.

SELECT month::date AS month
FROM generate_series(
  '2024-01-01'::timestamp,
  '2024-06-01'::timestamp,
  interval '1 month'
) AS s(month);

Результат:

month
----------
2024-01-01
2024-02-01
2024-03-01
2024-04-01
2024-05-01
2024-06-01

Для месячных отчётов обычно удобно генерировать первые числа месяцев.

Потом данные соединяют по интервалу:

created_at >= month
created_at < month + interval '1 month'

Это надёжнее, чем сравнивать только номер месяца.

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

Обычный GROUP BY показывает только те периоды, где есть строки.

Например, есть заказы:

created_at          | amount | status
--------------------+--------+-------
2024-01-01 10:00:00 | 1000   | paid
2024-01-03 12:00:00 | 1500   | paid

Если сгруппировать по дню:

SELECT
  created_at::date AS day,
  SUM(amount) AS revenue
FROM orders
WHERE status = 'paid'
GROUP BY created_at::date
ORDER BY day;

Результат:

day        | revenue
-----------+--------
2024-01-01 | 1000
2024-01-03 | 1500

А где 2 января?

Его нет.

Потому что в таблице orders нет строки за этот день.

Но для графика или отчёта часто нужно видеть:

2024-01-01 | 1000
2024-01-02 | 0
2024-01-03 | 1500

Вот здесь и нужен generate_series.

Календарь слева, данные справа

Правильный паттерн:

  1. сгенерировать полный календарь;
  2. поставить его слева;
  3. через LEFT JOIN присоединить реальные данные;
  4. заменить NULL на 0 через COALESCE.
SELECT
  cal.day::date AS day,
  COALESCE(SUM(o.amount), 0) AS revenue
FROM generate_series(
  '2024-01-01'::timestamp,
  '2024-01-31'::timestamp,
  interval '1 day'
) AS cal(day)
LEFT JOIN orders o
  ON o.created_at >= cal.day
 AND o.created_at < cal.day + interval '1 day'
 AND o.status = 'paid'
GROUP BY cal.day
ORDER BY cal.day;

Теперь результат будет содержать все дни января.

Если в какой-то день заказов не было, SUM(o.amount) даст NULL, а COALESCE превратит его в 0.

COALESCE(SUM(o.amount), 0)

Это ключевой приём для отчётов без дыр.

Почему фильтр status должен быть в ON

В запросе выше условие:

o.status = 'paid'

находится внутри ON.

LEFT JOIN orders o
  ON o.created_at >= cal.day
 AND o.created_at < cal.day + interval '1 day'
 AND o.status = 'paid'

Это важно.

Если перенести его во внешний WHERE:

WHERE o.status = 'paid'

то дни без заказов исчезнут.

Почему?

Потому что у дней без заказов справа будет NULL.

А условие:

o.status = 'paid'

для NULL не будет истинным.

В результате LEFT JOIN фактически превратится в поведение, похожее на INNER JOIN.

Главное правило:

Если вы сохраняете пустые периоды через LEFT JOIN, условия на правую таблицу обычно кладите в ON, а не во внешний WHERE.

Почему лучше соединять по полуоткрытому интервалу

Иногда пишут так:

ON o.created_at::date = cal.day::date

Это работает, но часто хуже для производительности.

Лучше писать через границы периода:

ON o.created_at >= cal.day
AND o.created_at < cal.day + interval '1 day'

Почему?

Потому что в таком варианте колонка o.created_at остаётся «голой», без функции.

Это помогает использовать индекс по created_at.

Хороший индекс для такого отчёта:

CREATE INDEX orders_created_status_idx
ON orders (created_at, status);

или в зависимости от запросов:

CREATE INDEX orders_status_created_idx
ON orders (status, created_at);

Идея:

не оборачивать indexed column в функцию, если можно сравнить её с границами

Заполнение пропусков по часам

Та же логика работает для часов.

Например, нужно получить выручку по каждому часу за день.

SELECT
  cal.hour AS hour,
  COALESCE(SUM(o.amount), 0) AS revenue
FROM generate_series(
  '2024-01-01 00:00'::timestamp,
  '2024-01-01 23:00'::timestamp,
  interval '1 hour'
) AS cal(hour)
LEFT JOIN orders o
  ON o.created_at >= cal.hour
 AND o.created_at < cal.hour + interval '1 hour'
 AND o.status = 'paid'
GROUP BY cal.hour
ORDER BY cal.hour;

Если в какой-то час не было заказов, он всё равно появится в результате с 0.

Это удобно для графиков активности, мониторинга, продаж и нагрузки.

Заполнение пропусков по месяцам

Для месяцев используем шаг 1 month.

SELECT
  cal.month::date AS month,
  COALESCE(SUM(o.amount), 0) AS revenue
FROM generate_series(
  '2024-01-01'::timestamp,
  '2024-12-01'::timestamp,
  interval '1 month'
) AS cal(month)
LEFT JOIN orders o
  ON o.created_at >= cal.month
 AND o.created_at < cal.month + interval '1 month'
 AND o.status = 'paid'
GROUP BY cal.month
ORDER BY cal.month;

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

COUNT и нули

Для COUNT есть нюанс.

Если вы пишете:

COUNT(*)

после LEFT JOIN, то день без заказов всё равно даст 1, потому что строка календаря существует.

Поэтому для подсчёта заказов нужно считать колонку из правой таблицы:

COUNT(o.id)

Пример:

SELECT
  cal.day::date AS day,
  COUNT(o.id) AS orders_count,
  COALESCE(SUM(o.amount), 0) AS revenue
FROM generate_series(
  '2024-01-01'::timestamp,
  '2024-01-31'::timestamp,
  interval '1 day'
) AS cal(day)
LEFT JOIN orders o
  ON o.created_at >= cal.day
 AND o.created_at < cal.day + interval '1 day'
 AND o.status = 'paid'
GROUP BY cal.day
ORDER BY cal.day;

Для дня без заказов:

COUNT(o.id) = 0
SUM(o.amount) = NULL
COALESCE(SUM(o.amount), 0) = 0

Главное правило:

После LEFT JOIN для подсчёта фактов считайте COUNT(fact_table.id), а не COUNT(*).

generate_series с LATERAL

generate_series отлично работает вместе с LATERAL.

Это нужно, когда для каждой строки внешней таблицы нужно создать свой ряд.

Например, для каждого пользователя сделать 3 контрольные точки после регистрации:

  • через 7 дней;
  • через 14 дней;
  • через 21 день.
SELECT
  u.id,
  u.email,
  u.created_at + (g.n * interval '7 days') AS checkpoint
FROM users u
CROSS JOIN LATERAL generate_series(1, 3) AS g(n);

Результат:

id | email         | checkpoint
---+---------------+---------------------
1  | anna@mail.com | created_at + 7 days
1  | anna@mail.com | created_at + 14 days
1  | anna@mail.com | created_at + 21 days
2  | bob@mail.com  | created_at + 7 days
2  | bob@mail.com  | created_at + 14 days
2  | bob@mail.com  | created_at + 21 days

Здесь generate_series(1, 3) создаёт три строки на каждого пользователя.

Сгенерировать месяцы для каждого пользователя

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

SELECT
  u.id,
  u.email,
  m.month::date AS month
FROM users u
CROSS JOIN LATERAL generate_series(
  date_trunc('month', u.created_at),
  date_trunc('month', now()),
  interval '1 month'
) AS m(month)
ORDER BY u.id, m.month;

Здесь generate_series использует u.created_at из текущей строки пользователя.

То есть для каждого пользователя будет свой диапазон месяцев.

Пользователь, зарегистрированный в январе, получит месяцы с января. Пользователь, зарегистрированный в мае, получит месяцы с мая.

Это хороший пример связки:

LATERAL + generate_series

Табличные функции и LATERAL

В PostgreSQL функции в FROM, которые ссылаются на колонки слева, часто могут работать как LATERAL даже без явного слова LATERAL.

Например:

SELECT
  u.id,
  g.n
FROM users u
CROSS JOIN generate_series(1, u.id) AS g(n);

Но на практике часто лучше писать явно:

SELECT
  u.id,
  g.n
FROM users u
CROSS JOIN LATERAL generate_series(1, u.id) AS g(n);

Так запрос читается честнее: сразу видно, что правая часть зависит от строки слева.

Генерация тестовых данных

generate_series часто используют для быстрой генерации тестовых данных.

Например, создать 100 тестовых пользователей:

INSERT INTO users (email, created_at)
SELECT
  'user' || n || '@mail.com' AS email,
  now() - (n || ' days')::interval AS created_at
FROM generate_series(1, 100) AS s(n);

Или создать тестовые заказы:

INSERT INTO orders (user_id, amount, status, created_at)
SELECT
  (random() * 10)::int + 1 AS user_id,
  round((random() * 1000)::numeric, 2) AS amount,
  'paid' AS status,
  now() - (n || ' hours')::interval AS created_at
FROM generate_series(1, 500) AS s(n);

Для учебных баз, демо и тестов это очень удобно.

generate_series и временные зоны

Если вы строите календарь по датам, часто проще генерировать даты или timestamp на полуночь.

Например:

SELECT day::date
FROM generate_series(
  '2024-01-01'::timestamp,
  '2024-01-31'::timestamp,
  interval '1 day'
) AS s(day);

Если вы работаете с timestamptz и часовыми поясами, особенно на границах перехода на летнее/зимнее время, нужно быть внимательнее.

В таких случаях лучше явно понимать:

  • в каком часовом поясе хранится created_at;
  • в каком часовом поясе строится отчёт;
  • что считается календарным днём для бизнеса;
  • как обрабатываются дни с 23 или 25 часами.

Для большинства отчётов по дням хорошая практика — сначала определить бизнес-границы дня, а потом соединять факты по полуоткрытым интервалам.

MySQL: рекурсивный CTE вместо generate_series

В MySQL нет встроенной функции generate_series как в PostgreSQL.

Обычно числовой ряд делают через рекурсивный CTE.

Например, числа от 1 до 31:

WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1
  FROM seq
  WHERE n < 31
)
SELECT n
FROM seq;

Результат:

n
--
1
2
3
...
31

Календарь дат:

WITH RECURSIVE cal AS (
  SELECT DATE '2024-01-01' AS day
  UNION ALL
  SELECT day + INTERVAL 1 DAY
  FROM cal
  WHERE day < DATE '2024-01-31'
)
SELECT day
FROM cal;

В MySQL есть лимит рекурсии. По умолчанию это часто 1000 итераций через настройку cte_max_recursion_depth.

Для длинных диапазонов это нужно учитывать.

MariaDB: sequence-таблицы

В MariaDB есть отдельный механизм sequence-таблиц.

Например, можно встретить виртуальные таблицы вида:

seq_1_to_100

или:

seq_0_to_100_step_10

Идея похожая: получить ряд чисел как таблицу.

Но это не тот же синтаксис, что generate_series в PostgreSQL.

При переносе запросов между PostgreSQL, MySQL и MariaDB такие места почти всегда нужно переписывать.

ClickHouse: numbers, range и arrayJoin

В ClickHouse есть свои способы получить ряд.

Например, numbers(5):

SELECT number
FROM numbers(5);

Результат:

number
------
0
1
2
3
4

Если нужны числа от 1 до 5:

SELECT number + 1 AS n
FROM numbers(5);

Ещё вариант — range вместе с arrayJoin:

SELECT arrayJoin(range(1, 6)) AS n;

Результат:

n
-
1
2
3
4
5

Важно: range(1, 6) в ClickHouse даёт значения от 1 до 5, правая граница не включается.

Это отличается от PostgreSQL generate_series, где правая граница включается, если достижима шагом.

Для дат и временных рядов в ClickHouse обычно используют комбинации функций даты, numbers, range, arrayJoin и приведения типов.

Практические шаблоны

Числа от 1 до 10

SELECT n
FROM generate_series(1, 10) AS s(n);

Числа от 0 до 100 с шагом 10

SELECT n
FROM generate_series(0, 100, 10) AS s(n);

Обратный ряд от 10 до 1

SELECT n
FROM generate_series(10, 1, -1) AS s(n);

Даты за январь

SELECT day::date AS day
FROM generate_series(
  '2024-01-01'::timestamp,
  '2024-01-31'::timestamp,
  interval '1 day'
) AS s(day);

Часы за сутки

SELECT hour
FROM generate_series(
  '2024-01-01 00:00'::timestamp,
  '2024-01-01 23:00'::timestamp,
  interval '1 hour'
) AS s(hour);

Слоты по 15 минут

SELECT slot
FROM generate_series(
  '2024-01-01 09:00'::timestamp,
  '2024-01-01 18:00'::timestamp,
  interval '15 minutes'
) AS s(slot);

Выручка по дням с нулями

SELECT
  cal.day::date AS day,
  COALESCE(SUM(o.amount), 0) AS revenue
FROM generate_series(
  '2024-01-01'::timestamp,
  '2024-01-31'::timestamp,
  interval '1 day'
) AS cal(day)
LEFT JOIN orders o
  ON o.created_at >= cal.day
 AND o.created_at < cal.day + interval '1 day'
 AND o.status = 'paid'
GROUP BY cal.day
ORDER BY cal.day;

Количество заказов по дням с нулями

SELECT
  cal.day::date AS day,
  COUNT(o.id) AS orders_count
FROM generate_series(
  '2024-01-01'::timestamp,
  '2024-01-31'::timestamp,
  interval '1 day'
) AS cal(day)
LEFT JOIN orders o
  ON o.created_at >= cal.day
 AND o.created_at < cal.day + interval '1 day'
GROUP BY cal.day
ORDER BY cal.day;

Выручка по месяцам

SELECT
  cal.month::date AS month,
  COALESCE(SUM(o.amount), 0) AS revenue
FROM generate_series(
  '2024-01-01'::timestamp,
  '2024-12-01'::timestamp,
  interval '1 month'
) AS cal(month)
LEFT JOIN orders o
  ON o.created_at >= cal.month
 AND o.created_at < cal.month + interval '1 month'
 AND o.status = 'paid'
GROUP BY cal.month
ORDER BY cal.month;

Три контрольные точки после регистрации

SELECT
  u.id,
  u.created_at + (g.n * interval '7 days') AS checkpoint
FROM users u
CROSS JOIN LATERAL generate_series(1, 3) AS g(n);

Месяцы жизни пользователя

SELECT
  u.id,
  m.month::date AS month
FROM users u
CROSS JOIN LATERAL generate_series(
  date_trunc('month', u.created_at),
  date_trunc('month', now()),
  interval '1 month'
) AS m(month);

MySQL: числа через recursive CTE

WITH RECURSIVE seq AS (
  SELECT 1 AS n
  UNION ALL
  SELECT n + 1
  FROM seq
  WHERE n < 31
)
SELECT n
FROM seq;

MySQL: календарь через recursive CTE

WITH RECURSIVE cal AS (
  SELECT DATE '2024-01-01' AS day
  UNION ALL
  SELECT day + INTERVAL 1 DAY
  FROM cal
  WHERE day < DATE '2024-01-31'
)
SELECT day
FROM cal;

ClickHouse: числа через numbers

SELECT number + 1 AS n
FROM numbers(5);

ClickHouse: числа через range и arrayJoin

SELECT arrayJoin(range(1, 6)) AS n;

Частые ошибки

Забыли, что правая граница включена

SELECT ts
FROM generate_series(
  '2024-01-01 00:00'::timestamp,
  '2024-01-02 00:00'::timestamp,
  interval '1 hour'
) AS s(ts);

Такой запрос вернёт и 2024-01-02 00:00:00.

Если нужен только день 1 января по часам, верхнюю границу задавайте до 23:00.

Поставили фильтр правой таблицы в WHERE

Плохо для заполнения нулями:

WHERE o.status = 'paid'

после LEFT JOIN.

Лучше:

LEFT JOIN orders o
  ON ...
 AND o.status = 'paid'

Иначе пустые дни исчезнут.

Использовали COUNT(*)

После календарного LEFT JOIN плохо:

COUNT(*)

Для дней без заказов он может дать 1.

Лучше:

COUNT(o.id)

Обернули дату заказа в функцию

Менее удачно:

ON o.created_at::date = cal.day::date

Лучше:

ON o.created_at >= cal.day
AND o.created_at < cal.day + interval '1 day'

Так индексу по created_at легче помочь запросу.

Шаг направлен не туда

Пустой результат:

SELECT n
FROM generate_series(10, 1, 1) AS s(n);

Нужно:

SELECT n
FROM generate_series(10, 1, -1) AS s(n);

Что важно запомнить

generate_series создаёт набор строк от начала до конца с заданным шагом.

Пример:

SELECT n
FROM generate_series(1, 5) AS s(n);

Результат:

1
2
3
4
5

Главные правила:

  • generate_series — табличная функция;
  • её обычно используют в FROM;
  • границы включены с обеих сторон;
  • шаг можно указать третьим аргументом;
  • шаг не может быть нулевым;
  • шаг может быть отрицательным;
  • если шаг направлен не туда, результат будет пустым;
  • для дат и времени шаг задаётся через interval;
  • для календарных отчётов календарь ставят слева в LEFT JOIN;
  • COALESCE превращает пустые агрегаты в нули;
  • условия на правую таблицу при LEFT JOIN часто нужно писать в ON;
  • для подсчёта фактов используйте COUNT(fact_table.id), а не COUNT(*);
  • с LATERAL можно генерировать отдельный ряд для каждой строки внешней таблицы.

Короткий вывод

generate_series нужен, когда вам нужна искусственная, но полезная ось данных.

Например, календарь:

SELECT day::date
FROM generate_series(
  '2024-01-01'::timestamp,
  '2024-01-31'::timestamp,
  interval '1 day'
) AS s(day);

Или отчёт по дням без пропусков:

SELECT
  cal.day::date AS day,
  COALESCE(SUM(o.amount), 0) AS revenue
FROM generate_series(
  '2024-01-01'::timestamp,
  '2024-01-31'::timestamp,
  interval '1 day'
) AS cal(day)
LEFT JOIN orders o
  ON o.created_at >= cal.day
 AND o.created_at < cal.day + interval '1 day'
 AND o.status = 'paid'
GROUP BY cal.day
ORDER BY cal.day;

Главная мысль:

GROUP BY показывает только периоды, где есть данные. generate_series создаёт полную ось, чтобы пустые периоды тоже появились в отчёте.

Именно поэтому generate_series так часто используют в аналитике, календарях, графиках, тестовых данных и задачах с заполнением пропусков нулями.

PreviousLATERAL JOIN in PostgreSQL: Top-N per Group and Correlated FROM Subqueries

Related articles

What is WITH … AS (CTE) in SQL? Common Table Expressions for beginnersMultiple CTEs in one query: chained WITH for beginnersRecursive CTEs in SQL: WITH RECURSIVE for Trees, Graphs and Number Series

Vadite na resničnih nalogah

Rešujte naloge v vadnici SQL s takojšnjim ocenjevanjem in namigi.

Odpri vadnico