Как работает функция ROW_NUMBER() в SQL и зачем нужны OVER и PARTITION BY?

ROW_NUMBER() — это оконная функция, которая присваивает каждой строке результата уникальный порядковый номер 1, 2, 3, ... в рамках заданного окна и порядка сортировки.

Разберём синтаксис OVER (PARTITION BY ... ORDER BY ...):

• OVER — обязательная часть, которая объявляет, что функция оконная, и задаёт «окно» (набор строк), над которым она работает.
• PARTITION BY — разбивает строки на группы (партиции). Нумерация начинается заново с 1 в каждой группе. Если PARTITION BY не указан — всё считается одной большой группой.
• ORDER BY (внутри OVER) — задаёт порядок, в котором назначаются номера. Для ROW_NUMBER() он обязателен по смыслу: без него порядок номеров недетерминирован.

Простой пример — сквозная нумерация сотрудников по зарплате:

SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;

С PARTITION BY нумерация рестартует внутри каждого отдела:

SELECT
    name,
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
FROM employees;

Теперь у каждого отдела свой rn = 1 у самого высокооплачиваемого.

Классическая задача — взять самую свежую запись по каждому пользователю. Нумеруем заказы внутри партиции пользователя по дате убывания и берём первый:

WITH ranked AS (
    SELECT
        o.*,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
    FROM orders o
)
SELECT *
FROM ranked
WHERE rn = 1;

Важный момент: оконные функции нельзя использовать прямо в WHERE той же выборки (они вычисляются позже фильтрации), поэтому оборачиваем в CTE или подзапрос, а уже снаружи фильтруем WHERE rn = 1.