Агрегатные функции SQL: шпаргалка для собеседования

Зачем аналитику агрегатные функции

Агрегатные функции схлопывают множество строк в одно значение. Это фундамент любой аналитики: сумма по дню, среднее по клиенту, количество уникальных пользователей. На собеседовании их знают все — но ловушек там столько, что даже middle-аналитики иногда спотыкаются.

Если готовитесь к собеседованию по SQL, это базовая тема, с которой начинается любой технический раунд.

Базовые агрегаты

Функция	Что делает	Игнорирует NULL?
COUNT(*)	Считает все строки	Нет
COUNT(col)	Считает не-NULL значения	Да
COUNT(DISTINCT col)	Считает уникальные не-NULL	Да
SUM(col)	Сумма	Да
AVG(col)	Среднее	Да
MIN(col)	Минимум	Да
MAX(col)	Максимум	Да

COUNT: самая частая ловушка

Три варианта COUNT дают разные результаты:

SELECT
    COUNT(*) AS all_rows,              -- все строки
    COUNT(email) AS rows_with_email,   -- строки, где email не NULL
    COUNT(DISTINCT email) AS unique    -- уникальные не-NULL email
FROM users;

Классический вопрос на собеседовании: «У нас 1000 строк в таблице, у 800 есть email, уникальных email — 750. Что вернёт каждая версия COUNT?» Ответы: 1000, 800, 750.

Тонкость: COUNT(*) в PostgreSQL обычно быстрее COUNT(1) (раньше разницы не было, сейчас * оптимизируется лучше). На собеседовании этот миф часто проверяют.

SUM и AVG с NULL

NULL игнорируется — это не 0:

-- Есть строки: 10, 20, NULL, 30
SELECT SUM(amount), AVG(amount), COUNT(*), COUNT(amount)
FROM orders;
-- SUM=60, AVG=20 (не 15!), COUNT(*)=4, COUNT(amount)=3

AVG делит сумму на COUNT(col), а не на COUNT(*). Если нужно, чтобы NULL считался как 0, оберните в COALESCE:

SELECT AVG(COALESCE(amount, 0)) FROM orders;

На собеседовании: «У нас 100 пользователей, у 50 есть amount, сумма 1000. Что даст AVG(amount)?» Ответ: 20, а не 10. Это частая ловушка в вопросах про метрики типа ARPU.

MIN и MAX работают и со строками, и с датами

SELECT
    MIN(created_at) AS first_order,
    MAX(created_at) AS last_order,
    MIN(product_name) AS first_alphabet,  -- лексикографический минимум
    MAX(product_name) AS last_alphabet
FROM orders JOIN products USING (product_id);

Для строк сравнение по алфавиту — редко осмысленно, но работает.

Тренироваться на таких вопросах можно в Telegram-боте Карьерник — там 1500+ задач с реальных собесов с разборами.

Условные агрегаты

Способ 1: CASE WHEN внутри агрегата

Классика, работает везде:

SELECT
    SUM(CASE WHEN status = 'paid' THEN amount ELSE 0 END) AS paid_revenue,
    COUNT(CASE WHEN status = 'refund' THEN 1 END) AS refund_count
FROM orders;

COUNT(CASE WHEN …) с ELSE NULL считает только строки, где условие выполнено.

Способ 2: FILTER WHERE (PostgreSQL, новые версии)

Более читаемый, рекомендуемый стандартом:

SELECT
    SUM(amount) FILTER (WHERE status = 'paid') AS paid_revenue,
    COUNT(*) FILTER (WHERE status = 'refund') AS refund_count
FROM orders;

FILTER работает в PostgreSQL, Snowflake, BigQuery (под разными названиями), ClickHouse. В MySQL и SQL Server — пишите CASE WHEN.

На собеседовании: знайте оба способа. FILTER — современный, CASE WHEN — универсальный.

Работа с текстом: STRING_AGG

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

SELECT user_id, STRING_AGG(product_name, ', ' ORDER BY created_at) AS purchases
FROM orders JOIN products USING (product_id)
GROUP BY user_id;

Результат: «user_1: товар А, товар Б, товар В».

