Чем по смыслу различаются непрерывный и дискретный процентиль в SQL?

Краткий ответ

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

Исторический контекст

Обычные агрегаты, такие как AVG, MIN и MAX, хорошо описывают среднее или крайние значения, но не показывают положение наблюдения внутри распределения. Для аналитики понадобились процентили: например, медиана, 95-й процентиль времени ответа или порог, ниже которого находится 99% запросов.

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

Постановка проблемы

Пусть упорядоченные значения времени ответа равны 10, 20, 30 и 40 миллисекунд. При вычислении медианы непрерывный метод может вернуть 25, хотя такого наблюдения не было, тогда как дискретный метод вернёт одно из исходных значений.

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

Подробное решение

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

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

В PostgreSQL и ряде аналитических СУБД эти функции записываются как упорядоченные агрегаты:

SELECT
    percentile_cont(0.5) WITHIN GROUP (ORDER BY latency_ms) AS median_cont,
    percentile_disc(0.5) WITHIN GROUP (ORDER BY latency_ms) AS median_disc
FROM requests;

Для значений 10, 20, 30 и 40 непрерывная медиана равна 25, а дискретная — 20. Точная позиционная формула и поддержка синтаксиса зависят от диалекта SQL, поэтому при переносе запроса нужно проверить документацию конкретной СУБД.

Оба варианта требуют определить порядок сортировки и обычно игнорируют NULL как отсутствующие значения, но поведение для пустого набора и особенности обработки NULL следует проверять для используемой СУБД. Процентиль также не равен среднему: он описывает позицию в распределении, а не сумму значений, делённую на их количество.

Практическое правило простое: для статистической оценки непрерывной метрики выбирают PERCENTILE_CONT, а для порога, который должен быть одним из реально наблюдавшихся значений, — PERCENTILE_DISC.

Ситуация из практики

Команда анализирует задержку API и хочет установить целевой показатель для 95% запросов. Вариант с непрерывным процентилем даёт более гладкую статистическую оценку и лучше подходит для сравнения версий сервиса, но результат может не совпадать ни с одной фактической задержкой.

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

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

Что кандидаты часто упускают

1. Может ли непрерывный процентиль вернуть значение, которого нет в таблице?

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

2. Какой процентиль выбрать для порога, который нельзя нарушать у заданной доли наблюдений?

Одного названия функции недостаточно: нужно уточнить бизнес-смысл порога и правило округления. Если порог обязан быть реальным наблюдаемым значением и должен гарантировать достижение заданной доли, обычно выбирают дискретную семантику. При этом необходимо проверить точное определение процентиля в конкретной СУБД, особенно для малых выборок.

3. Почему результаты процентиля могут меняться после добавления одной строки?

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