Агрегаты: COUNT, группы и честный знаменатель

Организатор спрашивает, сколько книг сейчас у каждого участника и какая книга популярнее. Эти вопросы похожи, но считают разные факты. Число активных физических экземпляров, число исторических выдач и число уникальных читателей дадут разные значения. Сначала определим единицу счёта и период, иначе правильная SQL-сумма ответит на другой вопрос.

GROUP BY меняет единицу строки

Запрос отдельных выдач возвращает одну строку на выдачу. GROUP BY объединяет строки по выбранным полям, а агрегат вычисляет значение внутри группы. После группировки одна строка результата может означать участника с числом выдач. Неагрегированные выбранные поля должны соответствовать определённым правилам группировки; нельзя добавить случайную дату и надеяться, что база выберет подходящую.

SELECT member_id, COUNT(*) AS total_loans
FROM club.loans
GROUP BY member_id
ORDER BY member_id;

Исходный результат: участник 1 имеет 2 выдачи, участник 2 — 1. Участника 3 нет: он не предоставил ни одной входной строки loans. Это корректный ответ «число выдач среди встречающихся в истории участников», но не полный список клуба. Вера появится, если начать с members и сохранить её через LEFT JOIN.

COUNT строки и COUNT значения

COUNT() считает все строки группы. COUNT(column) считает ненулевые значения колонки. После LEFT JOIN строка Веры присутствует, но l.id=NULL. Поэтому COUNT() даст ей единицу, а COUNT(l.id) — ноль. DISTINCT внутри агрегата меняет единицу: COUNT(DISTINCT member_id) считает разных участников, не число выдач.

SELECT m.id,m.name,COUNT(l.id) AS total_loans
FROM club.members m
LEFT JOIN club.loans l ON l.member_id=m.id
GROUP BY m.id,m.name
ORDER BY m.id;

Ожидается 1,Анна,2; 2,Борис,1; 3,Вера,0. В нашем наборе имена уникальны случайно; группировать только по имени было бы неверно, если в клуб придёт другая Анна. ID определяет участника, имя служит подписью. Условие наличия ключа позволяет отличить настоящую выдачу от NULL-строки соединения.

Для активных выдач добавим условие в ON, сохранив участников без совпадения:

SELECT m.id,COUNT(l.id) AS active_loans
FROM club.members m
LEFT JOIN club.loans l
  ON l.member_id=m.id AND l.returned_on IS NULL
GROUP BY m.id ORDER BY m.id;

Результат 1→2,2→0,3→0. Не переносите условие активности механически в WHERE с другими сравнениями: вы можете убрать нужные нулевые группы. Подготовленный набор из предыдущего урока помогает проверить смысл соединения до агрегата.

WHERE фильтрует вход, HAVING — группы

Нужно найти участников с двумя и более активными выдачами. WHERE отбирает строки до группировки; HAVING задаёт условие вычисленной группы. Агрегат COUNT нельзя без изменения модели использовать как условие одной ещё не сформированной строки.

SELECT member_id,COUNT(*) AS active_loans
FROM club.loans
WHERE returned_on IS NULL
GROUP BY member_id
HAVING COUNT(*) >= 2
ORDER BY member_id;

Получаем только 1→2. Если поменять порог на 3, результат пустой. Пустой результат здесь означает отсутствие подходящей группы, а не ошибку SQL. Для отчёта с общим числом пользователей без фильтра нужен другой запрос. Не следует скрывать разницу дополнительным COALESCE на уровне приложения.

SUM и AVG для набора без ненулевых значений могут вернуть NULL, тогда как COUNT возвращает ноль. Если бизнес-показатель должен трактовать пустой набор как ноль, примените COALESCE к результату осознанно. Ноль наблюдений и среднее значение ноль — разные факты; для среднего часто полезно возвращать ещё размер выборки.

SELECT COUNT(*) AS samples,
       AVG(due_on-borrowed_on) AS average_days
FROM club.loans;

На seed samples=3, средний срок равен 14 дням. Разность двух date возвращает число дней. Это плановая длительность выдачи, а не реальное время чтения и не срок до фактического возврата. Returned_on двух выдач отсутствует, поэтому другой показатель требует отдельного выбора завершённых фактов.

Проверяем популярность и знаменатель

У «Практики Go» две выдачи, но читатель один. У «Запросов SQL» одна выдача и один читатель. Книга «Как работают сети» пока без истории. Если назвать популярность числом выдач, первая лидирует; если числом разных читателей, первые две равны. В отчёте название метрики должно явно сообщать этот выбор.

SELECT b.id,b.title,COUNT(l.id) AS issues,
       COUNT(DISTINCT l.member_id) AS readers
FROM club.books b
LEFT JOIN club.copies c ON c.book_id=b.id
LEFT JOIN club.loans l ON l.copy_id=c.id
GROUP BY b.id,b.title
ORDER BY b.id;

Ожидаемые пары issues/readers: 10→2/1,11→1/1,12→0/0. Сумма выдач по книгам равна трём исходным loans. Это полезная сверка: если JOIN уведомлений умножил факты, сумма перестанет сходиться. Но одна сошедшаяся сумма не доказывает правильность всех распределений; проверяйте также конкретные группы.

Самостоятельная лаборатория

Отведите 30 минут. Постройте отчёт по авторам: количество физических экземпляров и число активных выдач. У Ирины книги 10 и 12, у Олега 11. Сначала выпишите ожидаемые числа вручную. Затем объясните, почему COUNT(c.id) после присоединения всех исторических loans в более полном dataset способен завысить число экземпляров.

Критерии: нулевые группы сохранены; различаются книги и экземпляры; знаменатель подписан; ID не заменён произвольным именем; дополнительный JOIN не увеличивает основной факт незаметно. Если считаются два независимых множества, агрегируйте их отдельно и соедините готовые группы либо используйте обоснованный COUNT(DISTINCT нужный ключ).

Разбор

Ирина имеет три экземпляра и две активные выдачи; Олег — один и ноль. В seed каждый экземпляр встречается в истории не более одного раза, но это не ограничение всей модели. При десяти прежних выдачах одной копии COUNT(c.id) насчитает её десять раз. Пример учит проверять будущую кардинальность, а не только приятные числа текущей таблицы.

Первичные источники

PostgreSQL: Aggregate Functions, GROUP BY и HAVING, Tutorial: Aggregates.