SQL: Медианная зарплата по департаментам

Решение

Понимание медианы

Медиана — значение, которое делит отсортированный набор пополам:

• Нечётное количество: средний элемент (3, 5, 7, 9, 11) → медиана = 7
• Чётное количество: среднее двух центральных элементов (3, 5, 7, 9) → медиана = (5+7)/2 = 6

В примере:

• IT: [50000, 60000, 70000] (3 человека) → медиана = 60000 (средний элемент)
• Sales: [80000, 90000] (2 человека) → медиана = (80000+90000)/2 = 85000

Решение 1: С использованием PERCENTILE_CONT (рекомендуется)

Это встроенная функция в большинстве БД для расчёта медианы:

SELECT
    department,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees
GROUP BY department
ORDER BY department;

Объяснение:

• PERCENTILE_CONT(0.5) — 50-й процентиль (медиана)
• WITHIN GROUP (ORDER BY salary) — сортировка значений для расчёта
• GROUP BY department — вычисляем медиану в каждой группе

Результат:

department | median_salary
-----------|---------------
IT         | 60000
Sales      | 85000

Почему PERCENTILE_CONT работает:

• Для нечётного кол-ва: возвращает средний элемент
• Для чётного кол-ва: интерполирует значение между двумя средними элементами

Решение 2: С использованием ROW_NUMBER (работает везде)

Для БД, где нет PERCENTILE_CONT:

SELECT
    department,
    AVG(salary) AS median_salary
FROM (
    SELECT
        department,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary) AS row_num,
        COUNT(*) OVER (PARTITION BY department) AS total_count
    FROM employees
) ranked
WHERE
    -- Для нечётного: middle row
    -- Для чётного: два middle rows
    row_num IN (
        FLOOR((total_count + 1) / 2),
        CEIL((total_count + 1) / 2)
    )
GROUP BY department
ORDER BY department;

Детальное объяснение:

1. ROW_NUMBER() — нумерует строки в каждом department'е от 1
2. COUNT(*) OVER (PARTITION BY department) — считает всех в department'е
3. WHERE row_num IN (...) — выбираем нужные строки:
   • Для 3 человек (IT): FLOOR(4/2)=2, CEIL(4/2)=2 → берём 2-ю строку (60000)
   • Для 2 человек (Sales): FLOOR(3/2)=1, CEIL(3/2)=2 → берём 1-ю и 2-ю (80000, 90000)
4. AVG(salary) — усредняем выбранные значения
   • IT: 60000 (одно значение)
   • Sales: (80000+90000)/2 = 85000 (два значения)

Визуально для IT (3 строки):

row_num | salary | FLOOR(4/2)=2 | CEIL(4/2)=2
--------|--------|--------------|------------
1       | 50000  | ✗            | ✗
2       | 60000  | ✓            | ✓  ← берём эту
3       | 70000  | ✗            | ✗

Визуально для Sales (2 строки):

row_num | salary | FLOOR(3/2)=1 | CEIL(3/2)=2
--------|--------|--------------|------------
1       | 80000  | ✓            | ✗  ← берём
2       | 90000  | ✗            | ✓  ← берём
→ AVG = 85000

Решение 3: С использованием NTILE (альтернатива)

SELECT
    department,
    AVG(salary) AS median_salary
FROM (
    SELECT
        department,
        salary,
        NTILE(2) OVER (PARTITION BY department ORDER BY salary) AS quartile
    FROM employees
) ranked
WHERE quartile = 1  -- Берём нижнюю половину и верхнюю половину
GROUP BY department
ORDER BY department;

Как работает NTILE(2):

• Делит данные на 2 равные части
• Для IT (3 строк): 50000 (quartile=1), 60000 (quartile=1), 70000 (quartile=2)
• Для Sales (2 строки): 80000 (quartile=1), 90000 (quartile=2)

Проблема: Для чётного количества этот метод не даст правильную медиану (нужно усредняющее вычисление), поэтому Решение 2 лучше.

Решение 4: Без оконных функций (для старых БД)

SELECT
    e1.department,
    AVG(e1.salary) AS median_salary
