AVG и ROUND в SQL: как посчитать среднее и не соврать себе

Запрос вида select round(avg(rating), 2) from reviews where device = 'tv' выглядит безобидно и почти всегда пишется правильно с первого раза. Проблема не в синтаксисе — в том, что среднее умеет врать тихо, и заметить это по результату нельзя.

AVG пропускает пустые значения

Главная ловушка одна: AVG не считает строки, где значение пустое (NULL). Он не подставляет ноль — он их просто не видит, и в знаменателе оказывается меньше строк, чем вы думали.

Разница получается заметной. Если из ста отзывов оценку поставили сорок, AVG(rating) вернёт среднее по этим сорока. Формально верно, а по смыслу — «средняя оценка тех, кто вообще оценивал», и это совсем другое утверждение.

Поэтому среднее полезно смотреть рядом с двумя числами: сколько строк всего и по скольким считали.

SELECT COUNT(*)      AS всего,
       COUNT(rating) AS с_оценкой,
       ROUND(AVG(rating), 2) AS среднее
FROM reviews
WHERE device = 'tv';

COUNT(*) считает все строки, COUNT(rating) — только заполненные. Разошлись — значит среднее посчитано не по всем, и это надо знать до того, как показывать цифру кому-то ещё.

ROUND: второй аргумент — знаки после запятой

ROUND(x, 2) оставляет два знака, ROUND(x) округляет до целого. Округлять стоит в самом конце: если сначала округлить каждое значение, а потом усреднить, ошибка накопится.

И округление — это оформление, а не расчёт. Внутри отчёта храните полное число, а ROUND ставьте на последнем шаге, там, где результат попадает человеку на глаза.

GROUP BY: среднее по каждой группе

Запрос выше даёт одно число на весь телевизор. Чтобы получить среднее по каждому виду устройств сразу, нужен GROUP BY:

SELECT device,
       COUNT(*) AS отзывов,
       ROUND(AVG(rating), 2) AS среднее
FROM reviews
GROUP BY device
ORDER BY среднее DESC;

Правило простое: всё, что стоит в SELECT и не завёрнуто в COUNT, AVG или другую считающую функцию, обязано стоять и в GROUP BY. Иначе база либо откажется выполнять запрос, либо — что хуже — выдаст произвольную строку из группы.

WHERE или HAVING

Эти два слова путают чаще всего, а разница у них честная и запоминается с одного раза.

• WHERE отсеивает строки до группировки: «берём только телевизоры».
• HAVING отсеивает группы после подсчёта: «оставляем те, где отзывов больше десяти».

Написать WHERE COUNT(*) > 10 нельзя: на этом шаге считать ещё нечего, группы не собраны. Нужно HAVING COUNT(*) > 10.

Ещё одна тихая ловушка: среднее по среднему

Средние нельзя усреднять. Если у одного устройства 3 отзыва со средним 5, а у другого 300 со средним 4, то среднее по этим двум числам — 4,5, а настоящее среднее по всем отзывам — 4,01. Считать нужно всегда от исходных строк, а не от готовых результатов.

Где потренироваться

Всё это проверяется руками за один вечер. В тренажёре SQL запросы выполняются на учебной базе и сверяются с эталоном — ошибку видно сразу, а не через полчаса. Первые уроки курса «SQL» открыты без регистрации: отбор строк через WHERE и сортировка и ограничение.

Если хочется сразу задач «как в жизни» — есть расследования: там базу приходится расспрашивать, чтобы понять, что произошло. А типовые вопросы работодателей собраны в статье вопросы по SQL на собеседовании.