Что возвращают агрегатные и неагрегатные функции в SQL запросе

Что проверяют

Понимаете ли вы, как функция меняет форму результата: сколько строк придёт на выходе и что станет с NULL. Для того, кто строит витрины и признаки для моделей, это основа. Следом обычно просят объяснить, почему нельзя выбрать колонку рядом с SUM без GROUP BY, и чем агрегат отличается от той же функции с OVER.

Как отвечать

• скалярные (неагрегатные): UPPER, COALESCE, DATE_TRUNC, ROUND, CASE. Одна строка на входе, одно значение на выходе, число строк не меняется;
• агрегатные: COUNT, SUM, AVG, MIN, MAX, STRING_AGG, percentile_cont. Сворачивают группу строк в одно значение. Без GROUP BY весь результат становится одной строкой, с GROUP BY получается строка на каждую группу;
• оконные: тот же SUM, но с OVER (PARTITION BY ...), считает по группе и оставляет каждую строку. Это аналог groupby().transform() в pandas, а обычный агрегат ближе к groupby().agg().

Отдельно проговорите NULL, на нём и ловят:

• агрегаты, кроме COUNT(*), пропускают NULL. AVG(score) делит на число непустых значений, а не на число строк;
• на пустом наборе COUNT вернёт 0, а SUM и AVG вернут NULL, поэтому в отчётах пишут COALESCE(SUM(x), 0);
• скалярная функция от NULL почти всегда даёт NULL: NULL + 1 и UPPER(NULL) одинаково пусты.

И порядок выполнения: WHERE фильтрует строки до группировки, агрегатов в нём быть не может, а HAVING фильтрует уже готовые группы.

Где спотыкаются

Пишут SELECT city, SUM(amount) FROM orders без GROUP BY city. PostgreSQL, ClickHouse и Spark SQL вернут ошибку, а MySQL со снятым ONLY_FULL_GROUP_BY молча подставит город из произвольной строки, и отчёт окажется тихо неверным.

Путают COUNT(*), COUNT(col) и COUNT(DISTINCT col). Разница между ними как раз в пропусках и дублях, а в метриках вроде числа активных пользователей это три разные цифры.

Считают среднее от средних. AVG по дневным AVG не равен среднему по всем событиям, если в днях разное число записей. Правильно делить общую сумму на общее количество.