WITH: именованные запросы

168 мин

Сложные запросы быстро превращаются в путаницу: подзапрос в подзапросе… WITH (CTE — Common Table Expression) разбивает их на понятные именованные шаги.

Шаблон

WITH имя AS (
  SELECT …
)
SELECT … FROM имя;

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

Пример: студенты с высоким средним баллом

Сначала считаем средний балл каждого студента, потом пользуемся им:

university· Попробуйте изменить

WITH avg_scores AS (
  SELECT student_id, AVG(score) AS avg_score
  FROM grades
  GROUP BY student_id
)
SELECT s.first_name, s.last_name, ROUND(a.avg_score, 1) AS avg_score
FROM avg_scores a
JOIN students s ON s.id = a.student_id
WHERE a.avg_score >= 85
ORDER BY a.avg_score DESC;

То же самое можно написать подзапросом, но с WITH читать гораздо легче: у каждого шага есть имя.

Несколько CTE

Пишутся через запятую, каждый следующий может использовать предыдущий:

shop· Попробуйте изменить

WITH order_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS total
  FROM order_items
  GROUP BY order_id
),
big_orders AS (
  SELECT order_id FROM order_totals WHERE total > 10000000
)
SELECT o.payment_method, COUNT(*) AS big_orders
FROM big_orders b
JOIN orders o ON o.id = b.order_id
GROUP BY o.payment_method;

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

WITH RECURSIVE вызывает сам себя — для данных в виде дерева (руководитель → подчинённый → его подчинённый…):

company· Попробуйте изменить

WITH RECURSIVE chain AS (
  SELECT id, first_name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.first_name, e.manager_id, c.level + 1
  FROM employees e JOIN chain c ON e.manager_id = c.id
)
SELECT level, COUNT(*) AS people FROM chain GROUP BY level ORDER BY level;

Первая часть — точка старта (генеральный директор), вторая каждый раз спускается на уровень ниже.

1Упражнение

С помощью WITH посчитайте число завершённых поездок каждого водителя и выведите driver_id и число поездок для тех, у кого их больше 40.

Запомните

WITH имя AS (…) разбивает запрос на именованные шаги.
Несколько CTE — через запятую; следующий может использовать предыдущий.
WITH RECURSIVE — для деревьев и цепочек.

Шаблон

WITH имя AS (
  SELECT …
)
SELECT … FROM имя;

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

Пример: студенты с высоким средним баллом

Сначала считаем средний балл каждого студента, потом пользуемся им:

university· Попробуйте изменить

WITH avg_scores AS (
  SELECT student_id, AVG(score) AS avg_score
  FROM grades
  GROUP BY student_id
)
SELECT s.first_name, s.last_name, ROUND(a.avg_score, 1) AS avg_score
FROM avg_scores a
JOIN students s ON s.id = a.student_id
WHERE a.avg_score >= 85
ORDER BY a.avg_score DESC;

То же самое можно написать подзапросом, но с WITH читать гораздо легче: у каждого шага есть имя.

Несколько CTE

Пишутся через запятую, каждый следующий может использовать предыдущий:

shop· Попробуйте изменить

WITH order_totals AS (
  SELECT order_id, SUM(quantity * unit_price) AS total
  FROM order_items
  GROUP BY order_id
),
big_orders AS (
  SELECT order_id FROM order_totals WHERE total > 10000000
)
SELECT o.payment_method, COUNT(*) AS big_orders
FROM big_orders b
JOIN orders o ON o.id = b.order_id
GROUP BY o.payment_method;

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

WITH RECURSIVE вызывает сам себя — для данных в виде дерева (руководитель → подчинённый → его подчинённый…):

company· Попробуйте изменить

WITH RECURSIVE chain AS (
  SELECT id, first_name, manager_id, 1 AS level FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.id, e.first_name, e.manager_id, c.level + 1
  FROM employees e JOIN chain c ON e.manager_id = c.id
)
SELECT level, COUNT(*) AS people FROM chain GROUP BY level ORDER BY level;

Первая часть — точка старта (генеральный директор), вторая каждый раз спускается на уровень ниже.

1Упражнение

Запомните

WITH имя AS (…) разбивает запрос на именованные шаги.
Несколько CTE — через запятую; следующий может использовать предыдущий.
WITH RECURSIVE — для деревьев и цепочек.

SQLab

WITH: именованные запросы

Шаблон

Пример: студенты с высоким средним баллом

Несколько CTE

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

Запомните

WITH: именованные запросы

Шаблон

Пример: студенты с высоким средним баллом

Несколько CTE

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

Запомните

SQLab