🤫 Как посчитать медиану в SQL — разберем задачу с собеседования:

На собеседованиях аналитиков часто спрашивают про медиану: “Как её посчитать в SQL без встроенных функций?” Это классика, потому что проверяет понимание сортировки, оконных функций и логики. Сегодня разберём всё по полочкам — от теории до кода.

Сначала разберём, что такое медиана и зачем она нужна: 😁

Что такое медиана — это значение, которое находится посередине в отсортированном массиве чисел. Давайте разберём пару примеров, и вам сразу всё станет ясно.
• Представим массив чисел [5, 3, 1, 10, 8]. Давайте его отсортируем по возрастанию чисел [1, 3, 5, 8, 10] смотрим, какое значение находится посередине списка — правильно, это число 5. Отлично, мы нашли медиану.
• Что если в массиве данных чётное количество чисел, например [5, 3, 1, 10, 8, 1]. Так, давайте отсортируем [1, 1, 3, 5, 8, 10] видим, что середина находится между 3 и 5, тогда остаётся взять среднее между этими числами: (3+5)/2 = 4. Супер, разобрались, как находить медиану в чётных и нечётных массивах!

Теперь давайте разберёмся, а зачем вообще нужна медиана? 💁‍♂

Представим, что мы хотим посчитать ЗП IT-компании, например, и у нас есть вот такие данные: [100тыс, 150тыс, 130тыс, 120тыс, 1млн]. Мы видим, что 1млн рублей выделяется из списка — давайте попробуем посчитать среднее и медиану и увидим, какая разница между ними?
• Среднее: (100тыс + 150тыс + 130тыс + 120тыс + 1млн)/5 = 300тыс.
• Медиана: отсортировано [100тыс, 120тыс, 130тыс, 150тыс, 1млн] — центральное 130тыс.

Вывод: медиана более точно отображает ситуацию, так как 1млн получает директор компании. Именно по этому и нужна медиана — она устойчива к выбросам, в отличие от среднего. Всё зависит от конкретной ситуации, и лучше считать сразу среднее и медиану, отталкиваясь от смысла данных. В аналитике медиана спасает в зарплатах, ценах на жильё или времени доставки — там, где есть аномалии.

Научимся находить медиану в SQL: 😏

• Рассмотрим таблицу зарплат с чётным числом записей (6 сотрудников).

employee_id | salary
------------|----------
1           | 100000
2           | 150000
3           | 130000
4           | 120000
5           | 1000000
6           | 110000

• Пишем sql запрос:

WITH ranked AS (
    SELECT 
        salary,
        ROW_NUMBER() OVER (ORDER BY salary) AS rn,
        COUNT(*) OVER () AS total_count
    FROM salaries
)
SELECT AVG(salary) AS median
FROM ranked
WHERE rn IN (
    FLOOR((total_count + 1) / 2.0), 
    CEIL((total_count + 1) / 2.0)
);

• Посмотрим, что делает CTE:

salary  | rn | total_count
--------|----|-----------
100000  | 1  | 6
110000  | 2  | 6
120000  | 3  | 6
130000  | 4  | 6
150000  | 5  | 6
1000000 | 6  | 6

• Посмотрим, что делают FLOOR и CEIL:

FLOOR((total_count + 1) / 2.0) = FLOOR((6 + 1) / 2.0)  = FLOOR(3.5) = 3 (округление вниз)

CEIL((total_count + 1) / 2.0) = CEIL((6 + 1) / 2.0)  = CEIL(3.5) = 4 (округление вверх)

• Значит, мы берём строки с номерами 3 и 4:

salary  | rn
--------|----
120000  | 3  ← берём
130000  | 4  ← берём

• Мы взяли строки с rn=3 и rn=4:

Результат:
----------------------------------------------------------------------
median = AVG(120000, 130000) = (120000 + 130000) / 2 = 250000 / 2 = 125000

**
Итог:**
Медиана устойчива к выбросам, а в SQL её легко посчитать через CTE с ROW_NUMBER.

🍸 Если вы нашли пост для себя полезным, то накидывайте реакций, чтобы я понимал, что вам эта тема интересна!
❤️Поддержать канал бустами, чтобы у автора появился дополнительный функционал можно - здесь (это бесплатно и доступно с подпиской telegram premium)

❓ Использовали медиану в SQL? Для каких метрик — зарплаты, продажи или что-то другое? Делитесь в комментариях!
✔️ Подпишитесь на канал, чтобы не пропустить следующие посты.

Сделал сайт - оцените:
🚬 Вопросы, обучение, консультации

@@dima_sqlit

Ссылки