Агрегации, GROUP BY и HAVING
Проверяем знаменатель, считаем оценённые задачи и отделяем фильтр строк от фильтра групп.
- Отличать COUNT(*) от COUNT(column).
- Определять знаменатель среднего и поведение неизвестных значений.
- Проверять GROUP BY и HAVING по ручному расчёту.
Одна цифра скрывает несколько вопросов
Показатель «средняя оценка задачи» кажется очевидным, пока в данных не появятся неизвестные значения. У шести задач нашего снимка оценки равны 3, NULL, 5, 0, 2, NULL. Известных оценок четыре, сумма равна десяти, среднее среди известных — 2,5 часа. Нулевая оценка участвует в знаменателе; неизвестная не превращается в ноль сама собой. Если продукт обещает среднее по всем задачам, правило обработки отсутствия нужно обсудить отдельно. SQL может корректно вычислить неверно сформулированную метрику.
COUNT(*) считает строки. COUNT(estimate) считает строки с ненулевым в смысле NULL значением estimate: значение 0 при этом считается. Название колонки «estimated» помогает показать смысл вместо безымянного результата. Числа стоит сверять в связке: total=6, estimated=4, hours=10, average=2.5. Проверка только average может пропустить две компенсирующие ошибки. Например, неверный фильтр одновременно удалит задачу и её часы, сохранив похожее отношение.
Q3 · Проверяем числитель и знаменатель
SELECT COUNT(*) AS total,
COUNT(estimate) AS estimated,
SUM(estimate) AS hours,
AVG(estimate) AS average
FROM tasks;
-- 6 | 4 | 10 | 2.5Строки до групп, группы после
Для общего количества задач по авторам ручной расчёт даёт u1=3, u2=2, u3=1. Пользователь u4 отсутствует в tasks, поэтому один GROUP BY этой таблицы не создаст для него строку с нулём. HAVING COUNT(*) >= 2 оставит u1 и u2. Если перед группировкой добавить WHERE state='published', входной набор сократится до t1, t3 и t5; по авторам получится u1=2 и u2=1, а тот же HAVING оставит только u1. Это два разных бизнес-вопроса, хотя меняется всего одна строка запроса.
Начните с явного набора идентификаторов, который входит в агрегацию. Затем распределите их по группам и посчитайте каждую группу. Этот промежуточный лист полезнее попытки догадаться по готовому результату. Не помещайте в SELECT случайное поле, не определённое группой: например, title рядом с COUNT(*) для автора с несколькими задачами не задаёт, какой заголовок нужен отчёту. Если нужна последняя задача, потребуется отдельное однозначное правило порядка.
Q5 и Q12 · Разные вопросы
-- Все задачи авторов, у которых не менее двух задач:
SELECT author_id, COUNT(*) AS total
FROM tasks
GROUP BY author_id HAVING COUNT(*) >= 2
ORDER BY author_id;
-- u1 | 3
-- u2 | 2
-- Только опубликованные, затем авторы с двумя публикациями:
SELECT author_id, COUNT(*) AS published
FROM tasks WHERE state='published'
GROUP BY author_id HAVING COUNT(*) >= 2
ORDER BY author_id;
-- u1 | 2Как проверять пустой набор
Отдельный риск — отчёт без подходящих задач. Для него сравнивайте семантику COUNT, SUM и среднего, а не ожидайте одинаковый ноль во всех колонках. Не маскируйте неизвестное значение ради красивой карточки: изменение представления требует продуктового решения. Укажите в тесте, каким снимком и фильтром получен пустой набор. В этом курсе такой пример можно создать условием state='missing', которое не меняет fixture.
Для каждой группы добавьте проверку принадлежности: сумма количеств по состояниям должна равняться шести, а список опубликованных идентификаторов должен совпасть с t1,t3,t5. Такая сверка ловит пропущенную группу и случайно включённый черновик, даже если итоговое среднее выглядит правдоподобно.
В итоговом отчёте назовите метрику буквально: «средняя оценка среди задач с известной оценкой», а не просто «среднее». Зафиксируйте точность отображения отдельно от вычисления: 2.5 в SQL и 2,5 ч в локализованном интерфейсе могут означать одно число. Пока интерфейс не запускался, утверждайте только результат SQL. Ошибка округления, часовой пояс и пагинация здесь не исследуются; их нельзя объявить проверенными из одного агрегата.
Источники и проверка фактов
SQLite · Aggregate functionsCOUNT, SUM и AVG: смысл отсутствующих значений.SQLite · SELECT processingПорядок WHERE, группировки и HAVING.Авторский учебный материал · Редакция QA Orbit · версия 2026-10-28.1