На собеседованиях аналитиков часто спрашивают про медиану: "Как её посчитать в 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