Какие знаешь агрегатные функции?

Агрегатные функции в SQL: Практическое применение для Business Analyst

Агрегатные функции (aggregate functions) — это мощный инструмент для анализа данных. За 10+ лет я использовал их почти каждый день при работе с аналитикой, метриками и отчётами. Расскажу о самых важных и как я их применяю на практике.

Что такое агрегатные функции?

Агрегатные функции — это SQL функции, которые берут несколько строк и возвращают одно значение (агрегат).

Основные агрегатные функции:

• COUNT() — посчитать количество
• SUM() — сумма
• AVG() — среднее значение
• MIN() — минимум
• MAX() — максимум
• GROUP_CONCAT() — объединить в строку

1. COUNT() — Подсчёт количества

Базовый пример:

SELECT COUNT(*) as total_users
FROM users;

Результат: 50000 (всего пользователей)

С условием:

SELECT COUNT(*) as active_users
FROM users
WHERE last_login > DATE_SUB(NOW(), INTERVAL 30 DAY);

Результат: 15000 (активные за последние 30 дней)

С GROUP BY (считаем по категориям):

SELECT 
  country,
  COUNT(*) as user_count
FROM users
GROUP BY country
ORDER BY user_count DESC;

Результат:
USA       | 20000
Europe    | 15000
Asia      | 10000
Other     | 5000

В контексте бизнеса:

• "Сколько пользователей в каждой стране?" → COUNT() с GROUP BY
• "Сколько заказов сделал каждый клиент?" → COUNT() по клиентам

COUNT(DISTINCT)

Пример: Уникальные пользователи

SELECT COUNT(DISTINCT user_id) as unique_users
FROM orders;

Результат: 12000 (12000 пользователей сделали заказы)

Важно: COUNT(*) вернул бы 50000 (количество заказов), а COUNT(DISTINCT user_id) вернёт количество уникальных пользователей.

В контексте бизнеса:

• DAU (Daily Active Users) → COUNT(DISTINCT user_id) за день
• Repeat customers → COUNT(DISTINCT user_id) с количеством заказов > 1

2. SUM() — Сумма

Пример: Общая выручка

SELECT SUM(amount) as total_revenue
FROM orders;

Результат: 5000000 ($5M выручка)

По месяцам:

SELECT 
  DATE_TRUNC('month', created_at) as month,
  SUM(amount) as monthly_revenue
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month DESC;

Результат:
2024-03 | 500000
2024-02 | 480000
2024-01 | 450000

В контексте бизнеса:

• MRR (Monthly Recurring Revenue) → SUM() по месячным подпискам
• Total customer lifetime value → SUM() всех платежей user'а
• Profit by segment → SUM(revenue) - SUM(costs) по сегментам

Conditional SUM

Пример: Разные суммы по типам

SELECT 
  SUM(CASE WHEN status = 'completed' THEN amount ELSE 0 END) as completed_revenue,
  SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END) as pending_revenue,
  SUM(CASE WHEN status = 'cancelled' THEN amount ELSE 0 END) as cancelled_revenue
FROM orders;

Результат:
completed_revenue | 4800000
pending_revenue   | 150000
cancelled_revenue | 50000

3. AVG() — Среднее значение

Пример: Средний размер заказа

SELECT AVG(amount) as avg_order_value
FROM orders;

Результат: 100 (средний заказ $100)

По сегментам:

SELECT 
  customer_segment,
  AVG(amount) as avg_order_value,
  COUNT(*) as order_count
FROM orders
GROUP BY customer_segment;

Результат:
Premium     | 500   | 8000
Standard    | 100   | 40000
Free        | 20    | 10000

В контексте бизнеса:

• AOV (Average Order Value) — key метрик для e-commerce
• ARPU (Average Revenue Per User) — ARPU = Total Revenue / Total Users
• Average handling time в support

4. MIN() и MAX() — Минимум и максимум

Пример: Диапазон цен

SELECT 
  MIN(price) as lowest_price,
  MAX(price) as highest_price,
  MAX(price) - MIN(price) as price_range
FROM products;

Результат:
lowest_price  | 5
highest_price | 1000
price_range   | 995

По датам: Первый и последний заказ пользователя

SELECT 
  user_id,
  MIN(created_at) as first_order_date,
  MAX(created_at) as last_order_date,
  DATEDIFF(day, MIN(created_at), MAX(created_at)) as days_between
FROM orders
GROUP BY user_id;

В контексте бизнеса:

• Самый дорогой товар, самый дешевый товар
• Дата первого использования юзера
• Дата последнего платежа (для выявления churn)

5. GROUP_CONCAT() или STRING_AGG() — Объединение строк

Пример: Список товаров в заказе

SELECT 
  order_id,
  GROUP_CONCAT(product_name, ', ') as products
FROM order_items
GROUP BY order_id;

Результат:
order_123 | "Laptop, Mouse, Keyboard, Monitor"
order_124 | "Phone, Case, Screen Protector"

В контексте бизнеса:

• Быстрый preview что в заказе
• Список активных фич пользователя

Комбинирование агрегатов: Реальный пример

Задача: Анализ продаж по месяцам и сегментам

SELECT 
  DATE_TRUNC('month', o.created_at) as month,
  u.segment,
  COUNT(DISTINCT o.id) as order_count,
  COUNT(DISTINCT o.user_id) as unique_customers,
  SUM(o.amount) as total_revenue,
  AVG(o.amount) as avg_order_value,
  MIN(o.amount) as min_order,
  MAX(o.amount) as max_order
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at >= '2024-01-01'
GROUP BY DATE_TRUNC('month', o.created_at), u.segment
ORDER BY month DESC, total_revenue DESC;

