12.19.2 GROUP BY Modifiers — MySQL 5.7

12.19.2 Модификаторы GROUP BY

Оператор GROUP BY позволяет использовать модификатор WITH ROLLUP, который добавляет в сводную информацию дополнительные строки, представляющие агрегации более высокого уровня (суперагрегаты). ROLLUP Таким образом, вы можете ответить на вопросы на нескольких уровнях анализа с помощью одного запроса. Например, ROLLUP может использоваться для поддержки операций OLAP (Online Analytical Processing).

Предположим, что таблица sales содержит столбцы year, country, product и profit для записи прибыльности продаж:

CREATE TABLE sales
(
    year    INT,
    country VARCHAR(20),
    product VARCHAR(32),
    profit  INT
);

Для подсчета итоговой прибыли по годам используйте простой запрос GROUP BY следующим образом:

mysql> SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year;
+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
+------+--------+

Вывод показывает общую (агрегированную) прибыль для каждого года. Чтобы определить также общую прибыль за все годы, необходимо суммировать значения вручную или выполнить дополнительный запрос. Или вы можете использовать ROLLUP, который предоставляет оба уровня анализа с помощью одного запроса. Добавление модификатора WITH ROLLUP к оператору GROUP BY заставляет запрос создавать ещё одну строку (суперагрегат), которая показывает общую сумму по всем значениям года:

mysql> SELECT year, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP;
+------+--------+
| year | profit |
+------+--------+
| 2000 |   4525 |
| 2001 |   3010 |
| NULL |   7535 |
+------+--------+

Значение NULL в столбце year определяет строку суперагрегата с общей суммой.

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

Например, без ROLLUP сводная информация для таблицы sales по столбцам year, country и product может выглядеть следующим образом, где вывод показывает значения сводной информации только на уровне анализа год/страна/товар:

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2000 | India   | Calculator |    150 |
| 2000 | India   | Computer   |   1200 |
| 2000 | USA     | Calculator |     75 |
| 2000 | USA     | Computer   |   1500 |
| 2001 | Finland | Phone      |     10 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   2700 |
| 2001 | USA     | TV         |    250 |
+------+---------+------------+--------+

С добавлением ROLLUP запрос создает несколько дополнительных строк:

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product WITH ROLLUP;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2000 | Finland | NULL       |   1600 |
| 2000 | India   | Calculator |    150 |
| 2000 | India   | Computer   |   1200 |
| 2000 | India   | NULL       |   1350 |
| 2000 | USA     | Calculator |     75 |
| 2000 | USA     | Computer   |   1500 |
| 2000 | USA     | NULL       |   1575 |
| 2000 | NULL    | NULL       |   4525 |
| 2001 | Finland | Phone      |     10 |
| 2001 | Finland | NULL       |     10 |
| 2001 | USA     | Calculator |     50 |
| 2001 | USA     | Computer   |   2700 |
| 2001 | USA     | TV         |    250 |
| 2001 | USA     | NULL       |   3000 |
| 2001 | NULL    | NULL       |   3010 |
| NULL | NULL    | NULL       |   7535 |
+------+---------+------------+--------+

Теперь вывод содержит сводную информацию на четырёх уровнях анализа, а не только на одном:

После каждой группы строк для определенного года и страны появляется дополнительная строка суперагрегата со сводной суммой по всем товарам. В этих строках столбец product имеет значение NULL.
После каждой группы строк для определенного года появляется дополнительная строка суперагрегата со сводной суммой по всем странам и товарам. В этих строках столбцы country и products имеют значение NULL.
Наконец, после всех остальных строк появляется дополнительная строка суперагрегата с общей сводной суммой по всем годам, странам и товарам. В этой строке столбцы year, country и products имеют значение NULL.

Указатели NULL в каждой строке суперагрегата создаются при отправке строки клиенту. Сервер рассматривает столбцы, указанные в операторе GROUP BY, начиная с самого левого изменённого столбца. Для любого столбца в наборе результатов, имя которого совпадает с одним из этих имён, его значение устанавливается в NULL. (Если вы указываете столбцы группировки по номеру столбца, сервер определяет, какие столбцы установить в NULL по номеру.)

