Дървета и редици: WITH RECURSIVE

Накратко: WITH RECURSIVE е заявка със стъпка, която чете собствения си резултат, и така обхожда дървета като категориите или прави редица от дати.

Проблем

Пътят до категорията

Сайтът показва над всеки продукт пътя до него: „Храни > Млечни“. Категориите са дърво: всяка има parent_id, който сочи категорията над нея. С self join от модула за свързване става така:

SQLбаза: магазин

SELECT c.name, p.name AS parent
FROM categories c
LEFT JOIN categories p ON p.id = c.parent_id;

SELECT c.name, p.name AS parent FROM categories c LEFT JOIN categories p ON p.id = c.parent_id;

▶ ИзпълниВърни заявкатаНова база

Това работи, защото дървото е на две нива. Ако утре под „Млечни“ се появи „Сирена“, трябва още едно свързване, а при четири нива още едно. Колко нива ще има, не знаеш предварително.

Идеята

Стъпка, която чете себе си

WITH RECURSIVE позволява на една стъпка да чете собствените си редове. Ето най-малкия пример, числата от 1 до 5:

SQLбаза: магазин

WITH RECURSIVE n(x) AS (
  SELECT 1
  UNION ALL
  SELECT x + 1 FROM n WHERE x < 5
)
SELECT x FROM n;

WITH RECURSIVE n(x) AS ( SELECT 1 UNION ALL SELECT x + 1 FROM n WHERE x < 5 ) SELECT x FROM n;

▶ ИзпълниВърни заявкатаНова база

Стъпката има две части, свързани с UNION ALL:

1. Началото SELECT 1 дава първия ред.
2. Рекурсивната част SELECT x + 1 FROM n WHERE x < 5 взима редовете от предишния кръг и прави нови: от 1 прави 2, от 2 прави 3.

Това се повтаря, докато рекурсивната част не върне нищо: при x = 5 условието вече не е вярно. Такава стъпка се казва рекурсивна (recursive CTE).

Без WHERE x < 5 заявката никога няма да спре. Всяка рекурсивна част трябва да има условие, което рано или късно спира.

Идеята

Дървото на категориите

Сега пътят. Началото са главните категории (без родител). Рекурсивната част намира децата на всичко, намерено досега, и добавя името им към пътя:

SQLбаза: магазин