FROM employees e1
JOIN (
    -- Подзапрос: найти позицию медианы
    SELECT
        department,
        COUNT(*) as cnt
    FROM employees
    GROUP BY department
) counts ON e1.department = counts.department
WHERE
    -- Условие для выбора строк, участвующих в медиане
    e1.salary >= (
        SELECT MIN(salary)
        FROM (
            SELECT salary
            FROM employees e2
            WHERE e2.department = e1.department
            ORDER BY salary
            LIMIT 1 OFFSET (counts.cnt / 2)
        ) t
    )
    AND e1.salary <= (
        SELECT MAX(salary)
        FROM (
            SELECT salary
            FROM employees e2
            WHERE e2.department = e1.department
            ORDER BY salary
            LIMIT 1 OFFSET ((counts.cnt - 1) / 2)
        ) t
    )
GROUP BY e1.department
ORDER BY e1.department;

Это сложно и медленно, но работает на древних базах данных.

Решение 5: Простое решение для MySQL

MySQL не имеет PERCENTILE_CONT, поэтому используем ROW_NUMBER:

SELECT
    department,
    ROUND(AVG(salary), 2) AS median_salary
FROM (
    SELECT
        department,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary) as rn,
        COUNT(*) OVER (PARTITION BY department) as cnt
    FROM employees
) t
WHERE rn IN (FLOOR((cnt+1)/2), CEIL((cnt+1)/2))
GROUP BY department;

Решение 6: Для PostgreSQL (самый элегантный)

SELECT
    department,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary)::DECIMAL(10,2) AS median_salary
FROM employees
GROUP BY department
ORDER BY department;

Расширенная версия с дополнительной статистикой:

SELECT
    department,
    COUNT(*) as employee_count,
    MIN(salary) as min_salary,
    PERCENTILE_CONT(0.25) WITHIN GROUP (ORDER BY salary) as q1,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) as median,
    PERCENTILE_CONT(0.75) WITHIN GROUP (ORDER BY salary) as q3,
    MAX(salary) as max_salary,
    AVG(salary) as avg_salary
FROM employees
GROUP BY department
ORDER BY department;

Решение 7: Для больших датасетов (оптимизация)

WITH dept_stats AS (
    SELECT
        department,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary) as rn,
        COUNT(*) OVER (PARTITION BY department) as total
    FROM employees
    WHERE department IN ('IT', 'Sales')  -- Фильтр если много department'ов
)
SELECT
    department,
    AVG(salary) as median_salary
FROM dept_stats
WHERE rn IN (FLOOR((total+1)/2), CEIL((total+1)/2))
GROUP BY department
ORDER BY department;

Сравнение методов

Тест на примере

Данные:

IT:    [50000, 60000, 70000] — 3 строки
Sales: [80000, 90000] — 2 строки

Расчёт медианы ROW_NUMBER:

IT:
  total_count = 3
  FLOOR((3+1)/2) = 2, CEIL((3+1)/2) = 2
  Берём row_num = 2 → salary = 60000
  AVG = 60000

Sales:
  total_count = 2
  FLOOR((2+1)/2) = 1, CEIL((2+1)/2) = 2
  Берём row_num IN (1, 2) → salaries = [80000, 90000]
  AVG = 85000

Результат:

department | median_salary
-----------|---------------
IT         | 60000
Sales      | 85000

Рекомендуемое решение для production

PostgreSQL/Oracle/SQL Server:

SELECT
    department,
    PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees
GROUP BY department
ORDER BY department;

MySQL/SQLite:

SELECT
    department,
    ROUND(AVG(salary), 2) AS median_salary
FROM (
    SELECT
        department,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary) as rn,
        COUNT(*) OVER (PARTITION BY department) as cnt
    FROM employees
) t
WHERE rn IN (FLOOR((cnt+1)/2), CEIL((cnt+1)/2))
GROUP BY department
ORDER BY department;

Итоговый чеклист

• PERCENTILE_CONT(0.5) — самый элегантный и быстрый
• ROW_NUMBER() — работает везде, универсален
• Помни про усреднение для чётного количества
• FLOOR и CEIL — магические числа для выбора нужных строк
• На больших датасетах добавь индекс на (department, salary)
• APPROX_PERCENTILE для очень больших данных (Spark, BigQuery)