🟢 Junior Level

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

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

Пример:

-- Пронумеровать все заказы по дате
SELECT 
    id,
    amount,
    created_at,
    ROW_NUMBER() OVER(ORDER BY created_at DESC) as row_num
FROM orders;

-- Результат:
-- id | amount | created_at | row_num
-- 5  | 100    | 2024-04-01 | 1
-- 4  | 200    | 2024-03-15 | 2
-- 3  | 150    | 2024-03-01 | 3

Частые применения:

• Нумерация строк в отчётах
• Пагинация
• Поиск первой/последней записи в группе
• Удаление дубликатов

---

🟡 Middle Level

Синтаксис

ROW_NUMBER() OVER(
    PARTITION BY column1  -- Разделение на группы (опционально)
    ORDER BY column2      -- Сортировка внутри группы (обязательно!)
)

Основные паттерны

1. Топ-N для каждой группы:

-- Последние 3 заказа для каждого пользователя
SELECT * FROM (
    SELECT 
        user_id,
        amount,
        created_at,
        ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at DESC) as rn
    FROM orders
) sub
WHERE rn <= 3;

2. Дедупликация:

-- Оставить только последнюю версию записи
WITH ranked AS (
    SELECT id, email, created_at,
           ROW_NUMBER() OVER(PARTITION BY email ORDER BY created_at DESC) as rn
    FROM users
)
DELETE FROM users WHERE id IN (SELECT id FROM ranked WHERE rn > 1);

3. Пагинация:

-- Страница 3 по 20 записей
SELECT * FROM (
    SELECT id, name,
           ROW_NUMBER() OVER(ORDER BY id) as rn
    FROM users
) sub
WHERE rn BETWEEN 41 AND 60;

Недетерминизм — важная ловушка!

-- ❌ ПРОБЛЕМА: если есть пользователи с одинаковым created_at
SELECT id, name, created_at,
       ROW_NUMBER() OVER(ORDER BY created_at DESC) as rn
FROM users;
-- Порядок строк с одинаковым created_at НЕ гарантирован!

-- ✅ РЕШЕНИЕ: добавьте уникальный столбец
SELECT id, name, created_at,
       ROW_NUMBER() OVER(ORDER BY created_at DESC, id DESC) as rn
FROM users;
-- id гарантирует уникальный порядок

ROW_NUMBER vs COUNT(*) OVER()

-- ROW_NUMBER() — дешёвая (просто инкрементирует счётчик)
SELECT id, name, ROW_NUMBER() OVER() as rn FROM users LIMIT 10;

-- COUNT(*) OVER() — дорогая (читает ВСЕ строки раздела)
SELECT id, name, COUNT(*) OVER() as total FROM users LIMIT 10;
-- ⚠️ Несмотря на LIMIT 10, база прочитает всех пользователей!

Типичные ошибки

1. Фильтрация в том же запросе

   -- ❌ ОШИБКА: WHERE не видит rn
   SELECT id, ROW_NUMBER() OVER(ORDER BY id) as rn
   FROM users
   WHERE rn <= 10;  -- Ошибка: column "rn" does not exist
      
   -- ✅ Подзапрос или CTE
   SELECT * FROM (
       SELECT id, ROW_NUMBER() OVER(ORDER BY id) as rn FROM users
   ) sub WHERE rn <= 10;
   

2. Забыли ORDER BY

   -- ❌ Синтаксическая ошибка
   ROW_NUMBER() OVER(PARTITION BY user_id)
      
   -- ✅ ORDER BY обязателен
   ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at)
   

Сравнение с аналогами

Функция	Уникальность	Обработка одинаковых значений
ROW_NUMBER()	✅ Всегда	Произвольно нумерует
RANK()	❌ Нет	Одинаковые = одинаковый ранг, потом пропуск
DENSE_RANK()	❌ Нет	Одинаковые = одинаковый ранг, без пропусков

Когда НЕ использовать ROW_NUMBER

1. Для агрегаций (COUNT, SUM, AVG) — используйте оконные агрегатные функции
2. Для ранжирования с пропусками — используйте RANK или DENSE_RANK
3. Для API-пагинации на больших таблицах (> 1 млн строк) — используйте Keyset Pagination (WHERE id > ?)

---

🔴 Senior Level

Физическая оптимизация: устранение Sort

Ключевой инсайт: Если данные уже отсортированы, PostgreSQL пропускает фазу Sort!

-- Индекс по (user_id, created_at DESC)
CREATE INDEX idx_orders_user_date ON orders(user_id, created_at DESC);

-- Запрос:
SELECT user_id, amount, created_at,
       ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at DESC) as rn
FROM orders;

