🟢 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 ОК:

• Довільні сторінки (перейти на сторінку 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