13 мая 2026 г.·6 мин чтения

Как посчитать медиану в SQL

Проверь себя · 1/3разбор после ответа
Какое утверждение верно про DATE_TRUNC('week', ts) в PostgreSQL (где ts имеет тип timestamp)?

Содержание:

Зачем медиана

Медиана — это значение, стоящее ровно в середине отсортированного ряда: половина наблюдений меньше него, половина больше. Её главное свойство — устойчивость (robustness) к выбросам. В выручке всегда есть «киты» (whales) — несколько клиентов, которые платят в десятки раз больше остальных. Среднее (mean) они утаскивают вверх, и цифра перестаёт описывать типичного пользователя. Медиану же они почти не двигают: один сверхбогатый клиент сдвигает середину ряда лишь на одну позицию.

Поэтому на собесах по аналитике медиану любят противопоставлять среднему: интервьюер проверяет, понимаете ли вы, что для скошенных (skewed) распределений вроде дохода или чека медиана честнее среднего.

PERCENTILE_CONT

PERCENTILE_CONT (continuous, непрерывный) вычисляет перцентиль с интерполяцией между соседними значениями. Медиана — это 50-й перцентиль, то есть аргумент 0.5:

SELECT
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM transactions
WHERE status = 'paid'
  AND created_at >= CURRENT_DATE - INTERVAL '30 days';

Если между двумя центральными значениями есть «разрыв», PERCENTILE_CONT вернёт промежуточное число:

-- Ряд: [1, 2, 3, 4]
-- PERCENTILE_CONT(0.5) = (2 + 3) / 2 = 2.5

PERCENTILE_DISC

PERCENTILE_DISC (discrete, дискретный) не интерполирует, а возвращает реально существующее в данных значение — ближайшее по позиции:

SELECT
    PERCENTILE_DISC(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM transactions
WHERE status = 'paid';
-- Ряд: [1, 2, 3, 4]
-- PERCENTILE_DISC(0.5) = 2 (меньшее из двух центральных)

Правило выбора простое: PERCENTILE_CONT — для непрерывных величин (сумма, время, вес), где дробное промежуточное значение осмысленно. PERCENTILE_DISC — для дискретных или категориальных величин, где важно вернуть настоящее значение из набора, а не выдуманное среднее.

По группам

Медиану часто считают в разрезе — например, по странам. Заодно полезно вытащить квартили (0.25 и 0.75) и среднее, чтобы сразу видеть форму распределения:

SELECT
    country,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY amount) AS p25,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY amount) AS p75,
    AVG(amount) AS mean_amount
FROM transactions
JOIN users USING (user_id)
WHERE status = 'paid'
  AND created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY country
ORDER BY median_amount DESC;

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

Медиана против среднего

Разрыв между средним и медианой — удобный индикатор скошенности. Считаем обе метрики и по их отношению классифицируем распределение:

WITH stats AS (
    SELECT
        PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_amt,
        AVG(amount) AS mean_amt,
        STDDEV(amount) AS std_amt,
        COUNT(*) AS n
    FROM transactions
    WHERE status = 'paid'
)
SELECT
    median_amt,
    mean_amt,
    mean_amt - median_amt AS skew_indicator,
    -- Положительная скошенность → среднее выше медианы (влияние китов)
    CASE
        WHEN mean_amt > median_amt * 1.5 THEN 'heavily RIGHT-skewed'
        WHEN mean_amt > median_amt * 1.1 THEN 'RIGHT-skewed'
        ELSE 'symmetric'
    END AS distribution
FROM stats;

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

Закрепи формулу median в Карьернике
Запомнить надолго — 5 коротких сессий с задачами на эту тему. Бесплатно
Тренировать median в Telegram

MySQL и старые БД

PERCENTILE_CONT есть не во всех СУБД — в частности, в старых версиях MySQL его нет. Медиану там считают вручную: нумеруем строки по порядку, берём общее количество и усредняем одно или два центральных значения:

SELECT AVG(t.amount) AS median
FROM (
    SELECT amount, ROW_NUMBER() OVER (ORDER BY amount) AS rn,
           COUNT(*) OVER () AS total
    FROM transactions
    WHERE status = 'paid'
) t
WHERE t.rn IN (FLOOR((t.total + 1) / 2), CEIL((t.total + 1) / 2));

