PARTITION BY рассказывает окну, на какие куски разбить таблицу. ORDER BY внутри OVER рассказывает, в каком порядке проходить строки внутри партиции. И как только появляется порядок — открывается целое семейство функций, у которых раньше не было смысла: ранжирующие.

ORDER BY внутри OVER — это не финальный ORDER BY

Сразу важный нюанс: ORDER BY бывает в двух местах запроса, и они разные.

• ORDER BY в конце запроса — сортировка финального результата для вывода.
• ORDER BY внутри OVER (...) — упорядочивание строк внутри окна для вычисления оконной функции. На вывод никак не влияет напрямую.

Это часто путает новичков: «я же написал ORDER BY в окне, почему результат не отсортирован?». Потому что окно — это контекст для функции, а не для вывода.

Внутри окна ORDER BY signup_date упорядочивает клиентов по дате — это позволяет ROW_NUMBER пронумеровать их «1, 2, 3, …» в порядке регистрации. А внешний ORDER BY country, signup_date уже выводит финальную таблицу удобно для глаза.

ROW_NUMBER, RANK, DENSE_RANK — три способа считать

Все три функции делают похожее: проставляют номер каждой строке внутри партиции в порядке ORDER BY. Разница — что они делают на равных значениях.

Запомнить правило просто: ROW_NUMBER никогда не повторяется, RANK повторяется и пропускает номера, DENSE_RANK повторяется и не пропускает.

В нашей таблице не так много дубликатов — попробуй сам найти партицию, где RANK и DENSE_RANK совпадают (нет дублей), и где ROW_NUMBER ломает связку (для Ирины с NULL в birth_year).

NULLS FIRST / NULLS LAST

Тонкость, которую легко проглядеть: ORDER BY внутри окна по умолчанию сортирует так же, как и снаружи — NULL в PostgreSQL идёт в конце при ASC и в начале при DESC. Это часто не то, что нужно: для ранжирования обычно лучше поставить NULLS LAST явно, чтобы NULL не оказались на первой позиции при DESC.

Это особенно критично, если ты выбираешь топ — иначе можешь случайно вывести «самый высокий» как NULL.

NTILE — разбиение на N равных бакетов

NTILE(N) — отдельная функция: она разбивает строки партиции на N примерно равных групп и проставляет каждой строке номер её группы. Полезно для квартилей, перцентилей, ABC-анализа.

• NTILE(4) поделит строки на 4 группы: первая четверть (самые маленькие) — 1, следующая — 2, и так далее.
• PERCENT_RANK возвращает позицию строки от 0 до 1 ((rank - 1) / (total - 1)).
• CUME_DIST — куммулятивная доля: «какая доля строк имеет значение ≤ моему».

Если строк не делится поровну на N — первые группы получают на одну строку больше. На очень маленьких выборках (как у нас) это заметно: NTILE(4) на 16 строках даст по 4 в группе, но на 17 — первая группа будет из 5.

Top-N per group — главная задача ранжирования

Классика, которую спрашивают на собеседованиях аналитиков: «найди топ-3 самых дорогих продукта в каждой категории». Без окон это решается через коррелированный подзапрос и медленно. С окнами — две строки.

Идея простая: для каждой партиции (категории) проставить ROW_NUMBER по убыванию цены, потом отфильтровать rn <= 3. И тут возникает классическая ловушка: фильтровать окно нужно в обёрточном запросе (помнишь — окно не работает в WHERE).

Этот же паттерн работает для:

• топ-3 заказов каждого клиента по сумме;
• последних 5 событий каждого пользователя;
• лучшего платежа каждого дня — там можно WHERE rn = 1.

ROW_NUMBER vs RANK для top-N — какой брать?

Когда дубликатов быть не может (например, ORDER BY по id или timestamp) — всё равно какой брать, результат одинаков.

Когда дубликаты возможны (например, ORDER BY по amount_cents, и у двух заказов одна сумма), выбор семантический:

• ROW_NUMBER: «Дай мне строго 3 строки, даже если 4-я равна 3-й». Произвольный tie-break.
• RANK или DENSE_RANK: «Дай мне все строки топ-3, даже если их получится 5». Честный «олимпийский» подход.

В большинстве отчётов аналитики берут ROW_NUMBER — это даёт детерминированный размер выдачи. В рекомендательных системах и при справедливом отборе чаще берут RANK.

Нюанс: ORDER BY без PARTITION BY

Если в окне есть только ORDER BY (без PARTITION BY), то вся таблица — одна партиция. ROW_NUMBER пронумерует строки от 1 до N по всему результату.

Чек-лист

• ORDER BY внутри OVER — для вычисления окна; внешний ORDER BY — для вывода. Это два разных места в запросе.
• ROW_NUMBER всегда уникален; RANK повторяется на дублях с пропусками; DENSE_RANK повторяется без пропусков.
• NTILE(N) разбивает на N равных бакетов — для квартилей, ABC-анализа.
• Top-N per group: ROW_NUMBER в PARTITION BY + фильтр в обёрточном запросе.
• На дубликатах в ORDER BY: ROW_NUMBER для фиксированного размера выдачи, RANK — для честного отбора всех равноценных.
• NULLS LAST при ORDER BY DESC обычно нужен явно — иначе NULL окажется на вершине ранга.