Оконные функции: ROW_NUMBER, LAG, RANK и агрегаты

Оконные функции: ROW_NUMBER, LAG, RANK и агрегаты

Главная идея

Как это выглядит на практике

1. Тебе нужно показать топ-5 товаров по продажам в каждой категории на странице категорий. Первое решение: загрузить все продажи в Ruby, сгруппировать по категориям, отсортировать, взять топ-5 каждой. При 1000 категорий — медленно и расточительно.
2. Узнаёшь про оконные функции. Пишешь ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY sales DESC) и фильтруешь WHERE rn <= 5 через CTE. Один запрос возвращает ровно нужные данные — 50ms вместо 5 секунд.
3. Аналитик просит добавить в дашборд процентное изменение выручки к предыдущему месяцу. Раньше писал два запроса и вычислял в коде. С LAG() OVER (ORDER BY month): revenue / LAG(revenue) OVER (ORDER BY month) - 1 — один запрос.
4. Появляется задача: для каждого заказа показать порядковый номер покупки данного клиента (первая, вторая, третья...). ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) решает за секунду то, что иначе требовало CTE с самоджойном.
5. Нужен нарастающий итог выручки с начала года. SUM(amount) OVER (ORDER BY day ROWS UNBOUNDED PRECEDING) — три слова меняют обычную агрегацию в нарастающий итог.
6. Скользящее среднее за 30 дней для сглаживания сезонных колебаний: AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 29 PRECEDING AND CURRENT ROW). Данные не покидают БД.
7. Задача дедупликации: в таблице дубликаты пользователей, нужно оставить последнего. DELETE FROM users WHERE id NOT IN (SELECT MAX(id) OVER (PARTITION BY email) FROM users) — один запрос.
8. Понимаешь что оконные функции нельзя использовать в WHERE напрямую. Первая попытка: WHERE RANK() OVER (...) <= 3 — синтаксическая ошибка. Решение: выносить в CTE или подзапрос, фильтровать снаружи.
9. На code review видишь запрос с LEFT JOIN + подзапрос для получения предыдущего значения (classic self-join anti-pattern). Показываешь коллеге LAG() — код становится в 3 раза короче и понятнее.
10. Требование: распределить пользователей по квартилям расходов (25%/50%/75%/100%). NTILE(4) OVER (ORDER BY total_spent) делит на 4 равные группы. Один запрос вместо двух-трёх с PERCENTILE_DISC.
11. На производственной нагрузке замечаешь что запрос с несколькими оконными функциями работает медленно. EXPLAIN показывает несколько Sort узлов — каждая оконная функция с разным ORDER BY требует отдельную сортировку. Используешь WINDOW clause для переиспользования определения окна.
12. Интервью: просят написать запрос 'найти второй по величине заказ каждого пользователя'. Пишешь: WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY amount DESC) AS rn FROM orders) SELECT * FROM ranked WHERE rn = 2. Решение за 30 секунд.

Примеры кода

Ранжирование: ROW_NUMBER, RANK, DENSE_RANK

-- Топ-3 товара по цене в каждой категории
WITH ranked_products AS (
  SELECT
    p.id,
    p.name,
    p.price,
    c.name AS category,
    ROW_NUMBER()  OVER (PARTITION BY p.category_id ORDER BY p.price DESC) AS row_num,
    RANK()        OVER (PARTITION BY p.category_id ORDER BY p.price DESC) AS rank,
    DENSE_RANK()  OVER (PARTITION BY p.category_id ORDER BY p.price DESC) AS dense_rank
  FROM products p
  JOIN categories c ON c.id = p.category_id
  WHERE p.is_active = true
)
SELECT *
FROM ranked_products
WHERE row_num <= 3
ORDER BY category, row_num;

-- Разница между ROW_NUMBER, RANK, DENSE_RANK при ровных значениях:
-- Если два товара стоят одинаково (цена = 1000):
-- ROW_NUMBER: 1, 2, 3, 4   (всегда уникальные, произвольный порядок при ровных)
-- RANK:       1, 1, 3, 4   (пропуск 2 после двух первых мест)
-- DENSE_RANK: 1, 1, 2, 3   (без пропусков)

-- Перцентили: в каком квантиле находится каждый пользователь по расходам?
SELECT
  user_id,
  total_spent,
  NTILE(4) OVER (ORDER BY total_spent) AS quartile,
  PERCENT_RANK() OVER (ORDER BY total_spent) AS percentile
FROM (
  SELECT user_id, SUM(amount) AS total_spent
  FROM orders
  WHERE status = 'completed'
  GROUP BY user_id
) user_totals
ORDER BY total_spent DESC;

LAG, LEAD и сравнение с предыдущими периодами

-- Динамика выручки: абсолютное и процентное изменение к предыдущему месяцу
WITH monthly_revenue AS (
  SELECT
    DATE_TRUNC('month', created_at) AS month,
    SUM(amount)                      AS revenue
  FROM orders
  WHERE status = 'completed'
  GROUP BY DATE_TRUNC('month', created_at)
)
SELECT
  month,
  revenue,
  LAG(revenue, 1) OVER (ORDER BY month)  AS prev_month_revenue,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) AS absolute_change,
  ROUND(
    (revenue - LAG(revenue, 1) OVER (ORDER BY month))
      * 100.0
      / NULLIF(LAG(revenue, 1) OVER (ORDER BY month), 0),
    2
  ) AS pct_change,
  LEAD(revenue, 1) OVER (ORDER BY month)  AS next_month_revenue
