Как рассчитать значение медианы в SQL без функции?

Расчёт медианы в SQL без встроенных функций

Медиана — это средний элемент отсортированного набора данных. Её расчёт без встроенной функции требует скользящего окна и логики для выбора среднего значения.

Метод 1: Использование ROW_NUMBER() и нумерации

Это универсальный и понятный метод для всех СУБД (PostgreSQL, MySQL, SQL Server, Oracle):

-- Для нечётного количества строк берём центральное значение
-- Для чётного — берём среднее двух центральных

WITH ranked AS (
  SELECT 
    value,
    ROW_NUMBER() OVER (ORDER BY value) AS rn,
    COUNT(*) OVER () AS total_count
  FROM table_name
  WHERE value IS NOT NULL
)
SELECT 
  AVG(value) AS median
FROM ranked
WHERE rn IN (
  FLOOR((total_count + 1) / 2),
  CEIL((total_count + 1) / 2)
);

Как это работает

Данные: [3, 1, 4, 1, 5, 9, 2, 6]
Отсортировано: [1, 1, 2, 3, 4, 5, 6, 9]
Всего: 8 элементов

FLOOR((8 + 1) / 2) = 4
CEIL((8 + 1) / 2) = 5

Берём 4-й и 5-й элементы: 3 и 4
Медиана = (3 + 4) / 2 = 3.5

Пример на реальных данных

CREATE TABLE sales (
  id INT,
  amount DECIMAL(10, 2),
  region VARCHAR(50)
);

INSERT INTO sales VALUES
(1, 100.50, 'North'),
(2, 200.00, 'North'),
(3, 150.75, 'North'),
(4, 300.00, 'North'),
(5, 250.50, 'North');

-- Расчёт медианы для North region
WITH ranked AS (
  SELECT 
    amount,
    ROW_NUMBER() OVER (ORDER BY amount) AS rn,
    COUNT(*) OVER () AS total_count
  FROM sales
  WHERE region = 'North' AND amount IS NOT NULL
)
SELECT 
  'North' AS region,
  AVG(amount) AS median_amount
FROM ranked
WHERE rn IN (
  FLOOR((total_count + 1) / 2),
  CEIL((total_count + 1) / 2)
);

-- Output: region | median_amount
-- North   | 150.75

Метод 2: С разбивкой по группам

Если нужна медиана по категориям (например, по регионам):

WITH ranked AS (
  SELECT 
    region,
    amount,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount) AS rn,
    COUNT(*) OVER (PARTITION BY region) AS total_count
  FROM sales
  WHERE amount IS NOT NULL
)
SELECT 
  region,
  AVG(amount) AS median_amount
FROM ranked
WHERE rn IN (
  FLOOR((total_count + 1) / 2),
  CEIL((total_count + 1) / 2)
)
GROUP BY region
ORDER BY region;

Результат

region | median_amount
-------|---------------
East   | 175.50
North  | 200.00
South  | 150.00
West   | 225.75

Метод 3: Через PERCENTILE (для PostgreSQL)

Если ваша БД поддерживает оконные функции PERCENTILE_CONT:

-- PostgreSQL 9.4+
SELECT 
  region,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY amount) AS median_amount
FROM sales
WHERE amount IS NOT NULL
GROUP BY region;

-- Это эквивалентно медиане
-- PERCENTILE_CONT(0.5) = медиана (50-й перцентиль)

Метод 4: Альтернативный способ с LIMIT (для MySQL)

Этот способ используется, когда данные маленькие:

-- Для нечётного количества
SELECT 
  SUBSTRING_INDEX(
    SUBSTRING_INDEX(
      GROUP_CONCAT(amount ORDER BY amount),
      ',',
      FLOOR(COUNT(*) / 2) + 1
    ),
    ',',
    -1
  ) AS median_amount
FROM sales;

НО этот метод медленный для больших данных, используй только для демонстрации.

Метод 5: Полное решение с вычислением медианы

Если нужна одна медиана для всей таблицы:

WITH sorted AS (
  SELECT 
    value,
    ROW_NUMBER() OVER (ORDER BY value) AS row_num,
    COUNT(*) OVER () AS total_rows
  FROM data_table
  WHERE value IS NOT NULL
),
median_positions AS (
  SELECT 
    value,
    row_num,
    total_rows,
    CASE 
      WHEN total_rows % 2 = 1 THEN (total_rows + 1) / 2
      ELSE total_rows / 2
    END AS lower_median_pos,
    CASE 
      WHEN total_rows % 2 = 1 THEN (total_rows + 1) / 2
      ELSE total_rows / 2 + 1
    END AS upper_median_pos
  FROM sorted
)
SELECT 
  AVG(value) AS median
FROM median_positions
WHERE row_num IN (lower_median_pos, upper_median_pos);

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

Практический пример: Анализ зарплат

CREATE TABLE employees (
  id INT,
  name VARCHAR(100),
  salary DECIMAL(10, 2),
  department VARCHAR(50)
);

INSERT INTO employees VALUES
(1, 'Alice', 50000, 'Engineering'),
(2, 'Bob', 60000, 'Engineering'),
(3, 'Charlie', 55000, 'Engineering'),
(4, 'Diana', 70000, 'Sales'),
(5, 'Eve', 75000, 'Sales'),
(6, 'Frank', 52000, 'HR'),
(7, 'Grace', 58000, 'HR'),
(8, 'Henry', 61000, 'HR');

-- Медиана зарплаты по отделам
WITH ranked AS (
  SELECT 
    department,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary) AS rn,
    COUNT(*) OVER (PARTITION BY department) AS dept_count
  FROM employees
)
SELECT 
  department,
  ROUND(AVG(salary), 2) AS median_salary,
  COUNT(*) AS employee_count
FROM ranked
WHERE rn IN (
  FLOOR((dept_count + 1) / 2),
  CEIL((dept_count + 1) / 2)
)
GROUP BY department
ORDER BY median_salary DESC;

-- Output:
-- department  | median_salary | employee_count
-- Sales       | 72500.00      | 2
-- Engineering | 55000.00      | 3
-- HR          | 59500.00      | 3

Оптимизация для больших данных

Для таблиц с миллионами строк:

-- Добавьте индекс на столбец, по которому считаете медиану
CREATE INDEX idx_amount ON sales(amount);

-- Используйте PARTITION для больших таблиц
WITH ranked AS (
  SELECT 
    DATE_TRUNC('month', created_date) AS month,
    amount,
    ROW_NUMBER() OVER (
      PARTITION BY DATE_TRUNC('month', created_date) 
      ORDER BY amount
    ) AS rn,
    COUNT(*) OVER (
      PARTITION BY DATE_TRUNC('month', created_date)
    ) AS cnt
  FROM sales
  WHERE amount IS NOT NULL
    AND created_date >= CURRENT_DATE - INTERVAL '12 months'
)
SELECT 
  month,
  AVG(amount) AS median_amount
FROM ranked
WHERE rn IN (FLOOR((cnt + 1) / 2), CEIL((cnt + 1) / 2))
GROUP BY month
ORDER BY month DESC;

Вывод

Лучший подход в большинстве случаев:

1. PostgreSQL/Oracle: используй встроенную PERCENTILE_CONT(0.5)
2. SQL Server: используй встроенную PERCENTILE_CONT(0.5) WITHIN GROUP
3. MySQL: используй ROW_NUMBER() метод с CTE
4. Если нет встроенной функции: ROW_NUMBER() + PARTITION BY — универсальное решение

Помни: медиана считается только для не-NULL значений, добавляй WHERE value IS NOT NULL в CTE.