Поскольку значения NULL в строках суперагрегатов размещаются в наборе результатов на столь поздней стадии обработки запроса, вы можете проверять их как значения NULL только в списке выбора или операторе HAVING. Вы не можете проверять их как значения NULL в условиях объединения или в операторе WHERE для определения строк для выбора. Например, вы не можете добавить WHERE product IS NULL в запрос, чтобы исключить из вывода все, кроме строк суперагрегатов.

Значения NULL отображаются как значения NULL на стороне клиента и могут проверяться как таковые с помощью любого программируемого интерфейса MySQL-клиента. Однако на этом этапе вы не можете отличить, представляет ли значение NULL обычное сгруппированное значение или значение суперагрегата. В MySQL 8.0 можно использовать функцию для проверки отличия.

Дополнительные соображения при использовании ROLLUP

Ниже приведены некоторые особенности, специфичные для реализации ROLLUP в MySQL.

При использовании ROLLUP нельзя также использовать оператор ORDER BY для сортировки результатов. Другими словами, ROLLUP и ORDER BY являются взаимоисключающими в MySQL. Тем не менее, вы всё ещё имеете некоторое управление порядком сортировки. Чтобы обойти ограничение, препятствующее использованию ROLLUP с ORDER BY и добиться конкретного порядка сортировки сгруппированных результатов, сгенерируйте набор сгруппированных результатов как производную таблицу и примените ORDER BY к ней. Например:

mysql> SELECT * FROM
         (SELECT year, SUM(profit) AS profit
         FROM sales GROUP BY year WITH ROLLUP) AS dt
       ORDER BY year DESC;
+------+--------+
| year | profit |
+------+--------+
| 2001 |   3010 |
| 2000 |   4525 |
| NULL |   7535 |
+------+--------+

В этом случае строки суперагрегатов сортируются вместе со строками, из которых они рассчитываются, и их расположение зависит от порядка сортировки (в начале для возрастающей сортировки, в конце для убывающей сортировки).

LIMIT может быть использован для ограничения количества строк, возвращаемых клиенту. LIMIT применяется после ROLLUP, поэтому ограничение применяется к дополнительным строкам, добавленным функцией ROLLUP. Например:

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product WITH ROLLUP
       LIMIT 5;
+------+---------+------------+--------+
| year | country | product    | profit |
+------+---------+------------+--------+
| 2000 | Finland | Computer   |   1500 |
| 2000 | Finland | Phone      |    100 |
| 2000 | Finland | NULL       |   1600 |
| 2000 | India   | Calculator |    150 |
| 2000 | India   | Computer   |   1200 |
+------+---------+------------+--------+

Использование LIMIT с ROLLUP может привести к результатам, которые сложнее интерпретировать, потому что контекста для понимания строк суперагрегатов меньше.

Расширение MySQL допускает указание столбца, отсутствующего в списке GROUP BY, в списке выбора. (Для получения информации о не агрегированных столбцах и GROUP BY см. Раздел 12.19.3, «Обработка GROUP BY в MySQL».) В этом случае сервер свободен выбирать любое значение из этого не агрегированного столбца в строках сводной информации, включая дополнительные строки, добавленные с помощью WITH ROLLUP. Например, в следующем запросе, country — это не агрегированный столбец, который не включён в список GROUP BY, и выбранные значения для этого столбца являются не детерминированными:

mysql> SELECT year, country, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP;
+------+---------+--------+
| year | country | profit |
+------+---------+--------+
| 2000 | India   |   4525 |
| 2001 | USA     |   3010 |
| NULL | USA     |   7535 |
+------+---------+--------+

Это поведение разрешено, когда режим SQL ONLY_FULL_GROUP_BY не включен. Если этот режим включён, сервер отклоняет запрос как неправильный, потому что country не указан в операторе GROUP BY. С включённым режимом ONLY_FULL_GROUP_BY вы всё ещё можете выполнить запрос, используя функцию ANY_VALUE() для столбцов с недетерминированными значениями:

mysql> SELECT year, ANY_VALUE(country) AS country, SUM(profit) AS profit
       FROM sales
       GROUP BY year WITH ROLLUP;
+------+---------+--------+
| year | country | profit |
+------+---------+--------+
| 2000 | India   |   4525 |
| 2001 | USA     |   3010 |
| NULL | USA     |   7535 |
+------+---------+--------+