• PostgreSQL — STRING_AGG(col, delimiter)
• MySQL — GROUP_CONCAT(col SEPARATOR delimiter)
• SQL Server — STRING_AGG(col, delimiter)
• ClickHouse — arrayStringConcat(groupArray(col), ', ')

ARRAY_AGG: собрать в массив

SELECT user_id, ARRAY_AGG(order_id ORDER BY created_at) AS orders
FROM orders
GROUP BY user_id;

Результат: user_1: {101, 105, 108}. Удобно, когда нужно дальше работать с массивом (распаковывать через UNNEST, проверять через ANY).

Процентили и медиана

Медиана — не базовая агрегатная функция. В SQL её считают через PERCENTILE_CONT или PERCENTILE_DISC:

SELECT
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY amount) AS p95,
    PERCENTILE_CONT(0.99) WITHIN GROUP (ORDER BY amount) AS p99
FROM orders;

• PERCENTILE_CONT — интерполирует (если медиана между двумя значениями — берёт среднее).
• PERCENTILE_DISC — возвращает реальное значение из данных.

На собеседовании: «Посчитайте медианный чек» — ответ: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount). Многие зачем-то начинают через оконные функции — это длиннее.

Статистика одной строкой

SELECT
    COUNT(*) AS n,
    AVG(amount) AS mean,
    STDDEV(amount) AS sd,                -- стандартное отклонение
    VARIANCE(amount) AS var,             -- дисперсия
    MIN(amount) AS min_val,
    MAX(amount) AS max_val,
    MAX(amount) - MIN(amount) AS range_val
FROM orders;

Это готовый describe() как в pandas — одна строка, и есть базовая статистика.

Агрегаты без GROUP BY

Важный момент: если в SELECT только агрегаты, GROUP BY не нужен — возвращается одна строка.

SELECT COUNT(*), SUM(amount), AVG(amount)
FROM orders;  -- OK, вернёт 1 строку

Но если добавить не-агрегатный столбец — обязательно GROUP BY:

-- ❌ Ошибка
SELECT user_id, COUNT(*) FROM orders;
-- ✅ Правильно
SELECT user_id, COUNT(*) FROM orders GROUP BY user_id;

К слову, набить руку на таких кейсах удобно через тренажёр в Telegram — разбирайте по 10 вопросов в день, через 2 недели тема становится рефлексом.

DISTINCT внутри агрегатов

COUNT(DISTINCT x) — самый частый случай. Работают также SUM(DISTINCT), AVG(DISTINCT), но они редко нужны:

SELECT
    COUNT(DISTINCT user_id) AS unique_users,
    COUNT(DISTINCT session_id) AS unique_sessions,
    SUM(DISTINCT amount) AS sum_of_unique_amounts  -- редко имеет смысл
FROM events;

Нюанс производительности: COUNT(DISTINCT) в больших таблицах — тяжёлая операция. В ClickHouse есть приближённый uniqCombined (как HyperLogLog), в BigQuery — APPROX_COUNT_DISTINCT. На собеседовании стоит упомянуть, что знаете про approx-версии.

Агрегаты в оконных функциях

SUM, AVG, COUNT можно использовать как оконные:

SELECT
    user_id,
    order_id,
    amount,
    SUM(amount) OVER (PARTITION BY user_id) AS user_total,
    AVG(amount) OVER (PARTITION BY user_id) AS user_avg,
    amount - AVG(amount) OVER (PARTITION BY user_id) AS deviation_from_avg
FROM orders;

Отличие от обычного GROUP BY: строки не сворачиваются, просто к каждой добавляется агрегат группы. Подробнее — в шпаргалке по оконным функциям.

10 задач с собеседований

Задача 1. Общая выручка и средний чек

SELECT SUM(amount), AVG(amount), COUNT(*) FROM orders;

Задача 2. Количество уникальных пользователей и сессий

SELECT COUNT(DISTINCT user_id), COUNT(DISTINCT session_id) FROM events;

Задача 3. Медианный чек по городам

SELECT city, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median
FROM orders
GROUP BY city;

