PERCENTILE_DISC в SQL: перцентиль, который возвращает реальное значение из таблицы

Иногда «среднее между двумя строками» звучит красиво только в теории.

Представьте, что у нас есть статусы заказов:

created
paid
shipped
delivered

Какой смысл в «медианном статусе» между paid и shipped? Такого статуса нет. Его нельзя показать пользователю, положить в отчёт или использовать в бизнес-логике.

Или другой пример: в прайсе есть цены 200 и 300, но цены 250 в реальности нет. Если отчёт должен показывать только настоящие значения из данных, синтетическая середина может быть неуместной.

Для таких случаев в PostgreSQL есть PERCENTILE_DISC.

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

То есть результат можно честно объяснить так:

Это значение действительно есть в таблице.

Без половинчатых статусов, выдуманных цен и «средних» значений, которых никто никогда не вводил.

Что такое перцентиль простыми словами

Перцентиль показывает, ниже какого значения находится определённая доля данных.

Например:

• 0.5 — это 50-й перцентиль, или медиана;
• 0.9 — это 90-й перцентиль;
• 0.99 — это 99-й перцентиль.

Если говорить по-человечески:

90-й перцентиль — это такое значение, ниже или на уровне которого находится примерно 90% строк.

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

delivery_days
1
2
2
3
10

Медиана здесь — 2, потому что это середина отсортированного набора.

А 90-й перцентиль будет ближе к верхнему хвосту: он помогает понять не обычный случай, а почти самый медленный сценарий.

Перцентили часто используют для анализа:

• зарплат;
• цен;
• времени доставки;
• времени ответа API;
• размера заказов;
• длительности сессий;
• SLA и производительности.

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

Что делает PERCENTILE_DISC

PERCENTILE_DISC — это дискретный перцентиль.

Слово «дискретный» здесь означает:

Функция выбирает значение из уже существующих значений, а не придумывает новое между соседними.

Допустим, есть четыре суммы заказов:

amount
100
200
300
400

Если считать медиану «математически гладко», середина находится между 200 и 300, то есть 250.

Но значения 250 в таблице нет.

PERCENTILE_DISC(0.5) вернёт одно из реальных значений. В PostgreSQL для такого набора медианой будет 200.

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM orders;

Здесь результат — не абстрактная середина между строками, а конкретное значение из столбца amount.

Базовый синтаксис

PERCENTILE_DISC относится к ordered-set aggregate — упорядоченным агрегатным функциям.

Из-за этого синтаксис отличается от привычных SUM, AVG и COUNT.

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM orders;

Разберём по частям:

• PERCENTILE_DISC(0.5) — просим 50-й перцентиль;
• WITHIN GROUP — говорим, что агрегат работает внутри отсортированной группы;
• ORDER BY amount — задаём порядок значений, по которому ищется перцентиль;
• AS median_amount — называем итоговую колонку.

Ещё пример: 90-й перцентиль зарплат.

SELECT PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY salary) AS p90_salary
FROM employees;

Такой запрос отвечает на вопрос:

Какая зарплата находится на уровне, до которого дошли примерно 90% сотрудников?

И при этом результатом будет настоящая зарплата из таблицы employees, а не интерполяция между двумя зарплатами.

Почему нужен WITHIN GROUP

У обычных агрегатов порядок строк часто не важен.

Например, для COUNT(*) всё равно, в каком порядке считать строки. Для SUM(amount) тоже.

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

Поэтому порядок указывается прямо внутри конструкции WITHIN GROUP.

Правильно:

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM orders;

Неправильно:

SELECT PERCENTILE_DISC(0.5 ORDER BY amount) AS median_amount
FROM orders;

Это не просто другой стиль. Это синтаксическая ошибка.

Запомните шаблон:

PERCENTILE_DISC(fraction) WITHIN GROUP (ORDER BY column)

Где fraction — число от 0 до 1.

Как PERCENTILE_DISC выбирает значение

Представим, что у нас есть суммы заказов:

amount
100
200
300
400
500

Для медианы:

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM orders;

Функция сортирует строки и идёт по ним, пока накопленная доля строк не достигнет нужного перцентиля.

Для пяти строк медиана — третье значение:

position	amount
1	100
2	200
3	300
4	400
5	500

Результат будет 300.

Теперь возьмём четыре строки:

amount
100
200
300
400

У набора нет одного центрального значения. В середине находятся 200 и 300.

