Spec-Zone.ru › MySQL 9.2

14.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 обычное сгруппированное значение или значение супер-агрегации. Для проверки различия используйте функцию GROUPING(), описанную позже.

Для запросов GROUP BY ... WITH ROLLUP, чтобы проверить, представляют ли значения NULL в результате значения супер-агрегации, функция GROUPING() доступна для использования в списке выбора, клаузе HAVING и клаузе ORDER BY. Например, GROUPING(year) возвращает 1, когда NULL в столбце year встречается в строке супер-агрегации, и 0 в противном случае. Аналогично, GROUPING(country) и GROUPING(product) возвращают 1 для супер-агрегированных значений NULL в столбцах country и product соответственно:

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

Вместо непосредственного отображения результатов GROUPING(), вы можете использовать GROUPING() для подстановки меток для значений супер-агрегированных NULL:

mysql> SELECT
         IF(GROUPING(year), 'All years', year) AS year,
         IF(GROUPING(country), 'All countries', country) AS country,
         IF(GROUPING(product), 'All products', product) AS 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       | All products |   1600 |
| 2000      | India         | Calculator   |    150 |
| 2000      | India         | Computer     |   1200 |
| 2000      | India         | All products |   1350 |
| 2000      | USA           | Calculator   |     75 |
| 2000      | USA           | Computer     |   1500 |
| 2000      | USA           | All products |   1575 |
| 2000      | All countries | All products |   4525 |
| 2001      | Finland       | Phone        |     10 |
| 2001      | Finland       | All products |     10 |
| 2001      | USA           | Calculator   |     50 |
| 2001      | USA           | Computer     |   2700 |
| 2001      | USA           | TV           |    250 |
| 2001      | USA           | All products |   3000 |
| 2001      | All countries | All products |   3010 |
| All years | All countries | All products |   7535 |
+-----------+---------------+--------------+--------+

При нескольких аргументах выражений GROUPING() возвращает результат, представляющий собой битовую маску, которая объединяет результаты для каждого выражения, где младший бит соответствует результату для правого выражения. Например, GROUPING(year, country, product) оценивается следующим образом:

  result for GROUPING(product)
+ result for GROUPING(country) << 1
+ result for GROUPING(year) << 2

Результат такой функции GROUPING() не равен нулю, если любое из выражений представляет собой супер-агрегированное NULL, поэтому вы можете вернуть только строки супер-агрегации и отфильтровать обычные сгруппированные строки следующим образом:

mysql> SELECT year, country, product, SUM(profit) AS profit
       FROM sales
       GROUP BY year, country, product WITH ROLLUP
       HAVING GROUPING(year, country, product) <> 0;
+------+---------+---------+--------+
| year | country | product | profit |
+------+---------+---------+--------+
| 2000 | Finland | NULL    |   1600 |
| 2000 | India   | NULL    |   1350 |
| 2000 | USA     | NULL    |   1575 |
| 2000 | NULL    | NULL    |   4525 |
| 2001 | Finland | NULL    |     10 |
| 2001 | USA     | NULL    |   3000 |
| 2001 | NULL    | NULL    |   3010 |
| NULL | NULL    | NULL    |   7535 |
+------+---------+---------+--------+

Таблица sales не содержит значений NULL, поэтому все значения NULL в результатах ROLLUP представляют собой супер-агрегированные значения. Когда набор данных содержит значения NULL, сводки ROLLUP могут содержать значения NULL не только в строках супер-агрегации, но и в обычных сгруппированных строках. GROUPING() позволяет их различать. Предположим, что таблица t1 содержит простой набор данных с двумя факторами группировки для набора значений количества, где NULL указывает что-то вроде “другое” или “неизвестно”:

mysql> SELECT * FROM t1;
+------+-------+----------+
| name | size  | quantity |
+------+-------+----------+
| ball | small |       10 |
| ball | large |       20 |
| ball | NULL  |        5 |
| hoop | small |       15 |
| hoop | large |        5 |
| hoop | NULL  |        3 |
+------+-------+----------+

Простая операция ROLLUP производит эти результаты, в которых трудно отличить значения NULL в строках супер-агрегации от значений NULL в обычных сгруппированных строках:

mysql> SELECT name, size, SUM(quantity) AS quantity
       FROM t1
       GROUP BY name, size WITH ROLLUP;
+------+-------+----------+
| name | size  | quantity |
+------+-------+----------+
| ball | NULL  |        5 |
| ball | large |       20 |
| ball | small |       10 |
| ball | NULL  |       35 |
| hoop | NULL  |        3 |
| hoop | large |        5 |
| hoop | small |       15 |
| hoop | NULL  |       23 |
| NULL | NULL  |       58 |
+------+-------+----------+

Использование GROUPING() для замены меток для значений супер-агрегированных NULL делает результат более интерпретируемым:

mysql> SELECT
         IF(GROUPING(name) = 1, 'All items', name) AS name,
         IF(GROUPING(size) = 1, 'All sizes', size) AS size,
         SUM(quantity) AS quantity
       FROM t1
       GROUP BY name, size WITH ROLLUP;
+-----------+-----------+----------+
| name      | size      | quantity |
+-----------+-----------+----------+
| ball      | NULL      |        5 |
| ball      | large     |       20 |
| ball      | small     |       10 |
| ball      | All sizes |       35 |
| hoop      | NULL      |        3 |
| hoop      | large     |        5 |
| hoop      | small     |       15 |
| hoop      | All sizes |       23 |
| All items | All sizes |       58 |
+-----------+-----------+----------+

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

В данном обсуждении перечислены некоторые особенности реализации ROLLUP в MySQL.

ORDER BY и ROLLUP могут использоваться вместе, что позволяет применять ORDER BY и GROUPING() для достижения определенного порядка сортировки сгруппированных результатов. Например: 

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

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

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 см. в Разделе 14.19.3, «Обработка GROUP BY в MySQL».) В этом случае сервер свободен выбрать любое значение из этого столбца в строках со сводными агрегатными значениями, что включает и дополнительные строки, добавленные WITH ROLLUP. Например, в следующем запросе, country — это столбец, не участвующий в агрегировании, и значения для этого столбца не детерминированы: 

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 |
+------+---------+--------+

Столбец rollup не может использоваться в качестве аргумента для функции MATCH() (и отклоняется с ошибкой), за исключением случаев вызова в предложении WHERE. Дополнительную информацию см. в Разделе 14.9, «Функции полнотекстового поиска».

© 2025 Oracle
Licensed under the GPLv2 License.
https://docs.oracle.com/cd/E17952_01/mysql-8.4-en/group-by-modifiers.html

Spec-Zone.ru

Настройки Оффлайн Что нового Помощь О нас
Spec-Zone .ru
спецификации, руководства, описания, API