FROM monthly_revenue
ORDER BY month;

-- Разница с одним из лучших показателей: отклонение от максимума
SELECT
  month,
  revenue,
  MAX(revenue) OVER ()                                           AS max_revenue,
  revenue - MAX(revenue) OVER ()                                 AS vs_max,
  FIRST_VALUE(revenue) OVER (ORDER BY month)                     AS first_month,
  revenue - FIRST_VALUE(revenue) OVER (ORDER BY month)           AS vs_start
FROM monthly_revenue
ORDER BY month;

Нарастающие итоги и скользящие агрегаты

-- Нарастающий итог выручки и скользящее среднее за 7 дней
WITH daily AS (
  SELECT
    DATE(created_at) AS day,
    SUM(amount)      AS revenue
  FROM orders
  WHERE status = 'completed'
    AND created_at >= NOW() - INTERVAL '90 days'
  GROUP BY DATE(created_at)
)
SELECT
  day,
  revenue,

  -- Нарастающий итог с начала периода
  SUM(revenue) OVER (
    ORDER BY day
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total,

  -- Скользящее среднее за 7 дней (текущий + 6 предыдущих)
  ROUND(AVG(revenue) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 2) AS moving_avg_7d,

  -- Процент от общей выручки за период
  ROUND(revenue * 100.0 / SUM(revenue) OVER (), 2) AS pct_of_total,

  -- Нарастающий процент (cumulative distribution)
  ROUND(
    SUM(revenue) OVER (ORDER BY day ROWS UNBOUNDED PRECEDING)
      * 100.0 / SUM(revenue) OVER (),
    2
  ) AS cumulative_pct

FROM daily
ORDER BY day;

-- Найти дни с аномально низкой выручкой (ниже 50% от скользящего среднего)
WITH daily_with_avg AS (
  SELECT
    day,
    revenue,
    AVG(revenue) OVER (
      ORDER BY day ROWS BETWEEN 13 PRECEDING AND CURRENT ROW
    ) AS moving_avg_14d
  FROM daily
)
SELECT day, revenue, ROUND(moving_avg_14d, 0) AS avg_14d
FROM daily_with_avg
WHERE revenue < moving_avg_14d * 0.5
ORDER BY day;

Что происходит под капотом

• Оконные функции выполняются в отдельной фазе плана после WHERE, JOIN, GROUP BY, HAVING. PostgreSQL сначала строит промежуточный набор строк, затем применяет оконные функции. Именно поэтому их нельзя использовать в WHERE той же команды.
• Sort узел: каждая оконная функция с уникальным ORDER BY требует отдельную сортировку O(n log n). WINDOW clause позволяет переиспользовать определение: WINDOW w AS (PARTITION BY category_id ORDER BY price). Это экономит повторные сортировки.
• Frame clause ROWS vs RANGE: ROWS считает физические строки. RANGE считает логически по значению ORDER BY — если несколько строк имеют одинаковое значение ORDER BY, RANGE включает их все в текущую строку. Для временных рядов с возможными одинаковыми датами — ROWS безопаснее.
• ROW_NUMBER() не имеет frame clause — она всегда смотрит на весь раздел. RANK() и DENSE_RANK() аналогично. LAG() и LEAD() — фиксированное смещение, frame clause не применяется.
• Производительность: оконная функция над 1M строк с сортировкой — это Sort O(n log n) + Window Agg O(n). Для скользящих агрегатов PostgreSQL использует incremental mode: не пересчитывает окно с нуля для каждой строки, а обновляет состояние (добавляет входящую, убирает выходящую строку).
• WindowAgg узел в EXPLAIN: Sequential Window Aggregation проходит по отсортированным данным слева направо, поддерживая состояние агрегата. Для PARTITION BY — перезапускает агрегат при смене значения партиционирования.
• FILTER с оконными функциями: SUM(amount) FILTER (WHERE status = 'completed') OVER (PARTITION BY user_id) — условная агрегация внутри окна. Мощный инструмент для условных нарастающих итогов.
• Distinct в оконных функциях: COUNT(DISTINCT product_id) OVER (...) не поддерживается в большинстве СУБД включая PostgreSQL. Обходной путь — плотная нумерация через DENSE_RANK() и подсчёт уникальных рангов.

Термины урока

Мини-разбор реальной ситуации

Аналитический дашборд для маркетинга: топ-5 продуктов по выручке в каждой категории + процент от выручки категории + изменение за неделю. Первоначальная реализация: 3 отдельных запроса + merge в Ruby, 200 строк кода, 1.2 секунды. Переписано с оконными функциями: WITH ranked AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY revenue DESC) AS rn, revenue * 100.0 / SUM(revenue) OVER (PARTITION BY category_id) AS pct_of_category, revenue - LAG(revenue) OVER (PARTITION BY category_id ORDER BY week) AS week_delta FROM product_weekly_stats) SELECT * FROM ranked WHERE rn <= 5. Один запрос, 45 миллисекунд.