WITH RECURSIVE tree(id, name, path, level) AS (
  SELECT id, name, name, 1
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, t.path || ' > ' || c.name, t.level + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT path, level FROM tree
ORDER BY path;

WITH RECURSIVE tree(id, name, path, level) AS ( SELECT id, name, name, 1 FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, t.path || ' > ' || c.name, t.level + 1 FROM categories c JOIN tree t ON c.parent_id = t.id ) SELECT path, level FROM tree ORDER BY path;

▶ ИзпълниВърни заявкатаНова база

В първия кръг са „Храни“ и „Дом“ (ниво 1). Във втория са шестте подкатегории (ниво 2), всяка с пътя на родителя, „ > “ и своето име. В третия кръг няма деца и всичко спира. Ако дървото стане по-дълбоко, същата заявка ще стигне и до новите нива.

Пробвай

Всичко в „Храни“

Покажи името и цената на всички продукти в „Храни“, заедно с подкатегориите ѝ. Номерата на категориите вземи с рекурсивна стъпка, а не на ръка. Полето започва от началото на стъпката.

SQLбаза: магазин

SELECT id FROM categories WHERE name = 'Храни';

SELECT id FROM categories WHERE name = 'Храни';

Провери▶ Изпълни

Виж го

Дните без продажби

Управителят иска графика на продажбите по дни за октомври 2026 г. Първият опит:

SQLбаза: магазин

SELECT o.ordered_at, ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.ordered_at BETWEEN '2026-10-01' AND '2026-10-31'
GROUP BY o.ordered_at;

SELECT o.ordered_at, ROUND(SUM(oi.quantity * oi.unit_price), 2) AS revenue FROM orders o JOIN order_items oi ON oi.order_id = o.id WHERE o.ordered_at BETWEEN '2026-10-01' AND '2026-10-31' GROUP BY o.ordered_at;

▶ ИзпълниВърни заявкатаНова база

Пет реда. Октомври има 31 дни, а графиката ще свърже 3 октомври направо с 5-и и ще скрие, че от 7 до 17 октомври не е продадено нищо. Ден без поръчки го няма в orders, затова GROUP BY няма от какво да направи ред.

Идеята

Редица от дати

Рекурсивна стъпка може да направи всички дни сама. Началото е 1 октомври, а всеки кръг добавя един ден с date(day, '+1 day'):

SQLбаза: магазин

WITH RECURSIVE days(day) AS (
  SELECT '2026-10-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM days WHERE day < '2026-10-31'
)
SELECT day FROM days;

WITH RECURSIVE days(day) AS ( SELECT '2026-10-01' UNION ALL SELECT date(day, '+1 day') FROM days WHERE day < '2026-10-31' ) SELECT day FROM days;

▶ ИзпълниВърни заявкатаНова база

31 реда. Сега продажбите се закачат към дните с LEFT JOIN, а за дните без поръчки COALESCE слага 0:

SQLбаза: магазин

WITH RECURSIVE days(day) AS (
  SELECT '2026-10-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM days WHERE day < '2026-10-31'
)
SELECT d.day, COUNT(DISTINCT o.id) AS orders,
  COALESCE(ROUND(SUM(oi.quantity * oi.unit_price), 2), 0) AS revenue
FROM days d
LEFT JOIN orders o ON o.ordered_at = d.day
LEFT JOIN order_items oi ON oi.order_id = o.id
GROUP BY d.day
ORDER BY d.day;

WITH RECURSIVE days(day) AS ( SELECT '2026-10-01' UNION ALL SELECT date(day, '+1 day') FROM days WHERE day < '2026-10-31' ) SELECT d.day, COUNT(DISTINCT o.id) AS orders, COALESCE(ROUND(SUM(oi.quantity * oi.unit_price), 2), 0) AS revenue FROM days d LEFT JOIN orders o ON o.ordered_at = d.day LEFT JOIN order_items oi ON oi.order_id = o.id GROUP BY d.day ORDER BY d.day;

▶ ИзпълниВърни заявкатаНова база

Всички 31 дни са тук, и тези с нула. Редът на LEFT JOIN е важен: отляво са дните, които искаш всичките, отдясно са поръчките, които може да липсват.

По-точно, за любопитните

PostgreSQL има готова функция за редица: generate_series(DATE '2026-10-01', DATE '2026-10-31', INTERVAL '1 day'). В SQLite и в MySQL (от версия 8) такава редица се прави с WITH RECURSIVE, както тук.

Упражнение

Две задачи

1. Колко дни през октомври 2026 г. магазинът не е имал нито една поръчка?

Преброй дните от октомври 2026 г. без нито една поръчка, до края на месеца, включително дните след последната поръчка (колона quiet_days). Полето вече има редицата от дни.

SQLбаза: магазин

WITH RECURSIVE days(day) AS (
  SELECT '2026-10-01'
  UNION ALL
  SELECT date(day, '+1 day') FROM days WHERE day < '2026-10-31'
)
SELECT day FROM days;

WITH RECURSIVE days(day) AS ( SELECT '2026-10-01' UNION ALL SELECT date(day, '+1 day') FROM days WHERE day < '2026-10-31' ) SELECT day FROM days;

Провери▶ Изпълни

2. Сайтът иска пълния път за всеки продукт: „Храни > Млечни > Сирене“.

Покажи името на всеки продукт, който има категория, и пълния му път (колона breadcrumb), от главната категория до името на продукта. Полето вече има дървото.

SQLбаза: магазин

WITH RECURSIVE tree(id, name, path, level) AS (
  SELECT id, name, name, 1
  FROM categories
  WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, t.path || ' > ' || c.name, t.level + 1
  FROM categories c
  JOIN tree t ON c.parent_id = t.id
)
SELECT path, level FROM tree;

WITH RECURSIVE tree(id, name, path, level) AS ( SELECT id, name, name, 1 FROM categories WHERE parent_id IS NULL UNION ALL SELECT c.id, c.name, t.path || ' > ' || c.name, t.level + 1 FROM categories c JOIN tree t ON c.parent_id = t.id ) SELECT path, level FROM tree;

Провери▶ Изпълни

Тест

Провери се

1. От какви две части е рекурсивната стъпка?

SELECT и WHEREНачало, UNION ALL и част, която чете самата стъпкаДве CTE с еднакво име

2. Какво става, ако махнеш WHERE x < 5 от рекурсивната част?

Заявката никога не свършваНищо: базата сама спираВръща 1 до 5

3. Отчет по дни показва само 5 дни от октомври. Защо?

GROUP BY прави редове само за датите, които ги има в ordersЗащото BETWEEN не включва краяЗащото датите са текст

4. Защо в пътя „Храни > Млечни“ рекурсивната стъпка е по-добра от self join?

По-бърза еНе иска parent_idРаботи при всякаква дълбочина на дървото, не само при две нива

Провери

Започни урока отначало

Свързани уроци

• Нужно отпреди: LEFT JOIN: и редовете без двойка · Много таблици и таблица със себе си · Подзаявки · Отчет на стъпки: WITH
• Продължава в: Графови бази
• Задачи по темата: Подзаявки и прозорци