PrepBro
← Назад к вопросам

SQL: Медиана выручки по месяцам

2.2 Middle🔥 231 комментариев
#SQL и базы данных#Статистика и математика

Условие

У вас есть таблица daily_sales:

  • date (date)
  • revenue (decimal)

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

Ожидаемый результат:

Таблица с колонками: month, median_revenue

Подсказка: используйте PERCENTILE_CONT или ранжирование.

Источник: задача на SQL

Комментарии (1)

🐱
claude-haiku-4.5PrepBro AI23 мар. 2026 г.(ред.)

Ответ сгенерирован нейросетью и может содержать ошибки

Решение

Это задача на расчет медианы (50-го процентиля) выручки по месяцам. Медиана часто информативнее среднего, так как не подвергается влиянию выбросов.

Логика решения:

  1. Группируем по месяцам — извлекаем месяц из даты
  2. Рассчитываем медиану — 50-й процентиль дневной выручки в каждом месяце
  3. Выводим результат — месяц и медиану

SQL запрос с PERCENTILE_CONT (PostgreSQL):

SELECT 
  DATE_TRUNC('month', date)::date as month,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY revenue) as median_revenue
FROM daily_sales
GROUP BY DATE_TRUNC('month', date)
ORDER BY month;

Альтернативный подход с оконными функциями (универсальный):

WITH monthly_ranked AS (
  SELECT 
    DATE_TRUNC('month', date)::date as month,
    revenue,
    ROW_NUMBER() OVER (PARTITION BY DATE_TRUNC('month', date) ORDER BY revenue) as rn,
    COUNT(*) OVER (PARTITION BY DATE_TRUNC('month', date)) as cnt
  FROM daily_sales
)
SELECT 
  month,
  AVG(revenue) as median_revenue
FROM monthly_ranked
WHERE rn IN (FLOOR((cnt + 1) / 2.0), CEIL((cnt + 1) / 2.0))
GROUP BY month
ORDER BY month;

Вариант с дополнительной информацией:

SELECT 
  DATE_TRUNC('month', date)::date as month,
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY revenue) as q1_revenue,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY revenue) as median_revenue,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY revenue) as q3_revenue,
  AVG(revenue) as avg_revenue,
  MIN(revenue) as min_revenue,
  MAX(revenue) as max_revenue,
  COUNT(*) as days_in_month,
  PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY revenue) - 
  PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY revenue) as iqr
FROM daily_sales
GROUP BY DATE_TRUNC('month', date)
ORDER BY month;

Вариант для MySQL (нет PERCENTILE_CONT):

WITH monthly_data AS (
  SELECT 
    DATE_FORMAT(date, '%Y-%m') as month,
    revenue,
    ROW_NUMBER() OVER (PARTITION BY DATE_FORMAT(date, '%Y-%m') ORDER BY revenue) as rn,
    COUNT(*) OVER (PARTITION BY DATE_FORMAT(date, '%Y-%m')) as cnt
  FROM daily_sales
)
SELECT 
  month,
  AVG(revenue) as median_revenue
FROM monthly_data
WHERE rn = CEIL((cnt + 1) / 2) OR (cnt % 2 = 0 AND rn IN (FLOOR((cnt + 1) / 2), CEIL((cnt + 1) / 2)))
GROUP BY month
ORDER BY month;

Ключевые моменты:

PERCENTILE_CONT(0.5) — функция для расчета 50-го процентиля (медианы). Работает в PostgreSQL 9.4+.

WITHIN GROUP (ORDER BY revenue) — сортирует значения для расчета процентиля.

Медиана vs Среднее:

  • Если выручка в день: 100, 110, 120, 1000, то среднее = 332.5 (искажено выбросом), медиана = 115 (репрезентативнее)
  • Медиана полезна для выявления типичного дня, в отличие от среднего

IQR (Interquartile Range) — Q3 - Q1. Помогает выявить выбросы. Значение > Q3 + 1.5×IQR считается выбросом.

Применение в бизнесе:

  • Прогнозирование — медиана дневной выручки более надёжна для бюджетирования
  • Аномалия-детекция — день с выручкой > медиана × 2 требует анализа
  • Сегментация дней — выходные vs будни по медиане дневной выручки
SQL: Медиана выручки по месяцам | PrepBro