Перцентили в SQL: NTILE, PERCENTILE_CONT и медиана

Через ordered-set функцию: PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY value). Это аргумент 0.5 — пятидесятый перцентиль, то есть медиана с линейной интерполяцией между соседними значениями, если строк чётное число. Работает и как обычная агрегатная функция по всей выборке, и с GROUP BY по сегментам: SELECT city, PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY delivery_time) FROM orders GROUP BY city. В отличие от AVG, медиана устойчива к выбросам, поэтому её и просят для времени доставки или чека.

Это разные задачи. NTILE(n) OVER (ORDER BY col) — оконная функция, она присваивает каждой строке номер бакета от 1 до n, разбивая набор на n примерно равных по количеству групп. Ответ — метка группы для каждой строки. PERCENTILE_CONT(p) WITHIN GROUP (ORDER BY col) — агрегатная ordered-set функция, она возвращает одно значение: границу перцентиля p. То есть NTILE отвечает «в какой квартиль попал юзер», а PERCENTILE_CONT — «какое значение выручки на границе квартиля». На собесе их часто дают вместе, чтобы проверить, не путаете ли.

PERCENTILE_CONT интерполирует: если 50-й перцентиль лежит между значениями 100 и 110, вернёт 105, даже если такого значения в данных нет. PERCENTILE_DISC возвращает первое реально существующее значение, чья накопленная доля не меньше p — то есть всегда одно из фактических чисел в колонке. CONT берут для непрерывных метрик (время, сумма, длительность), DISC — когда значение должно быть из набора (например, конкретная цена из прайса или дискретная категория). Уточнение про интерполяцию — типичный дополнительный вопрос.

NTILE(4) OVER (ORDER BY total_revenue) AS quartile присвоит каждому 1, 2, 3 или 4, где 4 — топ по выручке. Дальше по этой метке группируете: GROUP BY quartile и считаете AVG(total_revenue), COUNT(*) на каждый квартиль, чтобы описать сегменты. Это основа RFM и сегментации по деньгам. Нюанс: NTILE распределяет строки как можно равномернее, и если строк не делится на 4 нацело, первые группы получат на одну строку больше — при равных значениях на границе бакеты могут разрезать одинаковых юзеров, о чём стоит помнить.

Среднее прячет хвост распределения. Если 95% запросов отвечают за 100 мс, а 5% висят по 10 секунд, AVG покажет приемлемые цифры, но именно эти 5% ломают пользователям опыт. Перцентиль p95 = PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY response_time) отвечает на вопрос «сколько ждут самые невезучие 5%» — это стандарт для SLA и latency. Ответ, что среднее нечувствительно к хвосту, а перцентили описывают именно его, показывает, что кандидат думает про распределение, а не про одно число.