Модуль: Подзапросы и CTE


Общие табличные выражения: WITH


Подзапросы становятся длинными

Запрос с подзапросом во FROM читается изнутри наружу: сначала разбираешься с подзапросом в середине, потом возвращаешься к внешней части.

Общее табличное выражение (CTE, Common Table Expression) — подзапрос, которому дали имя и вынесли перед основным запросом с помощью слова WITH:

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

Возьмём средние баллы учеников из прошлого урока и с помощью CTE выберем тех, у кого средний балл ниже 3,5:

WITH student_avg AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
)
SELECT s.name, ROUND(sa.avg_grade, 2) AS avg_grade
FROM students s
JOIN student_avg sa ON sa.student_id = s.id
WHERE sa.avg_grade < 3.5
ORDER BY sa.avg_grade, s.name;

Результат:

nameavg_grade
Егор Новиков2.75
Захар Волков3.0
Борис Иванов3.4

Запрос читается сверху вниз: «пусть student_avg — средние баллы учеников; выберем из неё тех, у кого меньше 3,5».

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

Сначала дай шагу имя в WITH, потом пользуйся им.


Несколько CTE

После WITH можно перечислить несколько выражений через запятую. Каждое следующее может использовать предыдущие.

Найдём классы, средний балл которых выше среднего балла школы:

WITH class_avg AS (
    SELECT s.class_id, AVG(g.grade) AS avg_grade
    FROM grades g
    JOIN students s ON s.id = g.student_id
    WHERE s.class_id IS NOT NULL
    GROUP BY s.class_id
),
school_avg AS (
    SELECT AVG(grade) AS avg_grade FROM grades
)
SELECT c.name, ROUND(ca.avg_grade, 2) AS avg_grade
FROM class_avg ca
JOIN classes c ON c.id = ca.class_id
WHERE ca.avg_grade > (SELECT avg_grade FROM school_avg)
ORDER BY c.name;

Результат:

nameavg_grade
7А4.12
8А4.06

Слово WITH пишется один раз, выражения разделяются запятыми. После последнего выражения запятой нет — дальше идёт основной SELECT.


CTE можно использовать несколько раз

CTE достаточно описать один раз, а обращаться к нему — сколько угодно раз. Подзапрос во FROM пришлось бы для этого переписывать.

Найдём ученика с самым низким средним баллом: средние нужны и для списка, и для поиска минимума.

WITH student_avg AS (
    SELECT student_id, AVG(grade) AS avg_grade
    FROM grades
    GROUP BY student_id
)
SELECT s.name, ROUND(sa.avg_grade, 2) AS avg_grade
FROM student_avg sa
JOIN students s ON s.id = sa.student_id
WHERE sa.avg_grade = (SELECT MIN(avg_grade) FROM student_avg);

Результат:

nameavg_grade
Егор Новиков2.75

Почему не ORDER BY avg_grade LIMIT 1? Учеников с одинаковым минимальным баллом может оказаться несколько, и LIMIT 1 молча оставит одного. Сравнение с MIN вернёт всех.

Имена столбцов в заголовке

Имена столбцов можно задать сразу после имени CTE: WITH student_avg(student_id, avg_grade) AS (…). Это удобно, когда столбцы подзапроса — длинные выражения.

Коротко

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

А может ли CTE обратиться к самому себе? Может — и это открывает задачи, которые иначе не решить.