Приём универсален: для нечётного числа строк FLOOR и CEIL дадут одну и ту же позицию (возьмётся одно центральное значение), для чётного — две соседние, и AVG их усреднит. Оконные функции появились в MySQL начиная с версии 8.0; в более ранних придётся эмулировать нумерацию через переменные.

Как это спрашивают на собесе

«Средний чек — 5000, а вы говорите, что типичный клиент платит 1200. Как так?» Классический вопрос про скошенность. Правильный ответ: средний чек задирают несколько крупных плательщиков, а медиана (1200) честнее описывает типичного клиента. Для выручки, зарплат и чеков почти всегда репортят медиану. Потренировать такие вопросы с разбором можно в Карьернике.

«Напишите медиану на PostgreSQL». Ждут PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col) и понимание, что это упорядоченно-множественная агрегатная функция с синтаксисом WITHIN GROUP, а не обычный AVG.

«Чем PERCENTILE_CONT отличается от PERCENTILE_DISC?» Первый интерполирует и может вернуть значение, которого нет в данных, второй возвращает реально существующее. Для непрерывных величин берут CONT, для дискретных/категориальных — DISC.

Частые ошибки

AVG вместо медианы для выручки. Крупные плательщики (top-1%) способны раздуть среднее в полтора-два раза, и оно перестаёт отражать типичного клиента. Для скошенных данных медиана устойчивее — с неё и стоит начинать.

PERCENTILE_CONT нельзя использовать в HAVING. В большинстве СУБД упорядоченно-множественные агрегаты недоступны в HAVING. Если нужно фильтровать по медиане, оберните расчёт в подзапрос или CTE и фильтруйте уже снаружи.

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

Производительность на больших таблицах. PERCENTILE_CONT требует сортировки всех значений и на больших объёмах работает медленно. Если точная медиана не критична, берите выборку (sample) или приближённые функции — например, approx_percentile в ClickHouse или APPROX_QUANTILES в BigQuery.

Скользящая медиана. Медиана «по каждой строке» (rolling median) — задача заметно сложнее: обычный PERCENTILE_CONT не работает как оконная функция, и приходится комбинировать сортировку с нумерацией строк вручную. Это частый подвох на продвинутых собесах.

Связанные темы

FAQ

PERCENTILE_CONT или PERCENTILE_DISC — что выбрать?

PERCENTILE_CONT интерполирует между соседними значениями и может вернуть число, которого нет в данных (например, 2.5 для ряда [1,2,3,4]). PERCENTILE_DISC возвращает реально существующее значение. Для непрерывных величин (суммы, время) берут CONT, для дискретных или категориальных, где важно настоящее значение из набора, — DISC.

Когда репортить медиану, а когда среднее?

Для скошенных вправо распределений — выручка, зарплаты, чеки, время сессии — медиана честнее, потому что устойчива к выбросам. Для приблизительно симметричных величин (рост, тестовые баллы) среднее корректно и информативнее, так как учитывает все значения. На собесе стоит проговорить форму распределения перед выбором метрики.

Какой синтаксис медианы в PostgreSQL?

PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY col). Это упорядоченно-множественная агрегатная функция: конструкция WITHIN GROUP (ORDER BY ...) задаёт, по какому столбцу упорядочивать значения. Аргумент 0.5 — это доля, то есть 50-й перцентиль.

Как посчитать скользящую медиану (median как оконную функцию)?

Напрямую не выйдет: PERCENTILE_CONT не поддерживается как оконная функция в большинстве СУБД. Обходной путь — внутри окна пронумеровать строки ROW_NUMBER, посчитать COUNT и вручную выбрать центральные позиции, как в приёме для MySQL выше. Это заметно сложнее обычной медианы, поэтому такой вопрос считается продвинутым.

Как в SQL найти моду (самое частое значение)?

В PostgreSQL для этого есть отдельная упорядоченно-множественная функция MODE:

SELECT MODE() WITHIN GROUP (ORDER BY col) FROM transactions;

Она возвращает самое часто встречающееся значение. Если таких несколько, вернётся первое по сортировке. В СУБД без MODE то же самое делают через GROUP BY col ORDER BY COUNT(*) DESC LIMIT 1.

Ещё по теме