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

Короткий ответ

Оконная функция записывается с OVER (...):

функция() OVER (PARTITION BY группа ORDER BY порядок рамка)

• PARTITION BY делит строки на независимые группы (как GROUP BY, но без схлопывания).
• ORDER BY задаёт порядок внутри группы.
• Рамка (ROWS BETWEEN ...) уточняет, какие строки учитывать для текущей.

Главное отличие от GROUP BY: количество строк не меняется. Поэтому можно одновременно видеть сам заказ и, например, его место среди заказов клиента. Оконные функции поддерживают PostgreSQL, MySQL 8+, SQL Server, Oracle, SQLite и ClickHouse.

ROW_NUMBER, RANK и DENSE_RANK

Разница видна, когда значения совпадают:

• ROW_NUMBER — всегда уникальный номер, при равенстве порядок произвольный.
• RANK — одинаковые значения делят место, следующее место пропускается.
• DENSE_RANK — одинаковые значения делят место, без пропусков.

Задача 1. Топ-N в каждой группе

Три самых дорогих товара в каждой категории:

SELECT *
FROM (
  SELECT p.*,
         ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY price DESC) AS rn
  FROM products p
) t
WHERE rn <= 3;

Оконную функцию нельзя использовать в WHERE того же запроса: окна вычисляются после фильтрации. Поэтому нужен подзапрос или CTE. Если при равных ценах нужно показать все товары, замените ROW_NUMBER на DENSE_RANK.

Задача 2. Накопительный итог

Выручка нарастающим итогом по дням:

SELECT day, revenue,
       SUM(revenue) OVER (
         ORDER BY day
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM daily_sales;

Рамку лучше указывать явно. Если её опустить, при ORDER BY используется рамка RANGE, и строки с одинаковым значением сортировки попадут в сумму все сразу — итог будет «прыгать».

Скользящее среднее за 7 дней — та же идея с другой рамкой: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW. Работает корректно, если у вас ровно одна строка на каждый день без пропусков.

Задача 3. Изменение месяц к месяцу

LAG берёт значение из предыдущей строки, LEAD — из следующей.

WITH monthly AS (
  SELECT DATE_TRUNC('month', created_at) AS month, SUM(total) AS revenue
  FROM orders
  GROUP BY 1
)
SELECT month, revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_revenue,
       ROUND(100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
             / NULLIF(LAG(revenue) OVER (ORDER BY month), 0), 1) AS change_pct
FROM monthly
ORDER BY month;

Для первого месяца LAG вернёт NULL — это нормально. NULLIF защищает от деления на ноль. Если какого-то месяца нет в данных, LAG сравнит с более ранним — заполняйте пропуски календарём.

Задача 4. Удаление дублей

Оставить для каждого email только самую свежую запись:

DELETE FROM users
WHERE id IN (
  SELECT id FROM (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY created_at DESC) AS rn
    FROM users
  ) d
  WHERE rn > 1
);

Сначала выполните внутренний SELECT и посмотрите, что будет удалено, и сделайте бэкап. После очистки добавьте уникальный индекс, чтобы дубли не появились снова.

Частые ошибки

• Фильтр по оконной функции в WHERE вместо внешнего запроса.
• Забытый PARTITION BY — нумерация идёт по всей таблице, а не внутри группы.
• Неявная рамка RANGE в накопительных суммах.
• ROW_NUMBER без однозначного ORDER BY — результат может меняться между запусками; добавьте в сортировку id.