-- Plan: WindowAgg → Index Scan (БЕЗ Sort!)
-- WindowAgg — узел плана выполнения PostgreSQL, который вычисляет оконную
-- функцию. Когда вы видите `WindowAgg → Index Scan` — данные читаются
-- уже в нужном порядке и фаза Sort пропущена.
-- → Данные читаются из индекса уже в нужном порядке
-- → В 10-50 раз быстрее на больших таблицах!

Мониторинг:

EXPLAIN SELECT ... ROW_NUMBER() OVER(...);
-- Ищите: WindowAgg → Index Scan → отлично!
-- Ищите: WindowAgg → Sort → можно оптимизировать индексом

Bounded Heap Sort (Top-N оптимизация)

Когда ROW_NUMBER() используется с LIMIT:

SELECT * FROM (
    SELECT id, amount,
           ROW_NUMBER() OVER(ORDER BY amount DESC) as rn
    FROM orders
) sub WHERE rn <= 10;

-- PostgreSQL применяет Bounded Heap Sort:
-- → Не сортирует ВСЮ таблицу
-- → Держит в памяти только топ-10 элементов (как если бы вы отобрали
--   топ-10 кандидатов, не сортируя все 1000 резюме)
-- → Вытесняет лишние по мере обработки
-- → Память: O(k) вместо O(n), где k = LIMIT

Incremental Sort (PG 13+)

-- Индекс по user_id
CREATE INDEX idx_orders_user ON orders(user_id);

-- Запрос с PARTITION BY
SELECT user_id, created_at,
       ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at DESC) as rn
FROM orders;

-- PG 13+: Incremental Sort
-- Данные уже сгруппированы по user_id (из индекса)
-- → Сортируем только по created_at внутри каждой группы
-- → Намного дешевле полной сортировки!
-- На PG 12 и ниже — полная сортировка всех данных, даже если часть уже отсортирована.

Keyset Pagination (лучше чем ROW_NUMBER)

-- ❌ ROW_NUMBER пагинация (медленно на больших OFFSET)
SELECT * FROM (
    SELECT id, name, ROW_NUMBER() OVER(ORDER BY id) as rn
    FROM users
) sub WHERE rn BETWEEN 100001 AND 100020;

-- ✅ Keyset Pagination (быстро, использует индекс)
SELECT id, name FROM users
WHERE id > 100000  -- Последнее id с предыдущей страницы
ORDER BY id
LIMIT 20;
-- → Index Scan, O(log n + k) вместо O(n)
-- ROW_NUMBER требует пронумеровать ВСЕ строки до нужного OFFSET,
-- а Keyset Pagination сразу прыгает к нужному месту через B-tree индекс.
-- Для страницы 50,000: ROW_NUMBER обработает 100,000 строк, Keyset — только 20.

Когда использовать Keyset:

• Бесконечная прокрутка (infinite scroll)
• API пагинация
• Большие таблицы (> 1 млн строк)

Когда ROW_NUMBER OK:

• Произвольные страницы (перейти на страницу 57)
• Маленькие таблицы (< 100,000 строк)
• Отчёты с фиксированной нумерацией

Детерминизм и стабильность

-- Всегда делайте ORDER BY детерминированным!
-- Плохо:
ROW_NUMBER() OVER(ORDER BY created_at)

-- Хорошо (добавьте PK):
ROW_NUMBER() OVER(ORDER BY created_at DESC, id DESC)

-- Почему?
-- Если 2 записи имеют одинаковый created_at
-- → Порядок между ними не определён
-- → Результат может меняться от запуска к запуску
-- → Баги в production!

Edge Cases

1. PARTITION BY с одним элементом

   -- Если в группе 1 строка → rn = 1 всегда
   SELECT user_id, ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY id) as rn
   FROM orders;
   -- Для пользователей с 1 заказом: rn = 1
   -- Для пользователей с N заказами: rn = 1, 2, ..., N
   

2. NULL в ORDER BY

   -- NULL по умолчанию LAST в DESC
   SELECT id, priority,
          ROW_NUMBER() OVER(ORDER BY priority DESC) as rn
   FROM tasks;
      
   -- Явное управление NULL
   SELECT id, priority,
          ROW_NUMBER() OVER(ORDER BY priority DESC NULLS LAST) as rn
   FROM tasks;
   

3. ROW_NUMBER() без PARTITION BY

   -- Нумерация ВСЕХ строк
   SELECT id, name, ROW_NUMBER() OVER(ORDER BY id) as rn
   FROM users;
   -- rn = 1, 2, 3, ..., N
   

Performance Tuning

Память для Sort:

-- Если Sort не влезает в work_mem → spill на диск
EXPLAIN (ANALYZE) SELECT ... ROW_NUMBER() OVER(ORDER BY ...);
-- "external merge Disk: X kB" → увеличьте work_mem

-- Временное увеличение для запроса
SET work_mem = '256MB';
-- ... запрос ...
RESET work_mem;

