PERCENT_RANK and CUME_DIST: Leaderboard Percentiles in SQL

В SQL часто нужно не просто отсортировать строки, а понять относительную позицию каждой строки внутри списка.

Например:

• клиент входит в топ-10% по сумме покупок или нет;
• сотрудник находится в верхней части зарплатного распределения или в нижней;
• игрок в лидерборде лучше какой доли участников;
• заказ относится к дорогому хвосту или к обычным значениям;
• пользователь по активности ближе к лидерам или к концу списка.

Можно показать обычный ранг:

RANK() OVER (ORDER BY spent DESC)

Но ранг вроде 17 сам по себе не всегда понятен.

«17-й из 20» и «17-й из 100000» — это совершенно разные ситуации.

Поэтому в SQL есть оконные функции:

PERCENT_RANK()
CUME_DIST()

Они показывают позицию строки не как обычное место, а как число от 0 до 1.

Такую позицию проще интерпретировать как долю или процент.

Что такое PERCENT_RANK простыми словами

PERCENT_RANK показывает, где строка находится на шкале от первой строки до последней.

Результат всегда находится в диапазоне от 0 до 1.

Например:

0    -- самая верхняя строка в сортировке
0.25 -- примерно первая четверть пути
0.5  -- середина шкалы
0.75 -- ближе к концу
1    -- самая нижняя строка

Если мы сортируем клиентов по сумме покупок от большей к меньшей:

ORDER BY spent DESC

то PERCENT_RANK = 0 получит клиент с максимальными тратами.

А PERCENT_RANK = 1 получит клиент в самом низу списка, если в окне больше одной строки.

То есть PERCENT_RANK отвечает на вопрос:

Где эта строка находится между лучшей и худшей строкой в отсортированном списке?

Что такое CUME_DIST простыми словами

CUME_DIST означает cumulative distribution, то есть накопленная доля распределения.

Он показывает, какая доля строк находится до текущей строки включительно в заданном порядке сортировки.

Например, если мы сортируем клиентов по тратам по убыванию:

ORDER BY spent DESC

и у клиента:

CUME_DIST = 0.1

это значит:

10% строк находятся на уровне этого клиента или выше в отсортированном списке.

То есть при сортировке DESC по тратам это можно читать так:

этот клиент входит примерно в верхние 10% по тратам.

Если:

CUME_DIST = 0.8

значит до этой строки включительно находится 80% списка. Такой клиент уже ближе к нижней части распределения.

Главное отличие PERCENT_RANK и CUME_DIST

Обе функции возвращают число от 0 до 1, но отвечают на разные вопросы.

PERCENT_RANK:

Насколько далеко строка находится от первой строки к последней?

CUME_DIST:

Какая доля строк находится до текущей строки включительно?

Примерно так:

PERCENT_RANK -- позиция на шкале от 0 до 1
CUME_DIST    -- накопленная доля строк

У верхней строки:

• PERCENT_RANK всегда будет 0;
• CUME_DIST обычно будет больше 0, потому что сама верхняя строка уже входит в накопленную долю.

У нижней строки:

• CUME_DIST всегда будет 1;
• PERCENT_RANK будет 1, если строк в окне больше одной.

Базовый пример

Допустим, есть клиенты и сумма их покупок:

name | spent
-----+------
Anna | 10000
Bob  | 8000
Kate | 6000
Tom  | 4000
Max  | 2000

Посчитаем PERCENT_RANK и CUME_DIST.

SELECT
  name,
  spent,
  PERCENT_RANK() OVER (ORDER BY spent DESC) AS percent_rank,
  CUME_DIST() OVER (ORDER BY spent DESC) AS cume_dist
FROM customers
ORDER BY spent DESC;

Результат:

name | spent | percent_rank | cume_dist
-----+-------+--------------+----------
Anna | 10000 | 0            | 0.2
Bob  | 8000  | 0.25         | 0.4
Kate | 6000  | 0.5          | 0.6
Tom  | 4000  | 0.75         | 0.8
Max  | 2000  | 1            | 1

Почему у Anna CUME_DIST = 0.2?

Потому что всего 5 строк. Первая строка — это уже 1 из 5.

1 / 5 = 0.2

А PERCENT_RANK у Anna равен 0, потому что она первая в списке.

Формула PERCENT_RANK

PERCENT_RANK считается так:

(rank - 1) / (rows - 1)

Где:

• rank — это результат RANK() для текущей строки;
• rows — количество строк в окне.

Для примера с 5 клиентами:

name | rank | formula       | percent_rank
-----+------+---------------+-------------
Anna | 1    | (1 - 1)/(5-1) | 0
Bob  | 2    | (2 - 1)/(5-1) | 0.25
Kate | 3    | (3 - 1)/(5-1) | 0.5
Tom  | 4    | (4 - 1)/(5-1) | 0.75
Max  | 5    | (5 - 1)/(5-1) | 1

Отсюда важное свойство:

Первая строка всегда получает PERCENT_RANK = 0.

А если строк больше одной, последняя строка обычно получает PERCENT_RANK = 1.

Формула CUME_DIST

CUME_DIST считается иначе.

Он показывает долю строк, которые находятся до текущей строки включительно в порядке сортировки.

Упрощённо:

количество строк до текущей включительно / общее количество строк

Для 5 клиентов:

name | position | formula | cume_dist
-----+----------+---------+----------
Anna | 1        | 1 / 5   | 0.2
Bob  | 2        | 2 / 5   | 0.4
Kate | 3        | 3 / 5   | 0.6
Tom  | 4        | 4 / 5   | 0.8
Max  | 5        | 5 / 5   | 1

Отсюда важное свойство:

Последняя строка всегда получает CUME_DIST = 1.

А первая строка обычно не 0, потому что она сама уже занимает часть распределения.

Пример: топ клиентов по сумме покупок

Допустим, есть таблицы users и orders.

Хотим посчитать сумму оплаченных заказов каждого пользователя и понять его относительную позицию.

WITH user_spending AS (
  SELECT
    u.id,
    u.name,
    SUM(o.amount) AS total_spent
  FROM users u
  JOIN orders o ON o.user_id = u.id
  WHERE o.status = 'paid'
  GROUP BY u.id, u.name
)
SELECT
  id,
  name,
  total_spent,
  PERCENT_RANK() OVER (ORDER BY total_spent DESC) AS percent_rank,
  CUME_DIST() OVER (ORDER BY total_spent DESC) AS cume_dist
FROM user_spending
ORDER BY total_spent DESC;

Здесь:

ORDER BY total_spent DESC

означает, что сверху будут клиенты с самыми большими тратами.

PERCENT_RANK покажет позицию клиента на шкале от лидера до последнего.

CUME_DIST покажет, какая доля клиентов находится на этом уровне или выше.

Например:

name | total_spent | percent_rank | cume_dist
-----+-------------+--------------+----------
Anna | 10000       | 0            | 0.1
Bob  | 8000        | 0.11         | 0.2
Kate | 7000        | 0.22         | 0.3

Если у клиента cume_dist = 0.1, при сортировке DESC по сумме это можно читать как:

клиент находится в верхних 10% по тратам.

Как показать «лучше N% пользователей»

В интерфейсе число вроде 0.13 не всегда понятно.

Часто хочется показать фразу:

Вы лучше 87% пользователей

Если мы используем PERCENT_RANK, можно взять:

1 - percent_rank

и умножить на 100.

Пример для зарплат:

WITH ranked AS (
  SELECT
    id,
    name,
    dept,
    salary,
    PERCENT_RANK() OVER (ORDER BY salary ASC) AS pr
  FROM employees
)
SELECT
  name,
  dept,
  salary,
  ROUND((pr * 100)::numeric, 1) AS percentile,
  ROUND(((1 - pr) * 100)::numeric, 1) AS better_than_pct
FROM ranked
ORDER BY salary DESC;

Здесь сортировка:

ORDER BY salary ASC

означает, что маленькие зарплаты идут первыми, большие — ближе к концу.

Поэтому высокий pr означает более высокую зарплату относительно остальных.

Если вы сортируете наоборот:

ORDER BY salary DESC

то смысл шкалы меняется: лучшая строка получает 0.

Поэтому всегда внимательно читайте PERCENT_RANK вместе с направлением сортировки.

Пример: зарплатный перцентиль внутри отдела

Часто сравнивать всех со всеми неправильно.

Например, зарплаты инженеров, HR и sales могут жить в разных диапазонах. Поэтому полезно считать перцентиль внутри отдела.

Для этого используется PARTITION BY.

