Java Interview Questions

Главная / Все ответы / Базы данных SQL PostgreSQL

Вопрос 16 · Раздел 1

Что делает RANK() и DENSE_RANK()?

4. PERCENT_RANK() для перцентилей (топ 5%) 5. NULLS LAST/FIRST уточняйте явно 6. Индекс по (PARTITION BY, ORDER BY) → без Sort 7. Детерминизм: добавляйте уникальный столбец в OR...

Russian Базы данных SQL PostgreSQL Исходный Markdown
Версии по языкам: English Russian Ukrainian

🟢 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 как альтернатива группировке