SQLinfo.ru - Все о MySQL

MySQL 8.0: функция GROUPING

Дата: 5.05.2017

Данная статья является переводом статьи Chaithra Gopalareddy.

Начиная с MySQL 8.0.1, сервер поддерживает SQL-функцию GROUPING. Эта функция используется для того, чтобы при использовании модификатора ROLLUP отличать NULL в строках с итоговыми/подытоговыми значениями от NULL в обычных строках.

Ведение

Сервер MySQL поддерживает модификатор ROLLUP при выполнении группирующих запросов. Ниже пример его использования:

mysql> create table t1 (a integer, b integer, c integer);
Query OK, 0 rows affected (0.05 sec)
 
mysql> insert into t1 values (111,11,11);
Query OK, 1 row affected (0.01 sec)
 
mysql> insert into t1 values (222,22,22);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into t1 values (111,12,12);
Query OK, 1 row affected (0.00 sec)
 
mysql> insert into t1 values (222,23,23);
Query OK, 1 row affected (0.01 sec)
 
mysql> select * from t1;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|  111 |   11 |   11 |
|  222 |   22 |   22 |
|  111 |   12 |   12 |
|  222 |   23 |   23 |
+------+------+------+
4 rows in set (0.00 sec)
 
mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a,b WITH ROLLUP;
+------+------+------+
| a    | b    | SUM  |
+------+------+------+
|  111 |   11 |   11 |
|  111 |   12 |   12 |
|  111 | NULL |   23 |
|  222 |   22 |   22 |
|  222 |   23 |   23 |
|  222 | NULL |   45 |
| NULL | NULL |   68 |
+------+------+------+
7 rows in set (0.00 sec)
 

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

Давайте добавим значения NULL в исходные данные таблицы:


mysql> INSERT INTO t1 values (1111,NULL,112);
Query OK, 1 row affected (0.00 sec)
 
mysql> INSERT INTO t1 values (NULL,112,NULL);
Query OK, 1 row affected (0.00 sec)
 
mysql> select * from t1;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|  111 |   11 |   11 |
|  222 |   22 |   22 |
|  111 |   12 |   12 |
|  222 |   23 |   23 |
| 1111 | NULL |  112 |
| NULL |  112 | NULL |
+------+------+------+
6 rows in set (0.00 sec)
 

Теперь при использовании модификатора ROLLUP становится сложно понять - какие значения NULL относятся к обычным сгруппированным значениям, а какие указывают на подытоги и заключительное итоговое значение:


mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a,b WITH ROLLUP;
+------+------+------+
| a    | b    | SUM  |
+------+------+------+
| NULL |  112 | NULL |
| NULL | NULL | NULL |
|  111 |   11 |   11 |
|  111 |   12 |   12 |
|  111 | NULL |   23 |
|  222 |   22 |   22 |
|  222 |   23 |   23 |
|  222 | NULL |   45 |
| 1111 | NULL |  112 |
| 1111 | NULL |  112 |
| NULL | NULL |  180 |
+------+------+------+
11 rows in set (0.01 sec)
 

Что нового в MySQL-8.0.1

В приведенном выше примере можно использовать функцию GROUPING, чтобы отличать NULL, добавляемые ROLLUP, от NULL, относящихся к сгруппированным значениям. Функция GROUPING, использующая в качестве аргумента имя поля, возвращает 1, если значение NULL в этом поле является результатом действия ROLLUP. В противном случае возвращается 0.


mysql> SELECT a, b, SUM(c) as SUM, GROUPING(a), GROUPING(b) FROM t1 GROUP BY a,b WITH ROLLUP;
+------+------+------+-------------+-------------+
| a    | b    | SUM  | GROUPING(a) | GROUPING(b) |
+------+------+------+-------------+-------------+
| NULL |  112 | NULL |           0 |           0 |
| NULL | NULL | NULL |           0 |           1 |
|  111 |   11 |   11 |           0 |           0 |
|  111 |   12 |   12 |           0 |           0 |
|  111 | NULL |   23 |           0 |           1 |
|  222 |   22 |   22 |           0 |           0 |
|  222 |   23 |   23 |           0 |           0 |
|  222 | NULL |   45 |           0 |           1 |
| 1111 | NULL |  112 |           0 |           0 |
| 1111 | NULL |  112 |           0 |           1 |
| NULL | NULL |  180 |           1 |           1 |
+------+------+------+-------------+-------------+
11 rows in set (0.01 sec)
 