SELECT
  id,
  name,
  dept,
  salary,
  PERCENT_RANK() OVER (
    PARTITION BY dept
    ORDER BY salary ASC
  ) AS dept_salary_percent_rank,
  CUME_DIST() OVER (
    PARTITION BY dept
    ORDER BY salary ASC
  ) AS dept_salary_cume_dist
FROM employees
ORDER BY dept, salary;

Здесь:

PARTITION BY dept

означает:

считай отдельное распределение внутри каждого отдела.

Сотрудник из eng сравнивается только с сотрудниками eng.

Сотрудник из hr — только с hr.

В каждом отделе шкала снова идёт от 0 до 1.

Это удобно, когда группы имеют разный размер и разные диапазоны значений.

PARTITION BY: независимые распределения

PARTITION BY разбивает данные на независимые окна.

Например:

PERCENT_RANK() OVER (
  PARTITION BY country
  ORDER BY total_spent DESC
)

означает:

внутри каждой страны отдельно отсортируй пользователей по тратам и посчитай их позицию.

Это важно для аналитики.

Если смешать все страны в один список, пользователи из рынка с высокими средними чеками могут занять весь верх распределения.

А если считать внутри страны, мы получаем относительную позицию пользователя на своём рынке.

Пример:

WITH user_spending AS (
  SELECT
    u.id,
    u.email,
    u.country,
    SUM(o.amount) AS total_spent
  FROM users u
  JOIN orders o ON o.user_id = u.id
  WHERE o.status = 'paid'
  GROUP BY u.id, u.email, u.country
)
SELECT
  country,
  email,
  total_spent,
  CUME_DIST() OVER (
    PARTITION BY country
    ORDER BY total_spent DESC
  ) AS country_cume_dist
FROM user_spending
ORDER BY country, country_cume_dist;

Если country_cume_dist <= 0.1, пользователь находится в верхних 10% внутри своей страны.

Ничьи: одинаковые значения в ORDER BY

В реальных данных часто бывают одинаковые значения.

Например, два клиента потратили одинаковую сумму:

name | spent
-----+------
Anna | 10000
Bob  | 8000
Kate | 8000
Tom  | 4000
Max  | 2000

Посчитаем RANK, PERCENT_RANK и CUME_DIST.

SELECT
  name,
  spent,
  RANK() OVER (ORDER BY spent DESC) AS rank,
  PERCENT_RANK() OVER (ORDER BY spent DESC) AS percent_rank,
  CUME_DIST() OVER (ORDER BY spent DESC) AS cume_dist
FROM customers
ORDER BY spent DESC;

Результат:

name | spent | rank | percent_rank | cume_dist
-----+-------+------+--------------+----------
Anna | 10000 | 1    | 0            | 0.2
Bob  | 8000  | 2    | 0.25         | 0.6
Kate | 8000  | 2    | 0.25         | 0.6
Tom  | 4000  | 4    | 0.75         | 0.8
Max  | 2000  | 5    | 1            | 1

Что важно:

• Bob и Kate имеют одинаковый spent;
• они получают одинаковый RANK;
• они получают одинаковый PERCENT_RANK;
• CUME_DIST для них тоже одинаковый и учитывает обе строки группы равных значений.

Почему CUME_DIST = 0.6?

Потому что до значения 8000 включительно находятся 3 строки из 5:

Anna, Bob, Kate

3 / 5 = 0.6

Важная ловушка с тай-брейкером

Часто говорят: «добавляйте уникальный тай-брейкер в ORDER BY, чтобы результат был стабильным».

Например:

ORDER BY spent DESC, id

Это действительно делает порядок строк стабильным.

Но есть важный нюанс.

Если вы добавите id в ORDER BY самого окна, то формальная ничья исчезнет.

Например:

PERCENT_RANK() OVER (ORDER BY spent DESC, id)

Теперь два клиента с одинаковым spent уже не равны для оконной сортировки, потому что один id меньше другого.

В результате они могут получить разные PERCENT_RANK.

Поэтому нужно решить, что важнее по бизнес-смыслу.

Если одинаковые значения должны делить одну позицию, пишите в окне только бизнес-сортировку:

PERCENT_RANK() OVER (ORDER BY spent DESC)

А стабильный вывод обеспечьте внешним ORDER BY:

ORDER BY spent DESC, id;

Если же вам нужно полностью детерминированное распределение без ничьих, тогда добавляйте тай-брейкер прямо в окно:

