本文介绍GBase 8a数据库集群内置的grouping函数使用，其和group by rollup/cube/grouping sets一起使用时才有效果。
目录导航
参考
GBase 8a OLAP函数group by grouping sets的使用样例
GBase 8a窗口函数使用样例索引
语法
GROUPING (expr)说明
expr 参与group by rollup/cube/grouping sets的列。
- GROUPING 表示 GROUP BY 列表中的表达式是否参与分组。
- 返回1 表示不参与分组
- 返回 0 表示参与分组 。
- 对普通 GROUP BY 表达式 ，GROUPING 返回 0。
样例
数据
gbase> select * from t2;
+------+------+------+
| id | type | val |
+------+------+------+
| 1 | 1 | 111 |
| 2 | 1 | 122 |
| 3 | 1 | 133 |
| 4 | 2 | 222 |
| 5 | 2 | 233 |
| 6 | 2 | 244 |
| 7 | 3 | 333 |
+------+------+------+
7 rows in set (Elapsed: 00:00:00.01)
Grouping 在普通Group by的例子
返回0。
gbase> select type,sum(val),count(*) from t2 group by type;
+------+----------+----------+
| type | sum(val) | count(*) |
+------+----------+----------+
| 2 | 699 | 3 |
| 1 | 366 | 3 |
| 3 | 333 | 1 |
+------+----------+----------+
3 rows in set (Elapsed: 00:00:00.05)
Grouping 在group by rollup的例子
最后的汇总，type为全部，此时其并不参与group,所以grouping返回1。
gbase> select type,sum(val),count(*),grouping(type) from t2 group by rollup(type);
+------+----------+----------+----------------+
| type | sum(val) | count(*) | grouping(type) |
+------+----------+----------+----------------+
| 1 | 366 | 3 | 0 |
| 2 | 699 | 3 | 0 |
| 3 | 333 | 1 | 0 |
| NULL | 1398 | 7 | 1 |
+------+----------+----------+----------------+
4 rows in set (Elapsed: 00:00:00.03)
多列grouping的例子
多列时，当前分组级别不参与的列，返回1， 参与的返回0。
gbase> select type,(id%3) id,sum(val),count(*),grouping(type),grouping(id%3) from t2 group by rollup(type,id%3);
+------+------+----------+----------+----------------+----------------+
| type | id | sum(val) | count(*) | grouping(type) | grouping(id%3) |
+------+------+----------+----------+----------------+----------------+
| 1 | 1 | 111 | 1 | 0 | 0 |
| 1 | 2 | 122 | 1 | 0 | 0 |
| 1 | 0 | 133 | 1 | 0 | 0 |
| 2 | 1 | 222 | 1 | 0 | 0 |
| 2 | 2 | 233 | 1 | 0 | 0 |
| 2 | 0 | 244 | 1 | 0 | 0 |
| 3 | 1 | 333 | 1 | 0 | 0 |
| 1 | NULL | 366 | 3 | 0 | 1 |
| 2 | NULL | 699 | 3 | 0 | 1 |
| 3 | NULL | 333 | 1 | 0 | 1 |
| NULL | NULL | 1398 | 7 | 1 | 1 |
+------+------+----------+----------+----------------+----------------+
11 rows in set (Elapsed: 00:00:00.03)
gbase>
Grouping 在group by cube的例子
gbase> select type,(id%3) id,sum(val),count(*),grouping(type),grouping(id%3) from t2 group by cube(type,id%3);
+------+------+----------+----------+----------------+----------------+
| type | id | sum(val) | count(*) | grouping(type) | grouping(id%3) |
+------+------+----------+----------+----------------+----------------+
| 1 | 1 | 111 | 1 | 0 | 0 |
| 1 | 2 | 122 | 1 | 0 | 0 |
| 1 | 0 | 133 | 1 | 0 | 0 |
| 2 | 1 | 222 | 1 | 0 | 0 |
| 2 | 2 | 233 | 1 | 0 | 0 |
| 2 | 0 | 244 | 1 | 0 | 0 |
| 3 | 1 | 333 | 1 | 0 | 0 |
| NULL | 1 | 666 | 3 | 1 | 0 |
| NULL | 2 | 355 | 2 | 1 | 0 |
| NULL | 0 | 377 | 2 | 1 | 0 |
| 1 | NULL | 366 | 3 | 0 | 1 |
| 2 | NULL | 699 | 3 | 0 | 1 |
| 3 | NULL | 333 | 1 | 0 | 1 |
| NULL | NULL | 1398 | 7 | 1 | 1 |
+------+------+----------+----------+----------------+----------------+
14 rows in set (Elapsed: 00:00:00.04)
Grouping 在group by grouping sets的例子
请区分二者的区别，第一个grouping函数，第二个是和sets一起使用的。 GBase 8a OLAP函数group by grouping sets的使用样例
gbase> select type,(id%3) id,sum(val),count(*),grouping(type),grouping(id%3) from t2 group by grouping sets(type,id%3);
+------+------+----------+----------+----------------+----------------+
| type | id | sum(val) | count(*) | grouping(type) | grouping(id%3) |
+------+------+----------+----------+----------------+----------------+
| NULL | 1 | 666 | 3 | 1 | 0 |
| NULL | 2 | 355 | 2 | 1 | 0 |
| NULL | 0 | 377 | 2 | 1 | 0 |
| 1 | NULL | 366 | 3 | 0 | 1 |
| 2 | NULL | 699 | 3 | 0 | 1 |
| 3 | NULL | 333 | 1 | 0 | 1 |
+------+------+----------+----------+----------------+----------------+
6 rows in set (Elapsed: 00:00:00.03)