See artikkel on praegu venekeelne — ingliskeelne tõlge on töös.
Иногда среднее между двумя строками — это бессмыслица. Какой смысл в «медианном статусе» где-то посередине между paid и shipped? В цене 250.5, если в прайсе нет половинок? Ровно для таких случаев и существует PERCENTILE_DISC: он возвращает перцентиль как одно из реально существующих значений в данных, без интерполяции между соседями. Результат всегда можно показать пользователю и честно сказать — «вот эта строка действительно есть в таблице». Никаких синтетических чисел, которых никто никогда не вводил, никаких «средних по больнице» — только то, что лежит в столбце на самом деле.
Базовый синтаксис
PERCENTILE_DISC — это упорядоченная агрегатная функция (ordered-set aggregate), и потому у неё непривычный синтаксис с WITHIN GROUP (ORDER BY ...). В скобках после имени функции стоит доля от 0 до 1: 0.5 — это медиана, 0.9 — девяностый перцентиль, и так далее.
SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM orders;
SELECT PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY salary) AS p90_salary
FROM employees;
Механика проста. Строки сортируются по ORDER BY, затем функция идёт по ним и берёт первое значение, у которого накопленная доля строк дотягивает до заданной. Иначе говоря, для доли 0.9 мы поднимаемся по отсортированному списку, пока не накопим девяносто процентов строк, и фиксируем то значение, на котором это произошло. Что важно: на выходе всегда оказывается ровно одно из значений столбца — ни на копейку больше, ни на копейку меньше.
Дискретный против непрерывного
Вот здесь и кроется вся соль. PERCENTILE_CONT интерполирует: при чётном числе строк медиана у него — это среднее двух центральных. PERCENTILE_DISC так не хитрит и отдаёт реальное значение из выборки. Поставьте их рядом в одном запросе и сразу увидите расхождение:
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 всегда возвращает значение, которое в данных есть; CONT может выдать число, которого в таблице нет вообще.CONT хочет числовой тип (или интервал). DISC дружит и с текстом, и с датами, и с enum — с любым сортируемым типом.- На больших выборках их ответы сходятся почти в точку, а на маленьких разъезжаются ощутимо.
Отсюда и практический ориентир: чем меньше строк и чем «зернистее» данные, тем заметнее разница между двумя функциями. На отчёте по трём заказам выбор между DISC и CONT способен изменить итоговую цифру вдвое, а на миллионе строк им обоим уже почти всё равно.
Когда брать дискретный
Тянитесь за PERCENTILE_DISC всякий раз, когда промежуточное значение либо бессмысленно, либо попросту недопустимо:
- Категориальные и текстовые данные: «медианный» статус или страна обязаны быть настоящей строкой, а не плодом интерполяции.
- Дискретные суммы и цены, где
250.5 физически не бывает.
- SLA и отчётность, где нужно предъявить конкретную наблюдавшуюся величину, а не усреднённую абстракцию.
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_CONT даже не запустится: интерполировать строки country нечем и незачем, и СУБД честно об этом скажет. Так что выбор тут не вопрос вкуса, а вопрос типа данных — для нечисловых столбцов дискретный вариант просто единственный возможный.
Несколько перцентилей за один проход
В PostgreSQL есть приятный трюк: вместо одной доли можно передать целый массив и получить массив результатов одним вызовом. Это дешевле трёх отдельных запросов, потому что дорогая сортировка выполняется ровно один раз, а не трижды.
SELECT PERCENTILE_DISC(ARRAY[0.5, 0.9, 0.99])
WITHIN GROUP (ORDER BY amount) AS p
FROM orders;
Массив удобно тут же разложить по отдельным колонкам:
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;
А с GROUP BY приём раскрывается во всю силу, когда перцентили нужны в разрезе по сегментам:
SELECT dept,
PERCENTILE_DISC(ARRAY[0.25, 0.5, 0.75])
WITHIN GROUP (ORDER BY salary) AS quartiles
FROM employees
GROUP BY dept;
Грабли и различия между СУБД
NULL в ORDER BY при подсчёте позиции игнорируется — ровно как в прочих агрегатах. Обычно это и нужно, но если NULL у вас несут смысл, перепроверьте: молчаливое исключение строк легко не заметить.WITHIN GROUP нельзя подменить обычным ORDER BY внутри скобок функции — это синтаксическая ошибка, а не вопрос вкуса.- В MySQL
PERCENTILE_DISC нет вообще; перцентили там эмулируют оконными функциями и NTILE/ROW_NUMBER.
- В ClickHouse прямой аналог —
quantileExact(0.5)(amount): он тоже отдаёт реальное значение из данных, тогда как обычный quantile() интерполирует.
SELECT
quantileExact(0.5)(amount) AS disc_median,
quantile(0.5)(amount) AS cont_median
FROM orders;
Правило выбора в одну строку: если результат должен читаться как «настоящая строка из таблицы» — берите PERCENTILE_DISC; если нужна гладкая статистическая оценка центра или хвоста распределения — это работа для PERCENTILE_CONT. Запомните этот вопрос-фильтр — «обязано ли значение реально существовать в данных?» — и в девяти случаях из десяти он сам подскажет нужную функцию.
Иногда среднее между двумя строками — это бессмыслица. Какой смысл в «медианном статусе» где-то посередине между
paidиshipped? В цене250.5, если в прайсе нет половинок? Ровно для таких случаев и существуетPERCENTILE_DISC: он возвращает перцентиль как одно из реально существующих значений в данных, без интерполяции между соседями. Результат всегда можно показать пользователю и честно сказать — «вот эта строка действительно есть в таблице». Никаких синтетических чисел, которых никто никогда не вводил, никаких «средних по больнице» — только то, что лежит в столбце на самом деле.Базовый синтаксис
PERCENTILE_DISC— это упорядоченная агрегатная функция (ordered-set aggregate), и потому у неё непривычный синтаксис сWITHIN GROUP (ORDER BY ...). В скобках после имени функции стоит доля от 0 до 1: 0.5 — это медиана, 0.9 — девяностый перцентиль, и так далее.-- Median order amount as a real value from the data SELECT PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount FROM orders; -- 90th percentile of salaries SELECT PERCENTILE_DISC(0.9) WITHIN GROUP (ORDER BY salary) AS p90_salary FROM employees;Механика проста. Строки сортируются по
ORDER BY, затем функция идёт по ним и берёт первое значение, у которого накопленная доля строк дотягивает до заданной. Иначе говоря, для доли 0.9 мы поднимаемся по отсортированному списку, пока не накопим девяносто процентов строк, и фиксируем то значение, на котором это произошло. Что важно: на выходе всегда оказывается ровно одно из значений столбца — ни на копейку больше, ни на копейку меньше.Дискретный против непрерывного
Вот здесь и кроется вся соль.
PERCENTILE_CONTинтерполирует: при чётном числе строк медиана у него — это среднее двух центральных.PERCENTILE_DISCтак не хитрит и отдаёт реальное значение из выборки. Поставьте их рядом в одном запросе и сразу увидите расхождение:-- For amounts 100, 200, 300, 400: -- CONT median = 250 (interpolated), DISC median = 200 (real value) 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всегда возвращает значение, которое в данных есть;CONTможет выдать число, которого в таблице нет вообще.CONTхочет числовой тип (или интервал).DISCдружит и с текстом, и с датами, и с enum — с любым сортируемым типом.Отсюда и практический ориентир: чем меньше строк и чем «зернистее» данные, тем заметнее разница между двумя функциями. На отчёте по трём заказам выбор между
DISCиCONTспособен изменить итоговую цифру вдвое, а на миллионе строк им обоим уже почти всё равно.Когда брать дискретный
Тянитесь за
PERCENTILE_DISCвсякий раз, когда промежуточное значение либо бессмысленно, либо попросту недопустимо:250.5физически не бывает.-- Median country code per status: must be a real value, not interpolated 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_CONTдаже не запустится: интерполировать строкиcountryнечем и незачем, и СУБД честно об этом скажет. Так что выбор тут не вопрос вкуса, а вопрос типа данных — для нечисловых столбцов дискретный вариант просто единственный возможный.Несколько перцентилей за один проход
В PostgreSQL есть приятный трюк: вместо одной доли можно передать целый массив и получить массив результатов одним вызовом. Это дешевле трёх отдельных запросов, потому что дорогая сортировка выполняется ровно один раз, а не трижды.
-- p50, p90, p99 in a single ordered pass SELECT PERCENTILE_DISC(ARRAY[0.5, 0.9, 0.99]) WITHIN GROUP (ORDER BY amount) AS p FROM orders; -- p -> {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;А с
GROUP BYприём раскрывается во всю силу, когда перцентили нужны в разрезе по сегментам:SELECT dept, PERCENTILE_DISC(ARRAY[0.25, 0.5, 0.75]) WITHIN GROUP (ORDER BY salary) AS quartiles FROM employees GROUP BY dept;Грабли и различия между СУБД
NULLвORDER BYпри подсчёте позиции игнорируется — ровно как в прочих агрегатах. Обычно это и нужно, но еслиNULLу вас несут смысл, перепроверьте: молчаливое исключение строк легко не заметить.WITHIN GROUPнельзя подменить обычнымORDER BYвнутри скобок функции — это синтаксическая ошибка, а не вопрос вкуса.PERCENTILE_DISCнет вообще; перцентили там эмулируют оконными функциями иNTILE/ROW_NUMBER.quantileExact(0.5)(amount): он тоже отдаёт реальное значение из данных, тогда как обычныйquantile()интерполирует.-- ClickHouse: exact (discrete) vs interpolated SELECT quantileExact(0.5)(amount) AS disc_median, quantile(0.5)(amount) AS cont_median FROM orders;Правило выбора в одну строку: если результат должен читаться как «настоящая строка из таблицы» — берите
PERCENTILE_DISC; если нужна гладкая статистическая оценка центра или хвоста распределения — это работа дляPERCENTILE_CONT. Запомните этот вопрос-фильтр — «обязано ли значение реально существовать в данных?» — и в девяти случаях из десяти он сам подскажет нужную функцию.