PERCENTILE_DISC(0.5) не будет считать между ними среднее. Он выберет реальное значение из списка.

Результат:

median_amount
200

Вот это и есть главное отличие дискретного перцентиля: он не создаёт новое значение.

PERCENTILE_DISC против PERCENTILE_CONT

В PostgreSQL есть похожая функция — PERCENTILE_CONT.

Название похоже, но смысл другой.

PERCENTILE_CONT — непрерывный перцентиль. Он может интерполировать значения, то есть считать промежуточный результат между соседними строками.

Посмотрим на примере.

Пусть есть суммы:

amount
100
200
300
400

Сравним две функции:

SELECT
  PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount) AS disc_median,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS cont_median
FROM orders;

Результат будет примерно такой:

disc_median	cont_median
200	250

PERCENTILE_DISC вернул 200, потому что такое значение есть в таблице.

PERCENTILE_CONT вернул 250, потому что это середина между 200 и 300.

Обе функции полезны, но для разных задач.

Когда лучше PERCENTILE_DISC

Используйте PERCENTILE_DISC, когда результат обязан быть настоящим значением из данных.

Например:

• статус заказа;
• страна пользователя;
• дата события;
• enum-значение;
• тариф;
• категория;
• дискретная цена;
• количество товаров;
• значение, которое нельзя делить пополам.

Представьте, что в таблице заказов есть статусы:

created
paid
shipped
delivered

Мы можем посчитать «медианный» статус по отсортированному списку статусов:

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY status) AS median_status
FROM orders;

Звучит немного необычно, но технически это возможно: строки можно отсортировать, а PERCENTILE_DISC умеет работать с сортируемыми типами.

PERCENTILE_CONT здесь не подойдёт, потому что нельзя интерполировать между строками. Между paid и shipped нет осмысленного среднего значения.

Ещё пример: медианная страна пользователя внутри каждого статуса заказа.

SELECT
  o.status,
  PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY u.country) AS median_country
FROM orders o
JOIN users u ON u.id = o.user_id
GROUP BY o.status;

Такой запрос вернёт реальную страну из данных для каждой группы статусов.

Важно понимать: для категорий это не всегда бизнес-смысловая «середина». Она зависит от сортировки. Но если вам нужно выбрать реальное значение по позиции в отсортированном наборе, PERCENTILE_DISC подходит.

Когда лучше PERCENTILE_CONT

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

Например:

• вес;
• рост;
• время ответа;
• сумма заказа;
• длительность доставки;
• температура;
• метрика производительности.

Если между значениями 200 и 300 сумма 250 выглядит нормальной оценкой центра, можно использовать PERCENTILE_CONT.

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM orders;

Такой результат может быть удобен для статистики.

Но если вы хотите показать пользователю значение, которое реально встречалось в заказах, лучше выбрать PERCENTILE_DISC.

Главный вопрос-фильтр:

Результат обязан существовать в таблице?

Если да — берите PERCENTILE_DISC.

Если нет, и нужна гладкая статистическая оценка — смотрите в сторону PERCENTILE_CONT.

Почему разница особенно заметна на маленьких данных

На больших выборках PERCENTILE_DISC и PERCENTILE_CONT часто дают близкие результаты.

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

А вот на маленьких выборках разница может быть огромной.

Допустим, есть всего два заказа:

amount
100
1000

PERCENTILE_DISC(0.5) вернёт 100.

PERCENTILE_CONT(0.5) вернёт 550.

SELECT
  PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount) AS disc_median,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS cont_median
FROM orders;

Какой результат правильный?

Зависит от задачи.

Если нужен реальный заказ из таблицы — 100.

Если нужна математическая середина между двумя наблюдениями — 550.

На маленьких данных выбор функции может сильно менять итог отчёта. Поэтому нельзя просто писать «медиана» и не уточнять, как именно она считается.

PERCENTILE_DISC с GROUP BY

Как и другие агрегаты, PERCENTILE_DISC можно использовать вместе с GROUP BY.

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

SELECT
  status,
  PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM orders
GROUP BY status
ORDER BY status;

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

То есть отдельно для:

• created;
• paid;
• shipped;
• cancelled;
• других статусов.

Ещё пример: 90-й перцентиль зарплат по отделам.

SELECT
  dept,
  PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY salary) AS p90_salary
FROM employees
GROUP BY dept
ORDER BY dept;

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