Результат:
month   | segment  | orders | customers | revenue | avg_order | min | max
────────┼──────────┼────────┼───────────┼─────────┼───────────┼─────┼─────
2024-03 | Premium  | 8000   | 1500      | 4000000 | 500       | 100 | 5000
2024-03 | Standard | 40000  | 8000      | 4000000 | 100       | 10  | 1000
2024-03 | Free     | 10000  | 5000      | 200000  | 20        | 5   | 100
2024-02 | Premium  | 7500   | 1400      | 3750000 | 500       | 100 | 5000
...

Эта одна query вернула полный анализ! Я вижу:

• Какой месяц лучший (март лучше февраля)
• Какой сегмент самый прибыльный (Premium)
• AOV для каждого сегмента
• Trend по месяцам

HAVING — Фильтрация агрегатов

Проблема: Я хочу видеть только сегменты с revenue > $1M

SELECT 
  customer_segment,
  SUM(amount) as total_revenue
FROM orders
GROUP BY customer_segment
HAVING SUM(amount) > 1000000;  -- только > $1M
ORDER BY total_revenue DESC;

Результат:
customer_segment | total_revenue
─────────────────┼──────────────
Premium          | 4000000
Standard         | 2500000

Разница:

• WHERE фильтрует строки ДО агрегации
• HAVING фильтрует ДО результаты ПОСЛЕ агрегации

В контексте бизнеса:

• "Показать мне только города с более чем 1000 заказов в месяц"
• "Показать только пользователей, которые потратили более $500"

Практические примеры из моей работы

Пример 1: Чёрный список пользователей

Часто нужно найти пользователей, которые совершают подозрительную активность.

SELECT 
  user_id,
  email,
  COUNT(*) as order_count,
  SUM(amount) as total_spent,
  COUNT(DISTINCT payment_method) as payment_methods_count,
  COUNT(CASE WHEN status = 'refunded' THEN 1 END) as refunds
FROM orders
GROUP BY user_id, email
HAVING 
  refunds > 3  -- более 3 возвратов
  AND payment_methods_count > 5  -- множество способов оплаты
ORDER BY refunds DESC;

Это выявляет потенциально мошеннические user'ов.

Пример 2: Cohort Analysis (Анализ когорт)

Сравниваем retention разных cohort'ов (групп пользователей, зарегистрировавшихся в одном месяце).

SELECT 
  DATE_TRUNC('month', u.created_at) as signup_month,
  DATE_TRUNC('month', o.created_at) as order_month,
  COUNT(DISTINCT o.user_id) as active_users
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.created_at >= u.created_at  -- только orders после signup
GROUP BY DATE_TRUNC('month', u.created_at), DATE_TRUNC('month', o.created_at)
ORDER BY signup_month, order_month;

Результат: Таблица retention
signup | month1 | month2 | month3 | month4
────────┼────────┼────────┼────────┼────────
Jan    | 10000  | 6000   | 4200   | 3000
Feb    | 12000  | 7000   | 4800   | ...
Mar    | 15000  | 8500   | ...    | ...

Эта таблица показывает, как много пользователей из каждой cohort'ы остаются активны.

Пример 3: Чувствительность к цене (Price Sensitivity)

Проверяю, как цена влияет на конверсию.

SELECT 
  ROUND(price, 0) as price_point,
  COUNT(*) as impressions,
  COUNT(CASE WHEN purchased = 1 THEN 1 END) as purchases,
  ROUND(100.0 * COUNT(CASE WHEN purchased = 1 THEN 1 END) / COUNT(*), 2) as conversion_rate
FROM product_views
GROUP BY ROUND(price, 0)
HAVING COUNT(*) > 100  -- только если достаточно данных
ORDER BY price;

Результат:
price | impressions | purchases | conversion
──────┼─────────────┼───────────┼───────────
10    | 5000        | 800       | 16.00%
20    | 4800        | 720       | 15.00%
50    | 4000        | 400       | 10.00%
100   | 2000        | 120       | 6.00%

Видно, что цена 10 имеет лучшую конверсию.

Ошибки, которых я избегаю

Ошибка 1: Использование SUM без GROUP BY

-- Неправильно
SELECT user_id, SUM(amount)
FROM orders;
-- Ошибка: какой user_id выбрать?

-- Правильно
SELECT user_id, SUM(amount)
FROM orders
GROUP BY user_id;

**Ошибка 2: COUNT(*) когда нужен COUNT(DISTINCT)

-- Неправильно (считает строки, не пользователей)
SELECT COUNT(*) FROM orders;
-- Результат: 50000 (количество заказов)

-- Правильно
SELECT COUNT(DISTINCT user_id) FROM orders;
-- Результат: 12000 (количество уникальных пользователей)

Ошибка 3: Смешивание агрегатов с columns, которые не в GROUP BY

-- Неправильно в некоторых БД
SELECT user_id, email, SUM(amount)
FROM orders
GROUP BY user_id;
-- email не в GROUP BY, может быть ошибка

-- Правильно
SELECT user_id, SUM(amount)
FROM orders
GROUP BY user_id;
-- или если нужен email
SELECT o.user_id, u.email, SUM(o.amount)
FROM orders o
JOIN users u ON o.user_id = u.id
GROUP BY o.user_id, u.email;

Результат мастерства в агрегатных функциях

• Быстрые insights из данных (одна query вместо множества)
• Автоматизированные отчёты (query делает всю работу)
• Глубокое понимание бизнеса (видишь паттерны, которые другие пропускают)
• Быстрое обнаружение проблем (anomaly detection через агрегаты)

Агрегатные функции — это основа SQL анализа. Business Analyst без мастерства в них — это как водитель без умения ездить.