🟢 Junior Level

RANK() і DENSE_RANK() — віконні функції, які присвоюють ранг (місце) рядкам на основі їхніх значень, однакові значення отримують однаковий ранг.

Проста аналогія: Спортивні змагання:

• RANK() — олімпійська система: два золотих → наступний бронзовий (пропуск місця)
• DENSE_RANK() — система грейдів: два золотих → наступний срібний (без пропусків)

Приклад:

-- Зарплати: 100, 100, 80, 60

SELECT name, salary,
       RANK() OVER(ORDER BY salary DESC) as rank,
       DENSE_RANK() OVER(ORDER BY salary DESC) as dense_rank
FROM employees;

-- Результат:
-- name   | salary | rank | dense_rank
-- Іван   | 100    | 1    | 1        ← однакові зарплати
-- Петро  | 100    | 1    | 1        ← однаковий ранг
-- Анна   | 80     | 3    | 2        ← RANK пропускає 2
-- Сергій | 60     | 4    | 3

Коли використовувати:

• RANK() — коли важлива “дистанція” (скільки людей перед вами)
• DENSE_RANK() — коли важливий “рівень” без пропусків
• ROW_NUMBER() — коли потрібна унікальна нумерація

---

🟡 Middle Level

Порівняння ранжуючих функцій

-- Значення: 100, 100, 100, 80, 60

SELECT value,
       ROW_NUMBER() OVER(ORDER BY value DESC) as row_num,
       RANK() OVER(ORDER BY value DESC) as rank,
       DENSE_RANK() OVER(ORDER BY value DESC) as dense_rank
FROM data;

-- Результат:
-- value | row_num | rank | dense_rank
-- 100   | 1       | 1    | 1
-- 100   | 2       | 1    | 1        ← всі три 100 = ранг 1
-- 100   | 3       | 1    | 1
-- 80    | 4       | 4    | 2        ← RANK: 4, DENSE: 2
-- 60    | 5       | 5    | 3

Функція	Однакові значення	Пропуски	Унікальність
ROW_NUMBER()	Різні номери	Ні	✅ Завжди
RANK()	Однаковий ранг	✅ Так	❌ Ні
DENSE_RANK()	Однаковий ранг	❌ Ні	❌ Ні

Коли що обирати

RANK() — коли важливі “чесні місця”:

-- Топ-10 гравців (поверне всіх з 10-м місцем)
SELECT * FROM (
    SELECT name, score, RANK() OVER(ORDER BY score DESC) as r
    FROM players
) t WHERE r <= 10;
-- Якщо на 10 місці 3 гравці → поверне 12 рядків (чесно!)

DENSE_RANK() — коли важливі “рівні”:

-- Рівні зарплат без пропусків
SELECT name, salary,
       DENSE_RANK() OVER(ORDER BY salary DESC) as salary_level
FROM employees;

-- Рівень 1, 2, 3... без пропусків
-- Зручно для звітів: "Скільки рівнів зарплат існує?"
SELECT COUNT(DISTINCT salary_level) FROM (...);

Аналітичні функції ранжування

PERCENT_RANK() — відносний ранг (0.0 to 1.0):

SELECT name, salary,
       ROUND(PERCENT_RANK() OVER(ORDER BY salary DESC)::numeric, 2) as pct
FROM employees;

-- pct = 0.00 → топ (найкращий)
-- pct = 1.00 → найгірший
-- pct = 0.50 → медіана
-- Формула: `(rank - 1) / (total_rows - 1)`
-- Перша: (1-1)/(N-1) = 0.00. Остання: (N-1)/(N-1) = 1.00

NTILE(n) — поділ на групи:

-- Поділ на 4 квартилі (25% у кожній)
SELECT name, revenue,
       NTILE(4) OVER(ORDER BY revenue DESC) as quartile
FROM salespeople;

-- quartile = 1 → топ 25%
-- quartile = 4 → нижні 25%

CUME_DIST() — кумулятивний розподіл:

-- Який відсоток співробітників заробляє менше?
SELECT name, salary,
       ROUND(CUME_DIST() OVER(ORDER BY salary)::numeric, 2) as pct_below
FROM employees;

Обробка NULL

-- NULL вважаються "найбільшими" за замовчуванням
SELECT name, salary,
       RANK() OVER(ORDER BY salary DESC) as rank
FROM employees;

-- NULL отримають rank = 1!
-- Рішення: NULLS LAST
SELECT name, salary,
       RANK() OVER(ORDER BY salary DESC NULLS LAST) as rank
FROM employees;

Типові помилки

1. Забули NULLS LAST

   -- ❌ NULL будуть на першому місці при DESC
   RANK() OVER(ORDER BY score DESC)
   
   -- ✅ NULL в кінці
   RANK() OVER(ORDER BY score DESC NULLS LAST)
   

