Функции PERCENT_RANK() и CUME_DIST() в SQL: относительный ранг и накопленное распределение

PERCENT_RANK() и CUME_DIST() — оконные функции для относительного положения строки в отсортированном наборе, результат от 0 до 1. PERCENT_RANK считается как (ранг − 1) / (число строк − 1) и для первой строки всегда равна 0. CUME_DIST — доля строк со значением не больше текущего, поэтому она никогда не равна 0. Обе удобны, чтобы найти топ-10% пользователей или к какому перцентилю относится строка. Работают в PostgreSQL, MySQL 8+ и SQLite 3.25+.

Синтаксис

PERCENT_RANK() OVER (PARTITION BY ... ORDER BY ...) / CUME_DIST() OVER (... ORDER BY ...)

Примеры использования

Пример 1: Относительный ранг пользователей по выручке

SELECT
  user_id,
  revenue,
  PERCENT_RANK() OVER (ORDER BY revenue) AS pct_rank
FROM users;

Результат:

user_id | revenue | pct_rank
--------+---------+---------
3       |   500   | 0.00
7       |  1200   | 0.50
9       |  4800   | 1.00

Пример 2: PERCENT_RANK и CUME_DIST вместе

SELECT
  user_id,
  revenue,
  PERCENT_RANK() OVER (ORDER BY revenue) AS pct_rank,
  CUME_DIST()   OVER (ORDER BY revenue) AS cume_dist
FROM users;

Результат:

user_id | revenue | pct_rank | cume_dist
--------+---------+----------+----------
3       |   500   | 0.00     | 0.33
7       |  1200   | 0.50     | 0.67
9       |  4800   | 1.00     | 1.00

Пример 3: Топ-10% клиентов по выручке

SELECT user_id, revenue
FROM (
  SELECT
    user_id,
    revenue,
    CUME_DIST() OVER (ORDER BY revenue DESC) AS cd
  FROM users
) t
WHERE cd <= 0.10
ORDER BY revenue DESC;

Результат:

user_id | revenue
--------+--------
9       | 4800
15      | 4200
Порешать SQL-задачи в тренажёре →

Типичная ошибка

Не путай PERCENT_RANK (положение строки) с PERCENTILE_CONT (значение перцентиля) — это обратные друг другу вещи. PERCENT_RANK для первой строки всегда 0, а CUME_DIST — никогда, поэтому для «верхних N%» пороги немного разойдутся. Одинаковые значения (ties) получают одинаковый ранг.

Где спрашивают на собеседовании

Спрашивают, как выделить топ-10% клиентов по выручке или отнести пользователя к перцентилю. Идут в паре с NTILE (для бакетов) и RANK/DENSE_RANK; важно уметь объяснить разницу с PERCENTILE_CONT.

FAQ: частые вопросы про PERCENT_RANK / CUME_DIST

Чем PERCENT_RANK() отличается от CUME_DIST()?

PERCENT_RANK = (ранг − 1) / (число строк − 1) и для первой строки равна 0. CUME_DIST = доля строк со значением ≤ текущего и всегда больше 0. Первая измеряет положение по рангу, вторая — накопленную долю распределения.

Чем PERCENT_RANK() отличается от PERCENTILE_CONT()?

Это обратные операции: PERCENT_RANK по значению даёт его относительный ранг (0..1), а PERCENTILE_CONT по доле (например 0.9) возвращает само значение перцентиля. Первая — оконная функция, вторая — агрегат с WITHIN GROUP.

Как выбрать топ-10% строк через эти функции?

Отсортируйте по убыванию и возьмите CUME_DIST() OVER (ORDER BY revenue DESC) <= 0.10 в подзапросе. Можно использовать и PERCENT_RANK(), но помните, что у неё пороги немного другие из-за деления на (n − 1).

Задачи в тренажёре по теме

Вопросы с собеседований по теме

Смежные темы SQL

Оконные функции · Ранжирование

Другие функции SQL

ROW_NUMBER · RANK · DENSE_RANK · LAG · LEAD · NTILE

Открыть SQL-тренажёр (565 задач) →