PERCENT_RANK() OVER (ORDER BY spent DESC, id)

Главное — понимать последствия.

PERCENT_RANK против CUME_DIST на ничьих

На ничьих разница между функциями особенно заметна.

Представим данные:

score
-----
100
90
90
80

Запрос:

SELECT
  score,
  RANK() OVER (ORDER BY score DESC) AS rnk,
  PERCENT_RANK() OVER (ORDER BY score DESC) AS pr,
  CUME_DIST() OVER (ORDER BY score DESC) AS cd
FROM scores
ORDER BY score DESC;

Результат:

score | rnk | pr                 | cd
------+-----+--------------------+-----
100   | 1   | 0                  | 0.25
90    | 2   | 0.3333333333333333 | 0.75
90    | 2   | 0.3333333333333333 | 0.75
80    | 4   | 1                  | 1

PERCENT_RANK для 90 использует rank = 2.

CUME_DIST для 90 смотрит, сколько строк находится до этой группы включительно: 100, 90, 90.

Это 3 строки из 4, значит 0.75.

Можно запомнить так:

PERCENT_RANK смотрит на начало группы равных значений.
CUME_DIST смотрит на конец группы равных значений.

Одна строка в окне

Есть крайний случай: в окне всего одна строка.

Например, в каком-то отделе один сотрудник.

Для PERCENT_RANK формула:

(rank - 1) / (rows - 1)

формально дала бы деление на ноль, потому что:

rows - 1 = 0

Но SQL определяет результат для одной строки как 0.

То есть:

PERCENT_RANK = 0

А CUME_DIST для одной строки будет:

1

Потому что одна строка из одной уже составляет всю накопленную долю.

CUME_DIST = 1 / 1 = 1

Это нормально, но важно помнить при маленьких группах.

Например, если в PARTITION BY dept есть отдел с одним сотрудником, его PERCENT_RANK будет 0, а CUME_DIST — 1.

NULL в ORDER BY

NULL участвует в сортировке.

Если в колонке, по которой вы считаете ранг, есть NULL, такие строки тоже получат PERCENT_RANK и CUME_DIST.

Например:

SELECT
  id,
  salary,
  PERCENT_RANK() OVER (ORDER BY salary DESC NULLS LAST) AS pr
FROM employees;

Здесь:

NULLS LAST

означает, что строки без зарплаты уйдут в конец.

Если написать:

ORDER BY salary DESC

поведение с NULL зависит от СУБД и направления сортировки. В PostgreSQL лучше явно указывать:

NULLS FIRST

или:

NULLS LAST

Чтобы не было сюрпризов.

Практическое правило:

Если NULL в сортируемой колонке возможен, явно укажите, где он должен быть: NULLS FIRST или NULLS LAST.

PERCENT_RANK и CUME_DIST против NTILE

PERCENT_RANK, CUME_DIST и NTILE похожи тем, что все они помогают понять положение строки в распределении.

Но они решают разные задачи.

NTILE(4) делит строки на 4 примерно равные группы.

NTILE(4) OVER (ORDER BY spent DESC)

Результат:

quartile
--------
1
1
2
2
3
3
4
4

А PERCENT_RANK и CUME_DIST возвращают дробную позицию от 0 до 1.

0
0.25
0.5
0.75
1

Когда использовать что?

NTILE        -- нужны группы: квартиль, дециль, топ-25%
PERCENT_RANK -- нужна позиция на шкале от лучшего к худшему
CUME_DIST    -- нужна накопленная доля строк

Например, если нужно назначить клиентов в 4 сегмента, удобно взять NTILE(4).

Если нужно показать «вы находитесь лучше 87% участников», чаще подойдёт PERCENT_RANK.

Если нужно выбрать верхние 10% по метрике, часто удобно использовать CUME_DIST <= 0.1 при сортировке по убыванию.

Пример: выбрать верхние 10% клиентов

Хотим найти клиентов, которые входят в верхние 10% по сумме покупок.

WITH user_spending AS (
  SELECT
    u.id,
    u.email,
    SUM(o.amount) AS total_spent
  FROM users u
  JOIN orders o ON o.user_id = u.id
  WHERE o.status = 'paid'
  GROUP BY u.id, u.email
),
ranked AS (
  SELECT
    id,
    email,
    total_spent,
    CUME_DIST() OVER (ORDER BY total_spent DESC) AS cd
  FROM user_spending
)
SELECT
  id,
  email,
  total_spent,
  cd
