Web-разработчик в дебрях

Правильный подсчет среднего значения в выборке

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

Подсчет медианы будет очень полезен во многих случаях: средний платеж от клиента, среднее время активности услуги клиента, средний возраст, среднее время проведенное на сайте и т.д. Наверняка все замечали, что в небольшой выборке есть одно или несколько значений которые разительно отличаются от средней «температуры по больнице». К примеру, у Вас было 19 посетителей на сайте, которые просматривали главную страницу 2 минуты и 1 посетитель который просмотривал страницу 2 часа. В случае, если высчитывать среднее значение, то мы получим значение равное 7,9 минут. Что, согласитесь, достаточное странное значение. Ведь гораздо правильнее сказать, что среднее значение и есть 2 минуты, а посетителя, заснувшнего перед монитором, просто вычеркнуть. Для этого и нужно использовать подсчет медианы.

Вдаваться в теорию статистики я не буду, т.к. теоретическую часть легко найти на просторах интернета, а вот готовое решение в виде SQL запроса для MySQL уже гораздо сложнее. Поэтому без лишних слов Вам его представляю:

К примеру у нас таблица `mytable` с одним единственным полем `money`, в котором хранятся суммы и нам нужно подсчитать медиану сумм. Запрос для подсчета будет таким:

1
2
3
4
5

SELECT x.money AS median, COUNT(y.money) AS COUNT
FROM mytable x CROSS JOIN mytable y
GROUP BY x.money
HAVING (SUM(CASE WHEN y.money <= x.money THEN 1 ELSE 0 END) >= (COUNT(*) + 1) /2)
AND (SUM(CASE WHEN y.money >= x.money THEN 1 ELSE 0 END) >= COUNT(*) /2 + 1);

Достаточно просто, не так ли? =)

В первом столбце выборки мы получаем как раз значение медианы, а во втором сумму всех затронутых значений из поля `money`.

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

Метки: mysql, строки и числа