Использование MEDIAN с GROUP BY

Начиная с MariaDB 10.3.3 существует функция MEDIAN. К сожалению, есть небольшая проблема, когда я пытаюсь использовать его с оператором GROUP BY (в настоящее время используется версия 10.3.9).

Учитывая следующую таблицу:

CREATE TABLE testmed
  (
     id       INT NOT NULL auto_increment,
          PRIMARY KEY(id),
     group_id INT NOT NULL DEFAULT 0,
     score    INT NOT NULL DEFAULT 0
  );

Заполняем его некоторыми данными:

INSERT INTO testmed (group_id, score) 
VALUES (1,1), (1,2), (1,2), (1,2), (1,3), (2,5), (2,7), (2,9), (2,11), (2,11);

Теперь я получаю разные результаты с GROUP BY в запросе и без него:

MariaDB [test]> SELECT group_id, score, MEDIAN(score) OVER (PARTITION BY group_id) FROM testmed;
+----------+-------+--------------------------------------------+
| group_id | score | MEDIAN(score) OVER (PARTITION BY group_id) |
+----------+-------+--------------------------------------------+
|        1 |     1 |                               2.0000000000 |
|        1 |     2 |                               2.0000000000 |
|        1 |     2 |                               2.0000000000 |
|        1 |     2 |                               2.0000000000 |
|        1 |     3 |                               2.0000000000 |
|        2 |     5 |                               9.0000000000 |
|        2 |     7 |                               9.0000000000 |
|        2 |     9 |                               9.0000000000 |
|        2 |    11 |                               9.0000000000 |
|        2 |    11 |                               9.0000000000 |
+----------+-------+--------------------------------------------+
10 rows in set (0.000 sec)
MariaDB [test]> SELECT group_id, score, MEDIAN(score) OVER (PARTITION BY group_id) FROM testmed GROUP BY group_id;
+----------+-------+--------------------------------------------+
| group_id | score | MEDIAN(score) OVER (PARTITION BY group_id) |
+----------+-------+--------------------------------------------+
|        1 |     1 |                               1.0000000000 |
|        2 |     5 |                               5.0000000000 |
+----------+-------+--------------------------------------------+

Первый правильный, но почему он не работает должным образом с GROUP BY. В настоящее время я использую вложение запросов следующим образом:

MariaDB [test]> SELECT * FROM (SELECT group_id, score, MEDIAN(score) OVER (PARTITION BY group_id) FROM testmed) t GROUP BY group_id;
+----------+-------+--------------------------------------------+
| group_id | score | MEDIAN(score) OVER (PARTITION BY group_id) |
+----------+-------+--------------------------------------------+
|        1 |     1 |                               2.0000000000 |
|        2 |     5 |                               9.0000000000 |
+----------+-------+--------------------------------------------+
2 rows in set (0.000 sec)

но так неправильно делать это таким образом.

Как правильно это сделать?


mariadb median
person DevilaN    schedule 31.10.2018    source источник

Ответы (1)


arrow_upward
0
arrow_downward

Ваш второй запрос технически недействителен:

SELECT
    group_id,
    score,
    MEDIAN(score) OVER (PARTITION BY group_id)
FROM testmed
GROUP BY group_id;

Причина, по которой он недействителен, заключается в том, что вы выбираете score, которого нет в предложении GROUP BY. Вопрос здесь в том, какое значение score вы собираетесь использовать в базе данных для каждого group_id? Похоже, что здесь происходит то, что MariaDB произвольно выбирает минимальное значение score. Но поскольку существует только одно значение score, медиана просто возвращает это единственное значение.

Имейте в виду, что аналитические функции оцениваются после GROUP BY агрегирования. Я думаю, что это запрос, который вы намеревались запустить:

SELECT DISTINCT
    group_id,
    MEDIAN(score) OVER (PARTITION BY group_id) score_median
FROM testmed;

Если это не сработает, потому что MariaDB не любит использовать DISTINCT с MEDIAN, вы можете попробовать выполнить подзапрос:

SELECT DISTINCT
    group_id,
    score_median
FROM
(
    SELECT
        group_id,
        MEDIAN(score) OVER (PARTITION BY group_id) score_median
    FROM testmed
) t;
person Tim Biegeleisen    schedule 31.10.2018
comment
Какую версию MariaDB вы используете? Ваш запрос вызывает You have an error in your SQL syntax; ошибку в моем (10.3.9). К сожалению, MEDIAN, похоже, является функцией только окна (а не агрегированной), и это привело меня сюда, чтобы задать вопрос, как другие люди справляются с этим. - person DevilaN; 01.11.2018
comment
@DevilaN Я обновил свой ответ запросом, который может соответствовать тому, что вы действительно пытались сделать. Я не знал, что MEDIAN является только оконной функцией. - person Tim Biegeleisen; 01.11.2018
comment
Ваш ответ кажется логически таким же, как и мой последний пример, который, как мне кажется, немного хитрый способ сделать это. Хотя спасибо за предложение. - person DevilaN; 02.11.2018
comment
@DevilaN - мне удалось 10.3.21. Каково было ваше точное утверждение и полное сообщение об ошибке синтаксиса? - person Rick James; 09.02.2020
comment
@TimBiegeleisen - GROUP BY заметно быстрее, чем DISTINCT. (По крайней мере, в одном тесте, который я провел.) - person Rick James; 09.02.2020
comment
@RickJames: Это была версия 10.3.9. К сожалению для этого вопроса, я перешел на v10.4.x и больше не возникает синтаксическая ошибка. Тем не менее, нет хорошего материала об использовании MEDIAN, поэтому эта часть моего первоначального вопроса актуальна. - person DevilaN; 10.02.2020