FROM ranked
WHERE cd <= 0.1
ORDER BY total_spent DESC;

Здесь:

ORDER BY total_spent DESC

ставит самых дорогих клиентов сверху.

А:

cd <= 0.1

оставляет строки, которые находятся в верхних 10% накопленного распределения.

Важно: из-за ничьих на границе результат может включить немного больше строк, чем ровно 10%.

Это нормально для CUME_DIST: он не разрывает группу равных значений.

Пример: нижний квартиль зарплат

Хотим найти сотрудников в нижних 25% по зарплате внутри каждого отдела.

WITH ranked AS (
  SELECT
    id,
    name,
    dept,
    salary,
    CUME_DIST() OVER (
      PARTITION BY dept
      ORDER BY salary ASC
    ) AS cd
  FROM employees
)
SELECT
  id,
  name,
  dept,
  salary,
  cd
FROM ranked
WHERE cd <= 0.25
ORDER BY dept, salary;

Почему сортировка ASC?

Потому что нижние зарплаты должны идти первыми.

Тогда CUME_DIST <= 0.25 означает нижнюю четверть распределения внутри отдела.

Если бы мы написали ORDER BY salary DESC, верхние зарплаты оказались бы первыми.

Пример: лидерборд игроков

Допустим, есть таблица players:

id | name | score
---+------+------
1  | Anna | 9800
2  | Bob  | 9200
3  | Kate | 9200
4  | Tom  | 7000
5  | Max  | 5000

Хотим показать игроку его относительное положение.

SELECT
  id,
  name,
  score,
  RANK() OVER (ORDER BY score DESC) AS rank,
  PERCENT_RANK() OVER (ORDER BY score DESC) AS percent_rank,
  CUME_DIST() OVER (ORDER BY score DESC) AS cume_dist
FROM players
ORDER BY score DESC, id;

Результат:

id | name | score | rank | percent_rank | cume_dist
---+------+-------+------+--------------+----------
1  | Anna | 9800  | 1    | 0            | 0.2
2  | Bob  | 9200  | 2    | 0.25         | 0.6
3  | Kate | 9200  | 2    | 0.25         | 0.6
4  | Tom  | 7000  | 4    | 0.75         | 0.8
5  | Max  | 5000  | 5    | 1            | 1

Bob и Kate делят место, потому что у них одинаковый score.

PERCENT_RANK у них одинаковый.

CUME_DIST тоже одинаковый и показывает, что до их уровня включительно находится 60% строк.

Не путайте перцентиль строки и значение перцентиля

Есть важное различие.

PERCENT_RANK и CUME_DIST отвечают на вопрос:

где находится текущая строка в распределении?

А PERCENTILE_CONT отвечает на другой вопрос:

какое значение находится на заданной доле распределения?

Например:

PERCENT_RANK() OVER (ORDER BY salary)

показывает относительную позицию каждого сотрудника.

А:

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary)

возвращает медианную зарплату.

То есть:

PERCENT_RANK / CUME_DIST -- позиция строки
PERCENTILE_CONT          -- значение на позиции

Это разные инструменты.

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

PERCENT_RANK хорошо подходит, когда нужна позиция на шкале от лучшего к худшему.

Например:

• показать положение игрока в лидерборде;
• показать, насколько клиент близок к верхушке списка;
• построить относительный ранг сотрудника внутри отдела;
• вывести «лучше N% участников»;
• сравнить позицию объекта внутри группы.

Он особенно удобен, когда важно, что первая строка имеет ровно 0, а последняя — 1.

Пример:

SELECT
  name,
  score,
  PERCENT_RANK() OVER (ORDER BY score DESC) AS pr
FROM players;

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

CUME_DIST хорошо подходит, когда нужна накопленная доля строк.

Например:

• выбрать верхние 10%;
• выбрать нижние 25%;
• понять, какая доля строк не лучше текущей;
• отфильтровать хвост распределения;
• построить порог на основе доли строк.

Пример верхних 10%:

WITH ranked AS (
  SELECT
    id,
    total_spent,
    CUME_DIST() OVER (ORDER BY total_spent DESC) AS cd
  FROM user_spending
)
SELECT *
FROM ranked
WHERE cd <= 0.1;

