Оконные функции SQL без магии

SQL Studio / Разборы SQL / Оконные функции SQL без магии

Зачем нужен ROW_NUMBER

Допустим, вы хотите присвоить каждому заказу номер покупки внутри конкретного клиента. GROUP BY свернёт заказы клиента в одну строку — нам это не подходит.

SELECT
  customer_id,
  order_id,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY created_at, order_id
  ) AS purchase_n
FROM orders
ORDER BY customer_id, purchase_n;

PARTITION BY запускает нумерацию заново для каждого клиента. ORDER BY created_at, order_id задаёт порядок даже при одинаковых датах.

Здесь нумеруются все заказы, включая ещё не доставленные. Если нужен номер успешной покупки, добавьте фильтр по статусу до оконной функции.

Два разных ORDER BY: выражение внутри OVER задаёт порядок расчёта ROW_NUMBER, а внешний ORDER BY — порядок строк результата. Без внешнего ORDER BY показываемый порядок не гарантируется ни SQLite, ни MySQL 8.

Чем отличается от GROUP BY

Запрос SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id возвращает одну строку на клиента. Оконный расчёт может вернуть строку на каждый заказ и дополнить её номером или суммой по клиенту.

SELECT
  customer_id,
  order_id,
  COUNT(*) OVER (
    PARTITION BY customer_id
  ) AS total_orders_of_customer
FROM orders
ORDER BY customer_id, order_id;

ROW_NUMBER, RANK и DENSE_RANK

ROW_NUMBER даёт уникальный порядковый номер каждой строке. RANK одинаково ранжирует равные значения, оставляя пропуски после ничьей. DENSE_RANK тоже учитывает ничьи, но не оставляет пропусков. Для «последнего заказа клиента» обычно подходит ROW_NUMBER с сортировкой по дате в обратном порядке.

Попрактикуйтесь

Дальше: последняя запись каждого клиента с одинаковыми датами; TOP-2 товара в каждой категории, ничьи и отличие RANK от ROW_NUMBER.

Для последовательной практики на MySQL 8 — курс «SQL для аналитика» на Stepik с итоговой работой и возможностью получить сертификат.