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. Перепечатка в интернет-изданиях разрешается только с указанием автора и прямой ссылки на оригинальную статью. Перепечатка в бумажных изданиях допускается только с разрешения редакции.
|
