SQL для аналитики: метрики, KPI, срезы и фильтры

Что такое метрики и KPI в SQL? 🔍

Метрики — это количественные показатели, которые помогают оценить эффективность бизнеса. KPI (Key Performance Indicators) — ключевые метрики, напрямую влияющие на успех. В SQL мы вычисляем их с помощью агрегатных функций:

-- Пример расчёта базовых метрик для интернет-магазина
SELECT 
    COUNT(*) AS total_orders,
    SUM(revenue) AS total_revenue,
    AVG(revenue) AS avg_order_value,
    COUNT(DISTINCT user_id) AS unique_customers
FROM orders
WHERE order_date BETWEEN '2023-01-01' AND '2023-01-31';

Основные агрегатные функции для аналитики 📊

Вот «золотая пятёрка» функций, которые используют 95% аналитиков:

COUNT() — подсчёт строк
SUM() — сумма значений
AVG() — среднее значение
MIN()/MAX() — экстремальные значения
COUNT(DISTINCT) — уникальные значения

-- Анализ поведения пользователей
SELECT
    COUNT(DISTINCT session_id) AS sessions,
    COUNT(*) AS pageviews,
    COUNT(DISTINCT user_id) AS active_users,
    AVG(time_on_page) AS avg_engagement
FROM user_activity
WHERE date = CURRENT_DATE - 1;

Фильтрация данных: WHERE vs HAVING 🔧

WHERE фильтрует строки перед агрегацией
HAVING фильтрует результаты агрегации

-- Находим категории с высокой средней выручкой
SELECT 
    category,
    AVG(revenue) AS avg_revenue
FROM products
WHERE in_stock = TRUE  -- Фильтр до агрегации
GROUP BY category
HAVING AVG(revenue) > 1000  -- Фильтр после агрегации
ORDER BY avg_revenue DESC;

Срезы данных: GROUP BY и временные периоды ⏳

Группировка позволяет анализировать метрики по сегментам:

-- Анализ выручки по неделям и категориям
SELECT
    DATE_TRUNC('week', order_date) AS week,
    category,
    SUM(revenue) AS total_revenue,
    COUNT(*) AS order_count
FROM orders
GROUP BY 1, 2  -- Группировка по неделе и категории
ORDER BY week, total_revenue DESC;

Совет: используйте DATE_TRUNC() для стандартных временных интервалов (день, неделя, месяц).

Продвинутые техники: оконные функции 🚀

Оконные функции позволяют делать расчёты без группировки данных:

-- Сравнение выручки с предыдущим периодом
SELECT
    DATE_TRUNC('month', order_date) AS month,
    SUM(revenue) AS monthly_revenue,
    LAG(SUM(revenue), 1) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS prev_month_revenue,
    SUM(revenue) - LAG(SUM(revenue), 1) OVER (ORDER BY DATE_TRUNC('month', order_date)) AS revenue_growth
FROM orders
GROUP BY 1
ORDER BY 1;

Практический кейс: анализ воронки продаж 🛒

Давайте построим полный путь пользователя:

WITH funnel AS (
    SELECT
        COUNT(DISTINCT visit_id) AS visits,
        COUNT(DISTINCT CASE WHEN added_to_cart THEN visit_id END) AS cart_adds,
        COUNT(DISTINCT CASE WHEN checkout_started THEN visit_id END) AS checkouts,
        COUNT(DISTINCT CASE WHEN purchase_completed THEN visit_id END) AS purchases
    FROM user_journey
)
SELECT
    visits,
    cart_adds,
    ROUND(100.0 * cart_adds / visits, 2) AS visit_to_cart_rate,
    checkouts,
    ROUND(100.0 * checkouts / cart_adds, 2) AS cart_to_checkout_rate,
    purchases,
    ROUND(100.0 * purchases / checkouts, 2) AS checkout_to_purchase_rate
FROM funnel;

Оптимизация запросов для аналитики ⚡

Всегда фильтруйте данные как можно раньше с помощью WHERE
Используйте CTE (WITH) для сложных вычислений
Ограничивайте выборку при тестировании: LIMIT 100
Создавайте индексы на часто используемых полях фильтрации

Подведём итоги: чек-лист аналитика ✅

Определите ключевые метрики для вашего бизнеса
Проверьте фильтры (WHERE/HAVING)
Выберите правильный уровень детализации (GROUP BY)
Рассчитайте дополнительные показатели (конверсии, динамику)
Визуализируйте результат (графики, таблицы)