При сортировке по убыванию это выбирает строки в верхней части списка.

Производительность

PERCENT_RANK и CUME_DIST требуют сортировки внутри окна.

Например:

PERCENT_RANK() OVER (ORDER BY total_spent DESC)

Чтобы посчитать это, базе нужно упорядочить строки по total_spent.

Если есть PARTITION BY, сортировка выполняется внутри каждой партиции.

На небольших данных это обычно не проблема.

На больших отчётах стоит помнить:

• сортировка может быть дорогой;
• много больших партиций могут потребовать ресурсов;
• вычисления поверх агрегатов лучше делать в CTE или подзапросе;
• полезно проверять план через EXPLAIN ANALYZE;
• для регулярных BI-отчётов иногда лучше подготовить витрину.

Пример хорошей структуры:

WITH user_spending AS (
  SELECT
    u.id,
    u.country,
    SUM(o.amount) AS total_spent
  FROM users u
  JOIN orders o ON o.user_id = u.id
  WHERE o.status = 'paid'
  GROUP BY u.id, u.country
)
SELECT
  id,
  country,
  total_spent,
  CUME_DIST() OVER (
    PARTITION BY country
    ORDER BY total_spent DESC
  ) AS country_cd
FROM user_spending;

Сначала мы агрегируем данные до уровня пользователя, а уже потом считаем оконную функцию.

PERCENT_RANK и CUME_DIST в PostgreSQL

В PostgreSQL обе функции доступны как оконные функции.

Пример:

SELECT
  id,
  amount,
  PERCENT_RANK() OVER (ORDER BY amount) AS pr,
  CUME_DIST() OVER (ORDER BY amount) AS cd
FROM orders;

Можно использовать PARTITION BY:

SELECT
  user_id,
  amount,
  PERCENT_RANK() OVER (
    PARTITION BY user_id
    ORDER BY amount
  ) AS user_pr
FROM orders;

Обе функции работают по стандартной логике SQL.

PERCENT_RANK и CUME_DIST в MySQL

В MySQL 8.0+ PERCENT_RANK и CUME_DIST тоже поддерживаются как оконные функции.

Синтаксис похож:

SELECT
  id,
  amount,
  PERCENT_RANK() OVER (ORDER BY amount) AS pr,
  CUME_DIST() OVER (ORDER BY amount) AS cd
FROM orders;

В MySQL 5.7 оконных функций нет, поэтому такие вычисления приходилось эмулировать через переменные, подзапросы или выполнять вне SQL.

Если проект на MySQL, обязательно проверяйте версию.

PERCENT_RANK и CUME_DIST в ClickHouse

В ClickHouse поддержка оконных функций зависит от версии и настроек.

Если нужной функции нет или её поведение отличается, PERCENT_RANK можно собрать вручную через rank() и count().

Идея такая:

(rank - 1) / (rows - 1)

Примерный шаблон:

WITH ranked AS (
  SELECT
    id,
    amount,
    rank() OVER (ORDER BY amount DESC) AS rnk,
    count() OVER () AS total_rows
  FROM orders
)
SELECT
  id,
  amount,
  if(total_rows = 1, 0, (rnk - 1) / (total_rows - 1)) AS percent_rank
FROM ranked;

Для CUME_DIST ручная эмуляция сложнее, особенно при ничьих, потому что нужно учитывать конец группы равных значений.

При переносе таких запросов между СУБД лучше проверять результат на маленьком тестовом наборе с дублями и NULL.

Практические шаблоны

Относительная позиция заказов по сумме

SELECT
  id,
  amount,
  PERCENT_RANK() OVER (ORDER BY amount DESC) AS pr,
  CUME_DIST() OVER (ORDER BY amount DESC) AS cd
FROM orders
ORDER BY amount DESC;

Топ-10% клиентов по тратам

WITH user_spending AS (
  SELECT
    u.id,
    u.email,
    SUM(o.amount) AS total_spent
  FROM users u
  JOIN orders o ON o.user_id = u.id
  WHERE o.status = 'paid'
  GROUP BY u.id, u.email
),
ranked AS (
  SELECT
    id,
    email,
    total_spent,
    CUME_DIST() OVER (ORDER BY total_spent DESC) AS cd
  FROM user_spending
)
SELECT
  id,
  email,
  total_spent,
  cd
FROM ranked
WHERE cd <= 0.1
ORDER BY total_spent DESC;