Как видно из примера, GROUPING(b) возвращает 1 только для тех строк, у которых в поле b стоит NULL, являющийся результатом действия ROLLUP (иными словами, для строк с подытогом).

Другой способ использования GROUPING - указать в качестве аргумента несколько столбцов. Тогда GROUPING вернет целое число, полученное из битовой маски, имеющей 1 у тех аргументов, для которых GROUPING(argument) равен 1. Например:


mysql> SELECT a, b, SUM(c) as SUM, GROUPING(a,b) FROM t1 GROUP BY a,b WITH ROLLUP;
+------+------+------+---------------+
| a    | b    | SUM  | GROUPING(a,b) |
+------+------+------+---------------+
| NULL |  112 | NULL |             0 |
| NULL | NULL | NULL |             1 |
|  111 |   11 |   11 |             0 |
|  111 |   12 |   12 |             0 |
|  111 | NULL |   23 |             1 |
|  222 |   22 |   22 |             0 |
|  222 |   23 |   23 |             0 |
|  222 | NULL |   45 |             1 |
| 1111 | NULL |  112 |             0 |
| 1111 | NULL |  112 |             1 |
| NULL | NULL |  180 |             3 |
+------+------+------+---------------+
11 rows in set (0.00 sec)
 

В данном примере GROUPING (a,b), возвращающая 3, означает, что значение NULL в столбце a и значение NULL в столбце b являются результатом действия ROLLUP. Для строк, у которых GROUPING(a,b) равно 1, значение NULL только в столбце b являются результатом действия ROLLUP.

Другие способы использования функции GROUPING

Функцию GROUPING можно использовать в части перечисления полей после ключевого слова SELECT или в части HAVING (условия отбора после группировки). Например, во втором случае можно получить отображение только тех строк, которые содержат итоговые/подытоговые значения, как показано в примере ниже:


mysql> SELECT a, b, SUM(c) as SUM FROM t1 GROUP BY a,b WITH ROLLUP HAVING GROUPING(a) = 1 or GROUPING(b) = 1;
+------+------+------+
| a    | b    | SUM  |
+------+------+------+
| NULL | NULL | NULL |
|  111 | NULL |   23 |
|  222 | NULL |   45 |
| 1111 | NULL |  112 |
| NULL | NULL |  180 |
+------+------+------+
5 rows in set (0.00 sec)
 

Кроме того, с помощью функции GROUPING можно придать выборке удобный для восприятия вид:


mysql> SELECT IF(GROUPING(a)=1,'All Departments', a) as Department, IF(GROUPING(b)=1, 'All Employees', b) as Employees, SUM(c) as SUM FROM t1 GROUP BY a,b WITH ROLLUP;
+-----------------+---------------+------+
| Department      | Employees     | SUM  |
+-----------------+---------------+------+
| NULL            | 112           | NULL |
| NULL            | All Employees | NULL |
| 111             | 11            |   11 |
| 111             | 12            |   12 |
| 111             | All Employees |   23 |
| 222             | 22            |   22 |
| 222             | 23            |   23 |
| 222             | All Employees |   45 |
| 1111            | NULL          |  112 |
| 1111            | All Employees |  112 |
| All Departments | All Employees |  180 |
+-----------------+---------------+------+
11 rows in set (0.00 sec)
 

Это были некоторые из возможностей использования новой функции GROUPING.

Заключение

Мы хотели бы поблагодарить Zhe Dong за его вклад, который был использован в качестве основы при добавлении этой функциональности. Пожалуйста, протестируйте GROUPING и сообщите нам о своих пожеланиях.

Дата публикации: 5.05.2017

© Все права на данную статью принадлежат порталу SQLInfo.ru. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.

Статьи :
 Установка и настройка MySQL
 Коды ошибок в MySQL
>Программирование в MySQL
 Оптимизация производительности
 Кодировка символов в MySQL
 Хранение данных в MySQL
 MySQL Cluster
См. также:
 Оптимизация производительности MySQL
 Онлайн-курс по оптимизации MySQL
 Услуги по оптимизации MySQL