CTE и этапы запроса
В предыдущем уроке оконная функция присвоила курсам места внутри темы. Теперь нужно выбрать первый курс каждой темы и одновременно получить понятный отчёт о заведённых главах. Такие задачи удобнее описывать этапами: сначала сформировать промежуточный набор, затем использовать его результат на следующем уровне. CTE даёт имя этому набору внутри одного SQL-запроса.
Рассмотрим PostgreSQL 17.11 и снимок catalog-db/lesson-13 из архива серии. Он содержит только чтение исходных таблиц. SQL не запускался; результаты ниже ожидаются по договору каталога. CTE не создаёт новую постоянную таблицу и не заменяет файл сохранённого отчёта.
Имя этапа
Конструкция WITH располагается перед основным запросом. Внутри неё записывается запрос с именем, на которое затем можно ссылаться как на источник строк. Начнём с ранжирования опубликованных курсов:
WITH ranked AS (
SELECT id, topic_id, slug, planned_lessons,
row_number() OVER (
PARTITION BY topic_id ORDER BY planned_lessons DESC, id
) AS position
FROM catalog.courses
WHERE status = 'published'
)
SELECT topic_id, slug, planned_lessons
FROM ranked
WHERE position = 1
ORDER BY topic_id;Ожидаются JavaScript для фронтенда, Markdown для публикации и PostgreSQL для бэкенда. Дизайн отсутствует, поскольку там нет опубликованного курса. Имя ranked обозначает набор, в котором уже вычислено место. Поэтому внешнее условие может отбирать по position; оно не пытается обратиться к оконной функции раньше её появления.
Два уровня отражают зависимость операций. Нельзя просто перенести position = 1 в WHERE внутреннего запроса: оконный результат ещё не является входным полем этого уровня. CTE делает границу заметной для человека. Та же логика может быть выражена вложенным запросом, поэтому само слово WITH не создаёт новую предметную возможность, а помогает организовать её запись.
Если выбрать первый курс по времени публикации вместо количества глав, нужно изменить критерий окна. Нельзя оставлять прежнее имя отчёта «самый большой курс», когда SQL уже выбирает «самый свежий». Название этапа полезно только тогда, когда его вычисление соответствует смыслу. Псевдоним position также локален результату и не меняет порядок уроков в таблице lessons.
Агрегируем дочерний набор заранее
В групповой главе мы обнаружили, что соединение каждого курса с двумя уроками удваивает план, если сложить родительское значение после соединения. Решим это, получив сначала одну строку показателей на курс:
WITH lesson_totals AS (
SELECT course_id,
count(*) AS recorded,
count(body) AS with_text,
sum(reading_minutes) AS reading_minutes
FROM catalog.lessons
GROUP BY course_id
)
SELECT c.id, c.slug, c.planned_lessons,
coalesce(lt.recorded, 0) AS recorded,
coalesce(lt.with_text, 0) AS with_text,
coalesce(lt.reading_minutes, 0) AS reading_minutes
FROM catalog.courses AS c
LEFT JOIN lesson_totals AS lt ON lt.course_id = c.id
ORDER BY c.id;Ожидаются шесть строк, по одной на курс. У каждого recorded = 2. У производительности with_text = 1, у остальных два. Минуты по порядку курсов равны двадцати, двадцати пяти, двадцати, двадцати двум, двадцати шести и четырнадцати. Это суммы учебного набора, не измеренная скорость чтения аудитории.
count(body) считает заполненные значения. Он не оценивает качество текста и не отличает хороший урок от строки «Учебный текст». В данном наборе отличие нужно только для отсутствующей заготовки. Подпись «с текстом» точнее, чем «готово к публикации», поскольку завершённость требует более широкого редакционного договора.
После группировки lesson_totals имеет уникальный уровень course_id: одна строка на родителя. Левое соединение сохраняет курс, даже если у него позже не будет ни одной учебной строки. Заменяющие нули показывают выбранное представление пустого дочернего набора. План курса теперь представлен в ответе один раз, поэтому его последующая сумма не умножится числом глав.
Несколько зависимых этапов
Можно дать имя результату этого соединения и сгруппировать его по теме. Тогда каждый этап отвечает за один уровень данных: уроки превращаются в показатели курса, курсы превращаются в показатели темы. Такой порядок удобен для чтения, потому что ошибка кратности обнаруживается на границе конкретного набора, а не прячется внутри длинного выражения.
WITH lesson_totals AS (
SELECT course_id, sum(reading_minutes) AS minutes
FROM catalog.lessons
GROUP BY course_id
), course_totals AS (
SELECT c.id, c.topic_id, c.planned_lessons,
coalesce(lt.minutes, 0) AS minutes
FROM catalog.courses AS c
LEFT JOIN lesson_totals AS lt ON lt.course_id = c.id
)
SELECT topic_id, sum(planned_lessons) AS planned,
sum(minutes) AS minutes
FROM course_totals
GROUP BY topic_id
ORDER BY topic_id;Ожидаемые планы остаются сорок восемь, двадцать две и шестнадцать глав. Минуты соответствуют шестидесяти семи, тридцати четырём и двадцати шести. Сумма минут по всему набору равна ста двадцати семи. Если бы мы хотели включить пустой дизайн, последнему уровню понадобилась бы таблица тем с левым соединением, как в предыдущем групповом уроке.
Место фильтра снова важно. Отбор опубликованных курсов в course_totals изменит план фронтенда на тридцать шесть и минуты на сорок пять, поскольку черновая производительность будет исключена целиком. Фильтрация только заполненных body в первом этапе дала бы другой показатель минут. Оба варианта могут быть разумны, но отвечают разным вопросам.
CTE не обещает ускорение
Нельзя считать каждое имя WITH обязательной временной таблицей, физически созданной перед дальнейшим чтением. PostgreSQL может объединять допустимый CTE с основным запросом либо материализовать его в зависимости от формы и использования. Детали описаны в руководстве WITH для PostgreSQL 17. Здесь CTE используется прежде всего для выражения смысловых этапов.
Слова MATERIALIZED и NOT MATERIALIZED позволяют влиять на отдельные решения в поддерживаемых случаях, но не являются универсальным ускорителем. Прежде чем добавлять их в отчёт, нужно понимать повторное использование набора и читать план. В этой главе нет замеров, поэтому мы не приписываем CTE выигрыш времени только из-за более красивого текста.
CTE существует в пределах одного SQL-выражения. Следующий отдельный запрос не сможет обратиться к lesson_totals только потому, что это имя было в предыдущей команде. Если нужен постоянный объект для нескольких запросов, рассматривают другой механизм, например представление. Договор времени жизни важен для клиента, который отправляет команды по одной.
Промежуточное имя и повторное использование
При чтении длинного запроса полезно записать рядом с каждым CTE его ключ результата. Для lesson_totals это course_id, для course_totals — id курса, для окончательного отчёта — topic_id. Такая запись описывает уникальность уровня, а не требует физического индекса на временном имени. Она помогает увидеть, где следующее соединение может законно увеличить число строк.
Если добавить к course_totals ещё один многозначный набор, например независимые метки курса, прежняя сумма снова может умножиться. CTE не защищает запрос от дальнейшего нарушения кратности. Он лишь делает этап явным, поэтому исправление проще локализовать: метки надо агрегировать, проверять через существование либо выводить на другом уровне.
Также однажды правильно сформированный отчёт может перестать соответствовать новому предметному договору. Например, если курс должен иметь несколько основных тем, нынешнее одиночное topic_id уже не описывает связь. Не пытайтесь сохранить старые числа за счёт случайной первой строки связи. Сначала меняются модель и единица отчёта, затем SQL-этапы подстраиваются под неё.
Имена этапов выбираются для читателя. lesson_totals сообщает, почему появилась группировка, а безличные q1, q2, q3 заставляют постоянно возвращаться к определениям. Но слишком уверенное имя вроде finished_lessons было бы неверным для COUNT(body): наличие текста не доказывает завершённость. Хорошее имя сохраняет точный смысл вычисления.
При будущем ручном изучении прочитайте каждую промежуточную SELECT-часть как самостоятельный результат и назовите, что означает одна её строка. Урок, курс и тема не должны незаметно поменяться местами. Если уровень сохраняется, итоговые числа легко объяснить; теперь сложный отчёт растёт последовательными преобразованиями, а не исправлениями повторов через случайный DISTINCT.