Перцентиль зарплаты внутри отдела

SELECT
  id,
  name,
  dept,
  salary,
  PERCENT_RANK() OVER (
    PARTITION BY dept
    ORDER BY salary ASC
  ) AS dept_salary_pr
FROM employees
ORDER BY dept, salary;

Нижние 25% зарплат внутри отдела

WITH ranked AS (
  SELECT
    id,
    name,
    dept,
    salary,
    CUME_DIST() OVER (
      PARTITION BY dept
      ORDER BY salary ASC
    ) AS cd
  FROM employees
)
SELECT
  id,
  name,
  dept,
  salary,
  cd
FROM ranked
WHERE cd <= 0.25
ORDER BY dept, salary;

Лидерборд с относительной позицией

SELECT
  id,
  name,
  score,
  RANK() OVER (ORDER BY score DESC) AS rank,
  PERCENT_RANK() OVER (ORDER BY score DESC) AS pr,
  CUME_DIST() OVER (ORDER BY score DESC) AS cd
FROM players
ORDER BY score DESC, id;

Сравнение с NTILE

SELECT
  id,
  amount,
  NTILE(4) OVER (ORDER BY amount DESC) AS quartile,
  PERCENT_RANK() OVER (ORDER BY amount DESC) AS pr,
  CUME_DIST() OVER (ORDER BY amount DESC) AS cd
FROM orders
ORDER BY amount DESC;

Явная обработка NULL

SELECT
  id,
  salary,
  PERCENT_RANK() OVER (
    ORDER BY salary DESC NULLS LAST
  ) AS salary_pr
FROM employees;

Сохранить ничьи, но стабилизировать вывод

SELECT
  id,
  name,
  spent,
  PERCENT_RANK() OVER (ORDER BY spent DESC) AS pr,
  CUME_DIST() OVER (ORDER BY spent DESC) AS cd
FROM customers
ORDER BY spent DESC, id;

Разбить ничьи тай-брейкером внутри окна

SELECT
  id,
  name,
  spent,
  PERCENT_RANK() OVER (ORDER BY spent DESC, id) AS pr
FROM customers
ORDER BY spent DESC, id;

Что важно запомнить

PERCENT_RANK и CUME_DIST показывают относительную позицию строки в окне.

Пример:

SELECT
  id,
  amount,
  PERCENT_RANK() OVER (ORDER BY amount DESC) AS pr,
  CUME_DIST() OVER (ORDER BY amount DESC) AS cd
FROM orders;

Главные правила:

• обе функции являются оконными;
• обе возвращают число от 0 до 1;
• обязательно нужен осмысленный ORDER BY;
• PERCENT_RANK показывает позицию на шкале от первой строки до последней;
• формула PERCENT_RANK: (rank - 1) / (rows - 1);
• первая строка получает PERCENT_RANK = 0;
• CUME_DIST показывает накопленную долю строк до текущей включительно;
• последняя строка получает CUME_DIST = 1;
• при ничьих строки получают одинаковые значения;
• PARTITION BY считает распределение отдельно внутри каждой группы;
• NULL участвует в сортировке, поэтому лучше явно писать NULLS FIRST или NULLS LAST;
• если добавить тай-брейкер в окно, ничьи могут исчезнуть;
• если ничьи должны делить позицию, тай-брейкер лучше оставить только во внешнем ORDER BY.

Короткий вывод

PERCENT_RANK и CUME_DIST нужны, когда обычного места в рейтинге недостаточно.

PERCENT_RANK отвечает на вопрос:

Где строка находится на шкале от лучшей к худшей?

CUME_DIST отвечает на вопрос:

Какая доля строк находится до текущей строки включительно?

Пример:

SELECT
  id,
  total_spent,
  PERCENT_RANK() OVER (ORDER BY total_spent DESC) AS pr,
  CUME_DIST() OVER (ORDER BY total_spent DESC) AS cd
FROM user_spending;

Для лидербордов, зарплат, активности и клиентских сегментов эти функции дают удобную относительную позицию в диапазоне 0..1.

Главная мысль:

PERCENT_RANK — это позиция на шкале. CUME_DIST — это накопленная доля строк.

А чтобы отчёт был надёжным, всегда явно задавайте сортировку, думайте о ничьих и не забывайте, что направление ORDER BY меняет смысл результата.