Рекурсивные CTE и продвинутые паттерны¶

Зачем это DE?¶

Иерархии категорий, орг-структуры, графы зависимостей, генерация дат — всё это требует рекурсивных CTE. А LATERAL JOIN, GROUPING SETS и generate_series — мощные инструменты, которые сокращают десятки строк SQL до нескольких.

Рекурсивные CTE¶

Синтаксис¶

SQL

WITH RECURSIVE cte_name AS (
    -- Якорь (anchor): стартовые строки
    SELECT ... FROM ...
    UNION ALL
    -- Рекурсивный член: ссылается на cte_name
    SELECT ... FROM ... JOIN cte_name ON ...
)
SELECT * FROM cte_name;

Как работает:

Выполняется якорь → результат = итерация 0
Рекурсивный член берёт строки из итерации 0 → даёт итерацию 1
Повторяется, пока рекурсивный член не вернёт пустой набор
Все итерации объединяются через UNION ALL

Пример: дерево категорий¶

SQL

-- Таблица categories: parent_id → category_id (self-reference)
WITH RECURSIVE tree AS (
    -- Якорь: корневые категории (без родителя)
    SELECT category_id, name, parent_id, 
           0 AS depth,
           name::text AS path
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    -- Рекурсия: дети текущего уровня
    SELECT c.category_id, c.name, c.parent_id,
           t.depth + 1,
           t.path || ' → ' || c.name
    FROM categories c
    JOIN tree t ON c.parent_id = t.category_id
)
SELECT * FROM tree ORDER BY path;

category_id	name	depth	path
1	Электроника	0	Электроника
2	Смартфоны	1	Электроника → Смартфоны
3	Аксессуары	2	Электроника → Смартфоны → Аксессуары

Пример: орг-структура сотрудников¶

SQL

WITH RECURSIVE org AS (
    -- Якорь: CEO (нет руководителя)
    SELECT employee_id, name, manager_id, 
           1 AS level,
           ARRAY[employee_id] AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.employee_id, e.name, e.manager_id,
           o.level + 1,
           o.path || e.employee_id
    FROM employees e
    JOIN org o ON e.manager_id = o.employee_id
)
SELECT 
    repeat('  ', level - 1) || name AS org_chart,
    level
FROM org
ORDER BY path;

Защита от бесконечных циклов¶

SQL

WITH RECURSIVE tree AS (
    SELECT category_id, name, parent_id, 
           1 AS depth,
           ARRAY[category_id] AS visited  -- отслеживаем посещённые
    FROM categories
    WHERE parent_id IS NULL

    UNION ALL

    SELECT c.category_id, c.name, c.parent_id,
           t.depth + 1,
           t.visited || c.category_id
    FROM categories c
    JOIN tree t ON c.parent_id = t.category_id
    WHERE c.category_id != ALL(t.visited)  -- не заходим повторно
      AND t.depth < 100                     -- лимит глубины
)
SELECT * FROM tree;

Всегда ограничивай глубину

Без ограничения рекурсия может зависнуть. Используй WHERE depth < N или проверку на циклы.

generate_series — генерация данных¶

Генерация дат¶

SQL

-- Все дни за 2025 год
SELECT d::date AS day
FROM generate_series('2025-01-01', '2025-12-31', '1 day'::interval) AS d;

-- Заполнить пропуски в данных (LEFT JOIN с calendar)
WITH calendar AS (
    SELECT d::date AS day
    FROM generate_series('2025-01-01', '2025-01-31', '1 day'::interval) AS d
)
SELECT 
    cal.day,
    COALESCE(COUNT(o.order_id), 0) AS orders_count
FROM calendar cal
LEFT JOIN orders o ON o.order_date = cal.day
GROUP BY cal.day
ORDER BY cal.day;

Генерация числовых рядов¶

SQL

-- Бакеты для гистограммы
SELECT 
    bucket * 1000 AS price_from,
    (bucket + 1) * 1000 AS price_to,
    COUNT(p.product_id) AS products
FROM generate_series(0, 9) AS bucket
LEFT JOIN products p 
    ON p.price >= bucket * 1000 
   AND p.price < (bucket + 1) * 1000
GROUP BY bucket
ORDER BY bucket;

LATERAL JOIN¶

LATERAL позволяет подзапросу ссылаться на предыдущие таблицы в FROM. Как коррелированный подзапрос, но в FROM.

Top-N для каждой группы¶

SQL

