Агрегация и группы
Страница каталога показывает отдельные курсы, а редакторский отчёт может потребовать количество курсов и запланированных глав по темам. Для этого строки объединяются в группы, и над каждой группой вычисляется показатель. Основная трудность заключается не в названии функции SUM, а в том, что именно представляет входная строка и какие объекты нужно посчитать.
Работаем с PostgreSQL 17.11 и снимком catalog-db/lesson-11 из архива серии. Он читает исходные четыре темы и шесть курсов, не продолжая временные вставки предыдущих глав. SQL не выполнялся; ожидаемые суммы рассчитаны по зафиксированному учебному набору. Цены условные, отчёт не описывает доход ProfessorWeb.
Группа на уровне курса
Для начала сгруппируем непосредственно таблицу курсов:
SELECT topic_id, count(*) AS courses,
sum(planned_lessons) AS planned
FROM catalog.courses
GROUP BY topic_id
ORDER BY topic_id;Ожидаются три группы. Фронтенд содержит три курса и сорок восемь запланированных глав. Публикация содержит два курса и двадцать две главы. Бэкенд содержит один курс и шестнадцать глав. Дизайн не появляется, поскольку в таблице курсов нет строки с этой темой. Группировка не создаёт отсутствующие предметные объекты самостоятельно.
count(*) здесь считает строки курсов. sum(planned_lessons) складывает их планы. Число записанных строк lessons не участвует в запросе, поэтому показатель нельзя назвать «готовые уроки». У каждого курса в снимке две учебные строки, но планируемый объём больше. Подпись отчёта должна соответствовать выражению, иначе корректный SQL даст читателю неверное впечатление.
Поля результата также выбираются по уровню группы. Один topic_id относится ко всей группе, но у неё несколько названий курса. Если добавить произвольный c.title без группировки или агрегирования, нельзя получить единственное осмысленное название темы таким способом. Нужно присоединить таблицу тем по ключу и выбрать её собственное название.
Сохраняем пустой раздел
Для отчёта по всем темам используем левое соединение из предыдущего урока:
SELECT t.id, t.slug,
count(c.id) AS courses,
coalesce(sum(c.planned_lessons), 0) AS planned
FROM catalog.topics AS t
LEFT JOIN catalog.courses AS c ON c.topic_id = t.id
GROUP BY t.id, t.slug
ORDER BY t.id;Теперь ожидаются четыре строки. У дизайна courses = 0 и planned = 0. Обратите внимание на выбор count(c.id), а не count(*). Левое соединение сохраняет одну строку темы с отсутствующими правыми значениями. Звёздочка посчитала бы эту строку и дала единицу, хотя курс не найден. Подсчёт правого обязательного ключа учитывает только реально присоединённые курсы.
Сумма без значений даёт NULL, поэтому для представления нулевого плана используется COALESCE. Поведение агрегатов описано в документации PostgreSQL. Замена относится к результату отчёта: она не создаёт курс и не меняет исходную колонку. Ноль здесь означает выбранное представление отсутствующей суммы для пустой темы.
Если предметная задача требовала отличать «ничего не запланировано» от «данные не получены», безусловная замена могла бы скрыть важное состояние. В нашем фиксированном наборе соединение достоверно представляет пустую тему, поэтому ноль оправдан. Одно и то же выражение не нужно переносить во все отчёты без чтения смысла данных.
Несколько показателей над одной группой
Редактор хочет видеть общее число курсов и отдельно опубликованное. Можно вычислить два агрегата над одной группой, применив FILTER только к второму:
SELECT t.slug,
count(c.id) AS total,
count(c.id) FILTER (WHERE c.status = 'published') AS published
FROM catalog.topics AS t
LEFT JOIN catalog.courses AS c ON c.topic_id = t.id
GROUP BY t.id, t.slug
ORDER BY t.id;Ожидаются пары: фронтенд три и два, публикация два и два, бэкенд один и один, дизайн ноль и ноль. Условие фильтра относится к конкретному вычислению, поэтому не удаляет черновик из входа другого агрегата. Если вместо этого поставить статус в общий WHERE, «общее число» тоже станет числом опубликованных курсов, а пустая тема исчезнет.
Это удобный случай, где PostgreSQL отличается от исторического SQL Server 2012. В старой статье о FILTER в T-SQL конструкция описывалась как неподдерживаемая в рассматриваемом инструменте. Здесь она используется в PostgreSQL 17 по его реальным правилам. Историческое ограничение другого диалекта не нужно повторять как современный универсальный факт.
Отбор групп задаётся через HAVING. Например, к первому отчёту можно добавить HAVING count(*) >= 2, чтобы оставить темы как минимум с двумя курсами. Отбор отдельных курсов перед группировкой выполнялся бы через WHERE. Слова похожи по назначению, но применяются к данным разного уровня: строка курса и уже рассчитанная группа.
Умноженная сумма
Вернёмся к соединению курсов с уроками. У каждого курса в снимке две главы. Если соединить эти таблицы, а затем сложить c.planned_lessons, план каждого курса войдёт дважды. Ожидаемая общая сумма восемьдесят шесть превратится в сто семьдесят два. Агрегат не знает, что повторённое значение относится к одному родителю: он складывает входные строки.
Можно ли исправить это через sum(DISTINCT c.planned_lessons)? Нет. Несколько разных курсов законно имеют план двенадцать или шестнадцать. DISTINCT удаляет одинаковые значения, а не повторённые экземпляры определённого объекта. Получится другая ошибка, которая на одном наборе может казаться удачным совпадением. Уровень данных нужно сохранить до вычисления.
Для одновременного отчёта о плане и фактических главах сначала получают одну агрегированную строку уроков на курс, затем соединяют её с курсом. Такое разбиение будет в уроке CTE. Сейчас полезно уметь обнаружить проблему: спросите, сколько раз представлен один курс на входе функции и соответствует ли это измеряемому показателю.
Аналогично среднее цены после соединения с главами станет взвешенным числом, если разные курсы имеют разное количество уроков. В исходном наборе у всех по две строки, и совпадение результата могло бы спрятать ошибку. Поэтому маленькая таблица ожидаемого ответа не заменяет объяснение уровня группировки.
Общая сумма и среднее группы
Если редактор попросит средний план курса по всей библиотеке, нельзя бездумно усреднить суммы тем. Фронтенд содержит три курса, публикация два, бэкенд один. Каждая тема имеет разный вес в показателе «один курс». Для общего среднего требуется сумма планов, делённая на число курсов, либо AVG на исходном уровне курса.
Похожая ошибка возникает при усреднении средних отдельных тем без их количества. Такой расчёт придаст пустому или маленькому разделу произвольно равный вес большому. Он может быть осмыслен как «средняя тема», но тогда подпись показателя должна назвать именно этот объект сравнения. SQL не исправляет различие статистического смысла автоматически.
В нашей исходной базе общий план равен восьмидесяти шести при шести курсах. У опубликованных общий план семьдесят четыре при пяти курсах. Эти пары позволяют объяснить знаменатель независимо от форматирования дроби клиентом. Сначала фиксируется набор и единица измерения, затем выбирается округление для отчёта.
Если в будущем колонка допускает неизвестное значение, COUNT всех объектов и число значений, участвующих в AVG, могут различаться. Сейчас план обязателен и положителен, поэтому это осложнение отсутствует. Обязательность в схеме помогает отчёту, но не отменяет проверки кратности после соединений с дочерними данными.
При будущем ручном изучении измените только условие публичного статуса и предскажите новые числа. Опубликованных планируемых глав всего семьдесят четыре: черновые двенадцать исключаются. Если групповой отчёт сохранил цифру восемьдесят шесть, выясните, какой агрегат действительно получил фильтр. Такой разбор превращает GROUP BY из короткой команды в способ выразить точный смысл редакторского показателя.