Задача 4. Доля заказов со статусом paid

SELECT
    COUNT(*) FILTER (WHERE status = 'paid') * 1.0 / COUNT(*) AS paid_rate
FROM orders;

Задача 5. Конверсия из регистрации в первую покупку

SELECT
    COUNT(DISTINCT u.user_id) AS registered,
    COUNT(DISTINCT o.user_id) AS paid,
    ROUND(100.0 * COUNT(DISTINCT o.user_id) / COUNT(DISTINCT u.user_id), 2) AS conv_pct
FROM users u
LEFT JOIN orders o ON u.user_id = o.user_id;

Задача 6. Товары, купленные больше 100 раз

SELECT product_id, COUNT(*) AS sold
FROM order_items
GROUP BY product_id
HAVING COUNT(*) > 100;

Задача 7. Средний чек и стандартное отклонение по категориям

SELECT category, AVG(amount) AS mean, STDDEV(amount) AS sd
FROM orders JOIN products USING (product_id)
GROUP BY category;

Задача 8. Процент активных пользователей

SELECT
    COUNT(*) FILTER (WHERE last_active >= CURRENT_DATE - INTERVAL '30 day') * 100.0 / COUNT(*) AS active_pct
FROM users;

Задача 9. Список купленных товаров одной строкой на пользователя

SELECT
    user_id,
    STRING_AGG(DISTINCT product_name, ', ' ORDER BY product_name) AS products
FROM orders JOIN products USING (product_id)
GROUP BY user_id;

Задача 10. Суммарная и накопительная выручка по дням

SELECT
    created_at::DATE AS day,
    SUM(amount) AS daily,
    SUM(SUM(amount)) OVER (ORDER BY created_at::DATE) AS cumulative
FROM orders
GROUP BY created_at::DATE;

Двойной SUM — внешний оконный, внутренний обычный. Частая задача на middle-позиции.

---

Как тренироваться

Агрегаты учатся двумя видами практики: сначала чистые агрегаты (COUNT, SUM, AVG с фильтрами), потом условные (FILTER и CASE WHEN). После этого добавляется уверенность в NULL-поведении, и вы перестаёте путаться в собеседовательных ловушках.

Тренажёр Карьерник содержит блок задач на агрегаты, включая типовые ловушки: NULL в AVG, разные COUNT, условные суммы, медиана и процентили. 10 минут в день — и блок на собеседовании проходится за минуту.

Читайте также

• GROUP BY SQL: шпаргалка
• Оконные функции SQL: шпаргалка
• COUNT(*) vs COUNT(column) в SQL
• Подготовка к собеседованию аналитика

FAQ

Почему AVG игнорирует NULL?

По стандарту SQL все агрегатные функции игнорируют NULL (кроме COUNT(*)). AVG делит сумму на количество не-NULL значений, а не на количество строк. Если нужно, чтобы NULL считался как 0, используйте COALESCE: AVG(COALESCE(col, 0)). Это важно в метриках типа ARPU/ARPPU, где неправильный подсчёт даёт в разы завышенные значения.

Как посчитать медиану в SQL?

Через PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col). В ClickHouse — quantile(0.5)(col). В SQL Server — PERCENTILE_CONT через оконную функцию. Обычной функции MEDIAN в стандарте нет, хотя Oracle и Snowflake её предоставляют.

Когда использовать FILTER, а когда CASE WHEN?

FILTER — чище и рекомендуется стандартом SQL:2003, работает в PostgreSQL, Snowflake, BigQuery. CASE WHEN — универсальный, работает во всех СУБД включая MySQL и SQL Server. Если пишете кросс-СУБД код — CASE WHEN. Если один PostgreSQL — FILTER.

Можно ли сделать COUNT(DISTINCT) по нескольким колонкам?

В PostgreSQL — да: COUNT(DISTINCT (a, b)). В MySQL — да: COUNT(DISTINCT a, b). В SQL Server — только через обходное решение: COUNT(DISTINCT CONCAT(a, '_', b)) (с осторожностью из-за коллизий). Наиболее универсально — собрать комбинацию в подзапросе и посчитать.