Агрегатные функции SQL — полный гайд с примерами
Коротко
Агрегатные функции сворачивают набор строк в одно значение: считают количество, сумму, среднее, минимум или максимум. Без них невозможно посчитать ни одну метрику — DAU, выручку, конверсию, средний чек. На собеседованиях аналитика данных агрегатные функции встречаются в каждой второй задаче по SQL.
Пять основных функций
COUNT — количество
COUNT имеет три формы, и различие между ними спрашивают на собеседованиях почти гарантированно.
COUNT(*) — считает все строки, включая строки с NULL.
SELECT COUNT(*) AS total_orders
FROM ordersCOUNT(column) — считает строки, где column IS NOT NULL.
SELECT COUNT(email) AS users_with_email
FROM usersCOUNT(DISTINCT column) — считает уникальные не-NULL значения.
SELECT COUNT(DISTINCT user_id) AS unique_buyers
FROM ordersЕсли в таблице 1000 строк, из них у 50 email = NULL и 200 email-ов повторяются, то COUNT(*) = 1000, COUNT(email) = 950, COUNT(DISTINCT email) = 750. Подробный разбор — в статье COUNT(*) vs COUNT(column).
SUM — сумма
SELECT SUM(amount) AS total_revenue
FROM orders
WHERE order_date >= '2025-01-01'SUM игнорирует NULL. Если в столбце amount есть NULL-значения, они не повлияют на результат — не будут прибавлять ноль и не сломают вычисление. Но если все значения NULL, SUM вернёт NULL, а не 0.
AVG — среднее арифметическое
SELECT AVG(salary) AS avg_salary
FROM employees
WHERE department = 'analytics'AVG тоже игнорирует NULL — и в числителе, и в знаменателе. Если из 10 строк у 3 salary = NULL, AVG посчитает среднее по 7 оставшимся. Это принципиально: AVG(salary) и SUM(salary) / COUNT(*) дадут разные результаты при наличии NULL.
MIN и MAX — минимум и максимум
MIN и MAX работают не только с числами, но и с датами и строками.
SELECT
user_id,
MIN(order_date) AS first_order,
MAX(order_date) AS last_order
FROM orders
GROUP BY user_idДля строк MIN возвращает первое значение в алфавитном порядке, MAX — последнее. Для дат MIN — самая ранняя дата, MAX — самая поздняя. NULL обе функции игнорируют.
GROUP BY: агрегация по группам
Агрегатная функция без GROUP BY сворачивает всю таблицу в одну строку. С GROUP BY — сворачивает каждую группу отдельно.
SELECT
category,
COUNT(*) AS order_count,
SUM(amount) AS revenue,
ROUND(AVG(amount), 2) AS avg_check
FROM orders
GROUP BY category
ORDER BY revenue DESCВ SELECT при наличии GROUP BY можно указывать только столбцы из GROUP BY и агрегатные функции. SELECT category, product_name, COUNT(*) FROM orders GROUP BY category — ошибка, потому что product_name не входит в GROUP BY.
Группировать можно по нескольким столбцам — каждая уникальная комбинация станет отдельной группой:
SELECT
DATE_TRUNC('month', order_date) AS month,
city,
COUNT(DISTINCT user_id) AS buyers
FROM orders
GROUP BY DATE_TRUNC('month', order_date), cityПодробнее о разнице между GROUP BY и оконными функциями — в статье GROUP BY vs PARTITION BY.
HAVING: фильтрация после агрегации
WHERE фильтрует строки до группировки и не может содержать агрегатные функции. HAVING фильтрует группы после группировки — здесь агрегатные функции допустимы.
SELECT
user_id,
COUNT(*) AS order_count,
SUM(amount) AS total_spent
FROM orders
WHERE order_date >= '2025-01-01'
GROUP BY user_id
HAVING COUNT(*) >= 5 AND SUM(amount) > 10000
ORDER BY total_spent DESCСначала WHERE оставляет заказы 2025 года. Затем GROUP BY группирует по пользователям. HAVING оставляет только тех, у кого 5+ заказов и суммарная трата больше 10 000. Детальное сравнение — в статье WHERE vs HAVING.
NULL и агрегатные функции
Ключевое правило: все агрегатные функции игнорируют NULL, кроме COUNT(*).
| Функция | NULL-поведение |
|---|---|
| COUNT(*) | Считает все строки, включая NULL |
| COUNT(column) | Пропускает строки, где column IS NULL |
| SUM(column) | Игнорирует NULL; если все значения NULL — возвращает NULL |
| AVG(column) | Игнорирует NULL и в числителе, и в знаменателе |
| MIN(column) | Игнорирует NULL |
| MAX(column) | Игнорирует NULL |
Это значит, что AVG(column) != SUM(column) / COUNT(*), если в column есть NULL. На собеседованиях это проверяют.
CASE внутри агрегатных функций
Условная агрегация — мощный приём. С помощью CASE внутри COUNT или SUM можно считать метрики по условию в одном запросе.
Конверсия в покупку:
SELECT
DATE_TRUNC('day', visit_date) AS day,
COUNT(DISTINCT user_id) AS visitors,
COUNT(DISTINCT CASE WHEN has_purchase THEN user_id END) AS buyers,
ROUND(
COUNT(DISTINCT CASE WHEN has_purchase THEN user_id END)::numeric
/ NULLIF(COUNT(DISTINCT user_id), 0) * 100, 2
) AS conversion_pct
FROM user_visits
GROUP BY DATE_TRUNC('day', visit_date)Выручка по сегментам в одном запросе:
SELECT
SUM(CASE WHEN segment = 'new' THEN amount ELSE 0 END) AS new_revenue,
SUM(CASE WHEN segment = 'returning' THEN amount ELSE 0 END) AS returning_revenue,
SUM(amount) AS total_revenue
FROM ordersCOUNT(CASE WHEN ... THEN 1 END) считает строки, удовлетворяющие условию. SUM(CASE WHEN ... THEN amount ELSE 0 END) суммирует значения по условию. Этот паттерн встречается на собеседованиях в задачах на расчёт метрик.
Практические примеры
DAU — ежедневная активная аудитория
SELECT
DATE_TRUNC('day', event_time) AS day,
COUNT(DISTINCT user_id) AS dau
FROM events
GROUP BY DATE_TRUNC('day', event_time)
ORDER BY dayОбязательно COUNT(DISTINCT) — без него вы посчитаете количество событий, а не уникальных пользователей.
Выручка по сегментам за месяц
SELECT
u.segment,
COUNT(DISTINCT o.user_id) AS buyers,
SUM(o.amount) AS revenue,
ROUND(AVG(o.amount), 2) AS avg_check
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.order_date >= DATE_TRUNC('month', CURRENT_DATE)
GROUP BY u.segment
ORDER BY revenue DESCТипичные ошибки
Столбец не в GROUP BY и не в агрегате. SELECT user_id, name, COUNT(*) FROM orders GROUP BY user_id — ошибка. Столбец name нужно либо добавить в GROUP BY, либо обернуть в агрегат (MAX(name)).
Агрегат в WHERE вместо HAVING. WHERE COUNT(*) > 5 — ошибка. Группировка ещё не произошла. Нужно HAVING COUNT(*) > 5.
COUNT(*) вместо COUNT(DISTINCT). Если в таблице дубликаты (например, после JOIN), COUNT(*) завысит результат. Для уникальных значений используйте COUNT(DISTINCT column).
AVG при наличии NULL. Если в столбце есть пропуски, AVG посчитает среднее только по заполненным строкам. Если нужно учитывать пропуски как нули — используйте AVG(COALESCE(column, 0)).
SUM возвращает NULL. Если все строки в группе имеют NULL, SUM вернёт NULL, а не 0. Оберните в COALESCE: COALESCE(SUM(column), 0).
Вопросы с собеседований
— Чем отличается COUNT(*) от COUNT(column) от COUNT(DISTINCT column)? — COUNT(*) считает все строки. COUNT(column) — строки, где column не NULL. COUNT(DISTINCT column) — уникальные не-NULL значения.
— Как AVG обрабатывает NULL? — AVG игнорирует NULL и в числителе, и в знаменателе. Среднее считается только по не-NULL значениям. Поэтому AVG(salary) и SUM(salary) / COUNT(*) могут дать разные результаты.
— Почему нельзя написать WHERE COUNT(*) > 5? — Потому что WHERE выполняется до GROUP BY. На этапе WHERE строки ещё не сгруппированы, и агрегатная функция недопустима. Для фильтрации по агрегату используется HAVING.
— Напишите запрос: DAU за каждый день последнего месяца.
— SELECT DATE_TRUNC('day', event_time) AS day, COUNT(DISTINCT user_id) AS dau FROM events WHERE event_time >= CURRENT_DATE - INTERVAL '30 days' GROUP BY DATE_TRUNC('day', event_time) ORDER BY day. Ключевой момент — COUNT(DISTINCT user_id), а не COUNT(*).
— Как посчитать конверсию из визита в покупку одним запросом?
— Используйте CASE внутри COUNT: COUNT(DISTINCT CASE WHEN has_purchase THEN user_id END)::numeric / NULLIF(COUNT(DISTINCT user_id), 0). Это условная агрегация — один проход по таблице.
Потренируйтесь решать задачи — откройте тренажёр с 1500+ вопросами для подготовки к собеседованиям аналитиков.
FAQ
Чем агрегатные функции отличаются от оконных?
Агрегатные функции сворачивают группу строк в одну строку. Оконные функции вычисляют значение для каждой строки, не сворачивая результат. SUM(amount) с GROUP BY даст одну строку на группу. SUM(amount) OVER (PARTITION BY ...) оставит все строки и добавит накопленную сумму в каждую.
Что вернёт SUM, если все значения NULL?
NULL, а не 0. Это частый источник ошибок в отчётах. Если нужен 0 — оберните в COALESCE: COALESCE(SUM(column), 0).
Можно ли использовать несколько агрегатных функций в одном SELECT?
Да. Вы можете комбинировать COUNT, SUM, AVG, MIN, MAX в одном запросе — все они вычисляются по одним и тем же группам. SELECT category, COUNT(*), SUM(amount), AVG(amount), MIN(amount), MAX(amount) FROM orders GROUP BY category — абсолютно корректный запрос.
Как тренироваться
Агрегатные функции — фундамент SQL для аналитика. Их нужно написать руками десятки раз, чтобы довести до автоматизма. В тренажёре Карьерник есть вопросы на COUNT, SUM, AVG, GROUP BY, HAVING и условную агрегацию — с разборами. Больше вопросов по всем темам — в разделе с примерами.