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

Что такое среднее значение?

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

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

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

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

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

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

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

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

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

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));

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

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

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

Читайте также

Создание пользователя для резервного копирования с правами только для чтения для базы данных MySQL

5 лет назад

Как установить MySQL Workbench на Debian 11

3 года назад

В чем разница между статическим и динамическим SQL?

4 года назад

0 0 голоса

Рейтинг статьи

Подписаться

авторизуйтесь

Уведомить о

новых последующих комментарияхновых ответах на мои комментарии

Я разрешаю использовать свой email и отправлять уведомления о новых комментариях и ответах (вы cможете отказаться от подписки в любое время).

Label

{} [+]

Имя*

Email*

Веб-сайт

**ссылки nofollow

Δ

Label

{} [+]

Имя*

Email*

Веб-сайт

**ссылки nofollow

Δ

1 Комментарий

Старые

Новые Популярные

Межтекстовые Отзывы

Посмотреть все комментарии

Сергей

2 лет назад

Мне кажется, что человек, который понимает язык этой статьи, уже знает ответ на вопрос, в чём разница между статическим и динамическим SQL. А я не понял. Я хотел понять, в чём разница между статическим и динамическим запросом. Яндекс даёт эту статью на первой странице в ответ на запрос «Что такое динамический запрос». Вроде статья должна ответить на этот вопрос, но уровень явно для академиков.

1

Ответить