Используйте медиану
Когда Илон Маск заходит в бар, все его посетители становятся в среднем миллиардерами! :)
Эта старая шутка иллюстрирует проблему вычисления среднего значения. Когда нам требуется среднее значение по столбцу, обычно мы рефлекторно используем агрегатную функцию AVG. Но, как ни странно, в большинстве случаев такое решение будет неправильным.
Вот классический пример — посмотрите на список сотрудников:
![](/project/1036/post/22053/image/41699/imagesprojects361036ue05bi350f3d0icb.webp?1664971685786)
Какова средняя зарплата в компании? Если использовать популярную функцию AVG, то получим среднее арифметическое — чуть больше миллиона рублей.
![](/project/1036/post/22053/image/41700/imagesprojects361036qjwt23350f3d2j42.webp?1664971702000)
И это будет правдой, отдел кадров наверняка не откажется использовать эту статистику для привлечения новых сотрудников.
А вот профсоюз работников скорее всего с такой статистикой не согласится. Ведь если оценивать «по честному», то зарплата в среднем чуть больше ста рублей!
![](/project/1036/post/22053/image/41704/imagesprojects36103666392n350f4a70eb_original.webp?1664974454528)
Правильное решение — использовать не среднее арифметическое, а медиану. Медианная зарплата — это цифра, которая делит список сотрудников ровно пополам: половина получает зарплату меньше медианы, а другая половина — больше.
![](/project/1036/post/22053/image/41701/imagesprojects3610367kihf2350f3d7hcb.webp?1664971741367)
А теперь заново просмотрите все ваши аналитические SELECT’ы — наверняка во многих местах вы почувствуете необходимость заменить среднее арифметическое на медиану.
В SQL вычисление медианы можно выполнить при помощи функции PERCENTILE_CONT. Эта функция рассчитывает процентили, так что для получения медианы нужно указать параметр 0,5.
![](/project/1036/post/22053/image/41702/imagesprojects361036jswlmj350f3da24c.webp?1664971759257)
Конечно, у такого метода есть и недостатки, которые обязательно следует принять во внимание. Их два:
- Медиана вычисляется гораздо медленнее, чем среднее арифметическое. Если посмотреть на план исполнения этого запроса, сразу станет понятно насколько тяжело серверу даётся вычисление процентилей.
- Функция PERCENTILE_CONT весьма неудобно оформлена синтаксически. Не как обычная агрегатная функция и даже не как функция от набора строк. Это функция над упорядоченным набором, поэтому использовать её в запросах бывает не очень удобно.
Сразу хочется спросить:
- Что за странный синтаксис: "WITHIN GROUP"?
- Зачем здесь "TOP (1)"?
- Почему в секции "OVER" пустые скобки - разве так можно?
- Как посчитать медиану в старых версиях СУБД, где нет оконных функций?
Да, соглашусь, это неудобно. Хотелось бы просто иметь простую функцию, типа median(), как во многих других языках. Но в SQL пока так.