Идеи могут быть обезврежены только идеями (O. Бальзак).

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

1 мин для чтения

Главное меню » Базы данных » База данных MySQL » Как рассчитать медианное значение в MySQL с помощью простого запроса SQL

(1 оценок, среднее: 5,00 из 5)

Загрузка...

07.04.2019

Как рассчитать медианное значение в 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 решит добавить эту функциональность во встроенный продукт, что упростит любой запрос, который выбирает среднее значение.

Если вы нашли ошибку, пожалуйста, выделите фрагмент текста и нажмите Ctrl+Enter.