Как рассчитать медианное значение в MySQL с помощью простого запроса SQL

Медиана массива чисел – это значение среднего элемента в массиве, при условии, что массив отсортирован. Если массив имеет четное количество элементов, медиана представляет среднее значение двух средних значений в массиве.

Это значение очень популярно, и каждый пытается понять, «в чём половина моей стоимости?». Например, мы получили D (или 80) в нашем последнем тесте в школе, находимся ли мы среди 50% лучших учеников моего класса или нет?

В качестве практического примера давайте рассмотрим процесс извлечения медианного значения из следующего массива школьных тестовых оценок: [55, 80, 95, 100, 99, 70, 60].


Сначала мы отсортируем массив: [55, 80, 95, 100, 99, 70, 60] ===> [55, 60, 70, 80, 95, 99, 100].
Массив содержит 7 элементов, что не является четным числом, поэтому медиана – это элемент (7/2 + 1), то есть 4-й элемент => 80.

Вычисление медианного значения столбца в MySQL

К сожалению, MySQL пока не предлагает встроенную функцию для вычисления медианного значения столбца. Поэтому нам придется создавать запрос самостоятельно.

Предположим, мы хотели бы получить медианное значение из столбца `grades`. Давайте рассмотрим алгоритм, который мы будем использовать для построения запроса:


Получите отсортированный список значений из столбца  `grades` и прикрепите индекс к каждой строке.
Если количество значений в столбце нечетное, найдите значение элемента в середине отсортированного списка.
Если количество значений в столбце четное, найдите значения двух элементов в середине отсортированного списка.
Рассчитайте среднее значение, полученное в (3) и (4) выше.
Вернуть среднее значение в качестве медианного значения.

Так как будет выглядеть такой запрос?

SET @rowindex := -1;

SELECT
   AVG(g.grade)
FROM
   (SELECT @rowindex:=@rowindex + 1 AS rowindex,
           grades.grade AS grade
    FROM grades
    ORDER BY grades.grade) AS g
WHERE
g.rowindex IN (FLOOR(@rowindex / 2) , CEIL(@rowindex / 2));

Давайте сделаем некоторые объяснения:


Давайте начнем с внутреннего подзапроса – подвыбор присваивает @rowindex в качестве инкрементального индекса для каждой выбранной оценки и сортирует оценки.
Как только у нас будет отсортированный список оценок, внешний запрос извлечет средние элементы в массиве. Если массив содержит нечетное количество элементов, оба значения будут одним средним значением.
Затем предложение SELECT внешнего запроса возвращает среднее значение этих двух значений в качестве медианного значения.

Как видите, это не так просто, как запуск MEDIAN (column_name), но это выполнимо. Давайте скрестим пальцы, что MySQL решит добавить эту функциональность во встроенный продукт, что упростит любой запрос, который выбирает среднее значение.


Добавить комментарий

Автору будет очень приятно получить обратную связь.

Комментариев 0