Несколько перцентилей за один вызов

В PostgreSQL можно передать в PERCENTILE_DISC не одно число, а массив долей.

Например, получить сразу медиану, 90-й и 99-й перцентили:

SELECT
  PERCENTILE_DISC(ARRAY[0.5, 0.9, 0.99]) WITHIN GROUP (ORDER BY amount) AS percentiles
FROM orders;

Результатом будет массив значений.

Например:

{200,980,1500}

Это удобно: не нужно писать три отдельных запроса.

Можно разложить массив по колонкам:

SELECT
  p[1] AS p50,
  p[2] AS p90,
  p[3] AS p99
FROM (
  SELECT
    PERCENTILE_DISC(ARRAY[0.5, 0.9, 0.99]) WITHIN GROUP (ORDER BY amount) AS p
  FROM orders
) t;

Так отчёт становится аккуратнее: каждая метрика лежит в своей колонке.

Квартили по группам

Квартили делят данные на четыре части.

Обычно используют:

• 0.25 — первый квартиль;
• 0.5 — медиана;
• 0.75 — третий квартиль.

Например, посчитаем квартили зарплат по отделам:

SELECT
  dept,
  PERCENTILE_DISC(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY salary) AS quartiles
FROM employees
GROUP BY dept
ORDER BY dept;

Результат может выглядеть так:

dept	quartiles
analytics	{1200,1800,2500}
support	{900,1300,1700}
engineering	{2500,4000,5500}

Такой отчёт гораздо информативнее среднего значения.

Средняя зарплата может быть высокой из-за одного руководителя с большим окладом. А квартили показывают распределение спокойнее и честнее.

Что происходит с NULL

PERCENTILE_DISC игнорирует NULL в сортируемых значениях, как и многие другие агрегаты.

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM orders;

Если в amount есть NULL, они не участвуют в расчёте перцентиля.

Обычно это удобно: неизвестная сумма заказа не должна становиться частью медианы.

Но если NULL в ваших данных имеет особый смысл, это нужно учитывать отдельно.

Например, можно заранее посмотреть, сколько строк не попало в расчёт:

SELECT
  COUNT(*) AS total_rows,
  COUNT(amount) AS rows_with_amount,
  COUNT(*) - COUNT(amount) AS rows_without_amount
FROM orders;

Так отчёт будет честнее: вы покажете не только медиану, но и качество данных.

Сортировка влияет на результат

PERCENTILE_DISC выбирает значение из отсортированного набора. Поэтому ORDER BY — это не техническая мелочь, а часть смысла.

Для чисел всё обычно понятно:

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM orders;

Для дат тоже:

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY created_at) AS median_created_at
FROM orders;

А вот для текста нужно быть аккуратнее.

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY country) AS median_country
FROM users;

Технически запрос корректный. Но результат зависит от порядка сортировки строк. Для бизнес-смысла это может быть странно: «медианная страна» не всегда полезная метрика.

Поэтому для текстовых и категориальных данных задайте себе вопрос:

Мне действительно нужна середина по сортировке или я просто хочу выбрать самое частое значение?

Если нужно самое частое значение, это уже другая задача — мода, а не медиана.

PERCENTILE_DISC не заменяет MODE

Иногда начинающие аналитики видят, что PERCENTILE_DISC работает с текстом, и пытаются использовать его как «типичное значение».

Но медиана и самое частое значение — разные вещи.

Допустим, есть страны пользователей:

country
Brazil
Brazil
Brazil
Canada
Vietnam

Самая частая страна — Brazil.

А результат PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY country) зависит от сортировки и позиции в отсортированном списке. В этом примере он тоже может оказаться Brazil, но это не потому, что функция ищет популярное значение. Она ищет значение по позиции.

Если вам нужно именно самое частое значение, используйте группировку:

SELECT
  country,
  COUNT(*) AS users_count
FROM users
GROUP BY country
ORDER BY users_count DESC, country
LIMIT 1;

PERCENTILE_DISC отвечает не на вопрос «что встречается чаще всего», а на вопрос «какое реальное значение находится на нужной позиции в отсортированном наборе».

Производительность: почему перцентили могут быть дорогими

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

Это значит, что на больших таблицах запрос может быть тяжелее, чем обычный AVG или COUNT.

Например:

SELECT PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY amount) AS p90_amount
FROM orders;

