Используйте медиану
Когда Илон Маск заходит в бар, все его посетители становятся в среднем миллиардерами! :)
Эта старая шутка иллюстрирует проблему вычисления среднего значения. Когда нам требуется среднее значение по столбцу, обычно мы рефлекторно используем агрегатную функцию AVG. Но, как ни странно, в большинстве случаев такое решение будет неправильным.
Вот классический пример — посмотрите на список сотрудников:
Какова средняя зарплата в компании? Если использовать популярную функцию AVG, то получим среднее арифметическое — чуть больше миллиона рублей.
И это будет правдой, отдел кадров наверняка не откажется использовать эту статистику для привлечения новых сотрудников.
А вот профсоюз работников скорее всего с такой статистикой не согласится. Ведь если оценивать «по честному», то зарплата в среднем чуть больше ста рублей!
Правильное решение — использовать не среднее арифметическое, а медиану. Медианная зарплата — это цифра, которая делит список сотрудников ровно пополам: половина получает зарплату меньше медианы, а другая половина — больше.
А теперь заново просмотрите все ваши аналитические SELECT’ы — наверняка во многих местах вы почувствуете необходимость заменить среднее арифметическое на медиану.
В SQL вычисление медианы можно выполнить при помощи функции PERCENTILE_CONT. Эта функция рассчитывает процентили, так что для получения медианы нужно указать параметр 0,5.
Конечно, у такого метода есть и недостатки, которые обязательно следует принять во внимание. Их два:
- Медиана вычисляется гораздо медленнее, чем среднее арифметическое. Если посмотреть на план исполнения этого запроса, сразу станет понятно насколько тяжело серверу даётся вычисление процентилей.
- Функция PERCENTILE_CONT весьма неудобно оформлена синтаксически. Не как обычная агрегатная функция и даже не как функция от набора строк. Это функция над упорядоченным набором, поэтому использовать её в запросах бывает не очень удобно.
Сразу хочется спросить:
- Что за странный синтаксис: "WITHIN GROUP"?
- Зачем здесь "TOP (1)"?
- Почему в секции "OVER" пустые скобки - разве так можно?
- Как посчитать медиану в старых версиях СУБД, где нет оконных функций?
Да, соглашусь, это неудобно. Хотелось бы просто иметь простую функцию, типа median(), как во многих других языках. Но в SQL пока так.
Vkontakte
Twitter
Одноклассники