2. LIMIT замість RANK для Top-N

   -- ⚠️ LIMIT може відсікти рівних (безпечний, коли значення UNIQUE
   -- або ties не важливі, наприклад «показати 10 будь-яких для прев'ю»)
   SELECT * FROM players ORDER BY score DESC LIMIT 10;
   -- Якщо 10-й та 11-й мають однаковий score → один відсічений випадково
   
   -- ✅ RANK поверне всіх рівних
   SELECT * FROM (
       SELECT *, RANK() OVER(ORDER BY score DESC) as r
       FROM players
   ) t WHERE r <= 10;
   

3. Недетермінованість

   -- ❌ Однакові score → порядок не гарантований
   RANK() OVER(ORDER BY score DESC)
   
   -- ✅ Додайте унікальний стовпець
   RANK() OVER(ORDER BY score DESC, id DESC)
   

Коли НЕ використовувати RANK/DENSE_RANK

1. Коли потрібна унікальна нумерація — ROW_NUMBER (у 2x швидше)
2. Коли ties не важливі — простого LIMIT достатньо
3. При сотнях мільйонів рядків і важлива швидкість — ROW_NUMBER швидше

---

🔴 Senior Level

Фізика виконання та продуктивність

Вартість функцій:

ROW_NUMBER():   O(n) — просто інкрементує лічильник
RANK():         O(n log n) — порівняння з попереднім рядком
                (Peer check: якщо значення рівні — «peer» = «рівний» —
                RANK присвоює той самий ранг)
DENSE_RANK():   O(n log n) — peer check + відстеження УНІКАЛЬНИХ значень

DENSE_RANK дорожчий за ROW_NUMBER:

• Потрібно порівнювати поточне значення з попереднім
• При зміні значення → інкрементувати ранг
• На 100 млн рядків: ROW_NUMBER у 2x швидший

Індекси та усунення Sort

-- Індекс за (partition_col, order_col DESC)
CREATE INDEX idx_players_score ON players(score DESC, id DESC);

-- Запит без Sort
SELECT name, score,
       RANK() OVER(ORDER BY score DESC, id DESC) as rank
FROM players;

-- Plan: WindowAgg → Index Scan (БЕЗ Sort!)
-- → У 10-50 разів швидше на великих таблицях

Top-N з RANK: чесна вибірка

Сценарій: Конкурс з призовими місцями

-- ❌ LIMIT несправедливий при однакових балах
SELECT * FROM contest ORDER BY score DESC LIMIT 10;
-- Якщо 10-й та 11-й набрали однаково → один не отримає приз

-- ✅ RANK гарантує справедливість
SELECT * FROM (
    SELECT name, score,
           RANK() OVER(ORDER BY score DESC) as rank
    FROM contest
) t WHERE rank <= 10;
-- Поверне ВСІХ з рангом <= 10, навіть якщо їх 15

NTILE для сегментації

-- Поділ клієнтів на 10 груп за доходом (децилі)
SELECT
    customer_id,
    total_spent,
    NTILE(10) OVER(ORDER BY total_spent DESC) as decile
FROM customers;

-- decile = 1 → топ 10% клієнтів
-- decile = 10 → нижні 10%

-- Аналіз по квартилях
SELECT
    quartile,
    COUNT(*) as customers,
    AVG(total_spent) as avg_spent,
    MIN(total_spent) as min_spent,
    MAX(total_spent) as max_spent
FROM (
    SELECT customer_id, total_spent,
           NTILE(4) OVER(ORDER BY total_spent DESC) as quartile
    FROM customers
) t
GROUP BY quartile
ORDER BY quartile;

PERCENT_RANK для перцентилів

-- Знайти співробітників у топ-5% за зарплатою
SELECT * FROM (
    SELECT name, salary,
           PERCENT_RANK() OVER(ORDER BY salary DESC) as pct
    FROM employees
) t
WHERE pct <= 0.05;

Edge Cases

1. Порожній результат

   -- Якщо таблиця порожня → 0 рядків
   SELECT name, RANK() OVER(ORDER BY salary DESC) FROM empty_table;
   -- Result: 0 rows
   

2. PARTITION BY з RANK

   -- Ранг всередині кожної групи
   SELECT department, name, salary,
          RANK() OVER(PARTITION BY department ORDER BY salary DESC) as dept_rank
   FROM employees;
   
   -- В кожному відділі свій ранг 1, 2, 3...
   

3. RANK з кількома ORDER BY

   -- Ранжування за кількома колонками
   SELECT name, score, time,
          RANK() OVER(ORDER BY score DESC, time ASC) as rank
   FROM competition;
   
   -- Спочатку за score (DESC), потім за time (ASC)
   

Performance Tuning

Пам’ять для Sort:

-- RANK/DENSE_RANK потребують сортування
-- Якщо Sort не влізає у work_mem → spill на диск
EXPLAIN (ANALYZE) SELECT ... RANK() OVER(ORDER BY ...);
-- "Disk: X kB" → збільште work_mem

Паралелізм:

-- WindowAgg НЕ паралелиться (до PG 14)
-- PG 14+: Parallel Gather перед WindowAgg
-- Сам WindowAgg — в одному процесі

-- Для дуже великих таблиць:
-- Партиціювання + UNION ALL

Production Experience

Реальний сценарій #1: Рейтинг продавців

• E-commerce: щомісячний рейтинг 50,000 продавців
• Запит: 10 секунд (Sort на диску)
• Рішення:
  • Індекс за (month, revenue DESC)
  • work_mem = 256MB
• Результат: 500ms

Реальний сценарій #2: NTILE для AB-тесту

• Маркетинг: поділ користувачів на 4 групи за активністю
• Запит з NTILE(4): 2 секунди
• Використання: таргетовані кампанії по квартилях
• Результат: конверсія +15% завдяки точній сегментації

Monitoring

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

-- 2. Перевірка spill
EXPLAIN (ANALYZE)
SELECT ... RANK() OVER(ORDER BY ...);
-- "Disk: X kB" → spill! Збільште work_mem

-- 3. Пошук запитів з ранжуванням
SELECT query, mean_exec_time
FROM pg_stat_statements
WHERE query LIKE '%RANK()%' OR query LIKE '%DENSE_RANK()%'
ORDER BY mean_exec_time DESC;

Best Practices

1. RANK() для чесних Top-N (всі рівні потрапляють)
2. DENSE_RANK() для рівнів/грейдів без пропусків
3. NTILE(n) для сегментації на рівні групи
4. PERCENT_RANK() для перцентилів (топ 5%)
5. NULLS LAST/FIRST уточнюйте явно
6. Індекс за (PARTITION BY, ORDER BY) → без Sort
7. Детермінізм: додавайте унікальний стовпець в ORDER BY
8. LIMIT vs RANK: RANK для чесної вибірки, LIMIT для фіксованого числа

Резюме для Senior

• RANK() = олімпійська система (з пропусками)
• DENSE_RANK() = система грейдів (без пропусків)
• ROW_NUMBER() = унікальна нумерація (довільно при рівних)
• NTILE(n) = поділ на n рівних груп
• PERCENT_RANK() = відносний ранг (0.0-1.0)
• RANK > LIMIT для чесних Top-N при однакових значеннях
• Індекс за (PARTITION BY, ORDER BY) → WindowAgg без Sort
• NULLS LAST/FIRST для коректного ранжування з NULL

---

🎯 Шпаргалка для інтерв’ю

Обов’язково знати:

• RANK(): однакові значення = однаковий ранг, наступний ранг з пропуском (1, 1, 3)
• DENSE_RANK(): однакові значення = однаковий ранг, без пропусків (1, 1, 2)
• ROW_NUMBER(): завжди унікальна нумерація (1, 2, 3) — довільно при рівних
• NTILE(n): ділить на n рівних груп (квартилі, децилі)
• PERCENT_RANK(): відносний ранг 0.0-1.0, 0.0 = топ
• CUME_DIST(): кумулятивний розподіл (який відсоток нижче)
• RANK > LIMIT для «чесних» Top-N (поверне ВСІХ з рівними значеннями)
• NULLS LAST/FIRST: NULL за замовчуванням FIRST в ASC, LAST в DESC
• Індекс за (PARTITION BY, ORDER BY) → WindowAgg без Sort
• RANK/DENSE_RANK дорожчі за ROW_NUMBER (peer check → O(n log n))

Часті уточнюючі запитання:

• «Коли RANK кращий за LIMIT?» → При однакових значеннях (конкурс, рейтинг)
• «Чому потрібен NULLS LAST?» → Без нього NULL отримають rank = 1 при DESC
• «Чим NTILE корисніший за GROUP BY?» → Ділить на рівні групи для сегментації
• «Чому RANK дорожчий за ROW_NUMBER?» → Peer check + відстеження унікальних значень

Червоні прапорці (НЕ говорити):

• ❌ «RANK і ROW_NUMBER — одне й те саме» (RANK з пропусками, ROW_NUMBER унікальна)
• ❌ «LIMIT для Top-N завжди чесний» (відсікає рівних випадково)
• ❌ «NULL не впливають на ранжування» (NULL за замовчуванням FIRST у DESC!)

Пов’язані теми:

• [[Що робить ROW_NUMBER()]] → відмінності від RANK
• [[Що таке віконні функції]] → загальний огляд
• [[Що робить GROUP BY]] → NTILE як альтернатива групуванню