Базе нужно собрать подходящие amount, отсортировать их и выбрать значение на нужной позиции.

Если добавить GROUP BY, сортировка фактически нужна внутри каждой группы:

SELECT
  status,
  PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY amount) AS p90_amount
FROM orders
GROUP BY status;

На небольших таблицах это не проблема. На больших событиях или заказах за несколько лет — уже может быть заметно.

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

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

Пример: перцентили времени доставки

Допустим, в таблице orders есть дата создания заказа и дата доставки.

SELECT
  PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY delivered_at::date - created_at::date) AS p50_days,
  PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY delivered_at::date - created_at::date) AS p90_days
FROM orders
WHERE delivered_at IS NOT NULL;

Такой запрос покажет:

• за сколько дней доставляется «обычный» заказ;
• за сколько дней доставляется заказ на уровне 90-го перцентиля.

Но обратите внимание: мы используем PERCENTILE_DISC, поэтому результат будет реальным количеством дней, которое встречалось в данных.

Если доставка занимает целое число дней, это может быть именно то, что нужно.

Пример: SLA по времени ответа

Допустим, у нас есть таблица запросов к API:

CREATE TABLE api_requests (
  id bigint,
  endpoint text,
  response_ms integer
);

Нужно посчитать 95-й перцентиль времени ответа по каждому endpoint.

SELECT
  endpoint,
  PERCENTILE_DISC(0.95) WITHIN GROUP (ORDER BY response_ms) AS p95_response_ms
FROM api_requests
GROUP BY endpoint
ORDER BY endpoint;

Такой отчёт хорошо подходит для SLA.

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

95-й перцентиль показывает это лучше.

Различия между СУБД

В PostgreSQL PERCENTILE_DISC есть и используется с WITHIN GROUP.

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM orders;

В MySQL такой функции в привычном виде нет. Перцентили там обычно эмулируют через оконные функции, например через ROW_NUMBER, COUNT и расчёт нужной позиции.

В ClickHouse похожие задачи решают функциями семейства quantile.

Для точного значения можно использовать quantileExact.

SELECT quantileExact(0.5)(amount) AS median_amount
FROM orders;

Для более быстрых оценок на больших данных в ClickHouse часто используют приближённые варианты.

SELECT quantile(0.5)(amount) AS median_amount
FROM orders;

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

Как выбрать между DISC и CONT

Самый полезный вопрос:

Значение в результате обязано реально существовать в данных?

Если да — используйте PERCENTILE_DISC.

Примеры:

• медианный статус;
• медианная дата из списка событий;
• дискретная цена из прайса;
• количество товаров;
• значение enum;
• категория или тариф.

Если нет, и вам нужна статистическая оценка между точками, используйте PERCENTILE_CONT.

Примеры:

• медианная сумма заказа как гладкая оценка;
• медианное время ответа;
• медианная длительность доставки;
• 90-й перцентиль числовой метрики.

Но даже для чисел PERCENTILE_DISC может быть правильнее, если отчёт должен показывать только наблюдавшиеся значения.

Главное

PERCENTILE_DISC возвращает дискретный перцентиль — значение, которое реально есть в данных.

Базовый синтаксис:

SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM orders;

Число в скобках — доля от 0 до 1.

• 0.5 — медиана;
• 0.9 — 90-й перцентиль;
• 0.99 — 99-й перцентиль.

Главное отличие от PERCENTILE_CONT:

• PERCENTILE_DISC выбирает реальное значение из таблицы;
• PERCENTILE_CONT может интерполировать и вернуть значение, которого в таблице нет.

На данных 100, 200, 300, 400 медианы будут разными:

SELECT
  PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount) AS disc_median,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS cont_median
FROM orders;

PERCENTILE_DISC вернёт 200, а PERCENTILE_CONT — 250.

Для нескольких перцентилей в PostgreSQL можно передать массив:

SELECT
  PERCENTILE_DISC(ARRAY[0.5, 0.9, 0.99]) WITHIN GROUP (ORDER BY amount) AS percentiles
FROM orders;

NULL в сортируемом столбце игнорируется.

WITHIN GROUP обязателен: обычный ORDER BY внутри скобок функции здесь не подходит.

Выбирайте PERCENTILE_DISC, когда результат должен быть настоящей строкой из данных. Выбирайте PERCENTILE_CONT, когда нужна гладкая статистическая оценка между значениями.