Параллелизм:

-- WindowAgg НЕ параллелится в PG < 14
-- PG 14+: может использовать Parallel Gather перед WindowAgg
-- Но сам WindowAgg выполняется в одном процессе

-- Обходной путь для больших данных:
-- Партицирование таблицы + UNION ALL

Production Experience

Реальный сценарий #1: Дедупликация 10 млн записей

• CRM: дубликаты контактов (same email)
• Запрос с ROW_NUMBER + DELETE: 3 минуты
• Решение:
  • Индекс по (email, created_at DESC)
  • Batch DELETE по 10,000 записей
• Результат: 15 секунд

Реальный сценарий #2: Пагинация API

• E-commerce API: ROW_NUMBER пагинация на 10 млн товаров
• Страница 50,000: 8 секунд
• Решение: Keyset Pagination (WHERE id > ?)
• Результат: 50ms (ускорение в 160 раз)

Monitoring

-- 1. Проверка плана
EXPLAIN (ANALYZE, BUFFERS)
SELECT ... ROW_NUMBER() OVER(...);
-- WindowAgg → Sort → можно оптимизировать индексом
-- WindowAgg → Index Scan → отлично!

-- 2. Проверка Sort памяти
EXPLAIN (ANALYZE)
SELECT ... ROW_NUMBER() OVER(ORDER BY ...);
-- "Disk: X kB" → spill! Увеличьте work_mem

-- 3. Поиск запросов с ROW_NUMBER
SELECT query, mean_exec_time
FROM pg_stat_statements
WHERE query LIKE '%ROW_NUMBER()%'
ORDER BY mean_exec_time DESC;

Best Practices

1. Всегда добавляйте PK в ORDER BY для детерминизма
2. Индекс по (PARTITION BY, ORDER BY) → без Sort
3. Keyset Pagination для больших таблиц
4. ROW_NUMBER только для отчётов/пагинации по номеру страницы
5. Bounded Heap Sort автоматически при LIMIT
6. Incremental Sort (PG 13+) с индексами
7. Подзапрос/CTE для фильтрации по rn

Резюме для Senior

• ROW_NUMBER() — уникальная нумерация, всегда детерминирована с PK
• Индекс по (PARTITION BY, ORDER BY) → WindowAgg без Sort
• Bounded Heap Sort при LIMIT → O(k) памяти
• Keyset Pagination > ROW_NUMBER для API и infinite scroll
• Incremental Sort (PG 13+) экономит при partial сортировке
• Детерминизм: всегда добавляйте PK в ORDER BY
• COUNT(*) OVER() дорогое — не используйте для пагинации
• Фильтрация только в подзапросе/CTE

---

🎯 Шпаргалка для интервью

Обязательно знать:

• ROW_NUMBER() — уникальная нумерация каждой строки (1, 2, 3, …)
• PARTITION BY — разделение на группы, ORDER BY — сортировка внутри (обязателен!)
• Детерминизм: всегда добавляйте PK в ORDER BY (ORDER BY created_at DESC, id DESC)
• Фильтрация по rn → ТОЛЬКО в подзапросе/CTE (окна считаются ПОСЛЕ WHERE)
• Bounded Heap Sort: при LIMIT — O(k) памяти вместо O(n)
• Keyset Pagination (WHERE id > last_id) > ROW_NUMBER для API/infinite scroll
• Индекс по (PARTITION BY, ORDER BY) → WindowAgg без Sort
• Incremental Sort (PG 13+): данные частично отсортированы → быстрее
• COUNT(*) OVER() дорогое — читает ВСЕ строки раздела, не используйте с LIMIT
• Дедупликация: ROW_NUMBER + DELETE WHERE rn > 1

Частые уточняющие вопросы:

• «Почему ROW_NUMBER пагинация медленная на больших OFFSET?» → Sort всех строк до OFFSET
• «Что такое Keyset Pagination?» → WHERE id > last_id ORDER BY id LIMIT 20 → Index Scan
• «Почему нужен PK в ORDER BY?» → Без PK порядок при одинаковых значениях не определён
• «Когда COUNT(*) OVER() проблема?» → Несмотря на LIMIT, читает ВСЕ строки

Красные флаги (НЕ говорить):

• ❌ «ROW_NUMBER можно фильтровать в WHERE» (нет, после WHERE считаются)
• ❌ «Keyset Pagination — это то же самое, что ROW_NUMBER» (Keyset = Index Scan, ROW_NUMBER = Sort)
• ❌ COUNT(*) OVER() OK с LIMIT (читает ВСЕ строки!)

Связанные темы:

• [[Что делает RANK() и DENSE_RANK()]] → отличия от ROW_NUMBER
• [[Что такое оконные функции]] → общий обзор
• [[Что такое explain plan]] → проверка плана WindowAgg → Sort/Index Scan