Используйте медиану

Когда Илон Маск заходит в бар, все его посетители становятся в среднем миллиардерами! :)

Эта старая шутка иллюстрирует проблему вычисления среднего значения. Когда нам требуется среднее значение по столбцу, обычно мы рефлекторно используем агрегатную функцию AVG. Но, как ни странно, в большинстве случаев такое решение будет неправильным.

Вот классический пример — посмотрите на список сотрудников:

Какова средняя зарплата в компании? Если использовать популярную функцию AVG, то получим среднее арифметическое — чуть больше миллиона рублей.

И это будет правдой, отдел кадров наверняка не откажется использовать эту статистику для привлечения новых сотрудников.

А вот профсоюз работников скорее всего с такой статистикой не согласится. Ведь если оценивать «по честному», то зарплата в среднем чуть больше ста рублей!

Правильное решение — использовать не среднее арифметическое, а медиану. Медианная зарплата — это цифра, которая делит список сотрудников ровно пополам: половина получает зарплату меньше медианы, а другая половина — больше.

А теперь заново просмотрите все ваши аналитические SELECT’ы — наверняка во многих местах вы почувствуете необходимость заменить среднее арифметическое на медиану.

В SQL вычисление медианы можно выполнить при помощи функции PERCENTILE_CONT. Эта функция рассчитывает процентили, так что для получения медианы нужно указать параметр 0,5.

Конечно, у такого метода есть и недостатки, которые обязательно следует принять во внимание. Их два:

1. Медиана вычисляется гораздо медленнее, чем среднее арифметическое. Если посмотреть на план исполнения этого запроса, сразу станет понятно насколько тяжело серверу даётся вычисление процентилей.
2. Функция PERCENTILE_CONT весьма неудобно оформлена синтаксически. Не как обычная агрегатная функция и даже не как функция от набора строк. Это функция над упорядоченным набором, поэтому использовать её в запросах бывает не очень удобно.

Сразу хочется спросить:

• Что за странный синтаксис: "WITHIN GROUP"?
• Зачем здесь "TOP (1)"?
• Почему в секции "OVER" пустые скобки - разве так можно?
• Как посчитать медиану в старых версиях СУБД, где нет оконных функций?

Да, соглашусь, это неудобно. Хотелось бы просто иметь простую функцию, типа median(), как во многих других языках. Но в SQL пока так.