-- Топ-3 заказа для каждого клиента
SELECT c.name, top_orders.*
FROM customers c
CROSS JOIN LATERAL (
    SELECT order_id, total_amount, order_date
    FROM orders o
    WHERE o.customer_id = c.customer_id  -- ссылка на c
    ORDER BY total_amount DESC
    LIMIT 3
) AS top_orders;

LATERAL vs оконные функции

Для Top-N задач LATERAL часто быстрее ROW_NUMBER(), потому что PostgreSQL может использовать индекс и остановиться после N строк.

Распаковка JSON-массива¶

SQL

-- products.attributes содержит {"colors": ["red", "blue", "green"]}
SELECT p.name, color.value AS color
FROM products p
CROSS JOIN LATERAL jsonb_array_elements_text(
    p.attributes -> 'colors'
) AS color(value);

GROUPING SETS, ROLLUP, CUBE¶

Проблема: несколько уровней агрегации¶

Нужно в одном запросе получить: итого по городам, по месяцам и общий итог.

GROUPING SETS¶

SQL

SELECT 
    c.city,
    EXTRACT(MONTH FROM o.order_date) AS month,
    SUM(o.total_amount) AS revenue
FROM orders o
JOIN customers c USING (customer_id)
GROUP BY GROUPING SETS (
    (c.city, EXTRACT(MONTH FROM o.order_date)),  -- город + месяц
    (c.city),                                      -- только город
    (EXTRACT(MONTH FROM o.order_date)),            -- только месяц
    ()                                              -- общий итог
)
ORDER BY city NULLS LAST, month NULLS LAST;

GROUPING() определяет, какой уровень:

SQL

SELECT 
    CASE WHEN GROUPING(c.city) = 1 THEN 'ВСЕ' ELSE c.city END AS city,
    CASE WHEN GROUPING(month) = 1 THEN 'ВСЕ' ELSE month::text END AS month,
    SUM(total_amount) AS revenue
FROM ...
GROUP BY GROUPING SETS ((city, month), (city), (month), ())

ROLLUP — иерархическая агрегация¶

SQL

-- Эквивалент GROUPING SETS ((city, month), (city), ())
SELECT city, month, SUM(revenue)
FROM sales
GROUP BY ROLLUP (city, month);
-- city + month → city → итого

CUBE — все комбинации¶

SQL

-- Эквивалент GROUPING SETS ((city, month), (city), (month), ())
SELECT city, month, SUM(revenue)
FROM sales
GROUP BY CUBE (city, month);

Продвинутые паттерны¶

Anti-join: строки без пары¶

SQL

-- Клиенты без заказов
-- Вариант 1: LEFT JOIN + IS NULL (классика)
SELECT c.*
FROM customers c
LEFT JOIN orders o ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

-- Вариант 2: NOT EXISTS (часто быстрее)
SELECT c.*
FROM customers c
WHERE NOT EXISTS (
    SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

Self-join: сравнение строки с собой¶

SQL

-- Сотрудники, зарабатывающие больше руководителя
SELECT 
    e.name AS employee,
    e.salary AS emp_salary,
    m.name AS manager,
    m.salary AS mgr_salary
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id
WHERE e.salary > m.salary;

Pivot через CASE¶

SQL

-- Заказы по месяцам → столбцы
SELECT 
    c.city,
    SUM(CASE WHEN EXTRACT(MONTH FROM o.order_date) = 1 THEN o.total_amount END) AS jan,
    SUM(CASE WHEN EXTRACT(MONTH FROM o.order_date) = 2 THEN o.total_amount END) AS feb,
    SUM(CASE WHEN EXTRACT(MONTH FROM o.order_date) = 3 THEN o.total_amount END) AS mar
FROM orders o
JOIN customers c USING (customer_id)
GROUP BY c.city;

Что запомнить¶

Рекурсивные CTE — якорь + рекурсивный член через UNION ALL, всегда ограничивай глубину
generate_series — заполнение пропусков в датах, бакеты для гистограмм
LATERAL JOIN — подзапрос, который ссылается на предыдущие таблицы (Top-N на группу)
GROUPING SETS — несколько уровней агрегации в одном запросе
ROLLUP — иерархические подытоги (город → месяц → итого)
CUBE — все комбинации измерений (OLAP-стиль)
Anti-join через NOT EXISTS часто быстрее LEFT JOIN + IS NULL
Pivot через CASE + агрегаты — единственный переносимый способ транспонировать данные

Рекурсивные CTE и продвинутые паттерны¶

Зачем это DE?¶