GBase 8a的grouping函数使用说明和用例

本文介绍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)

发表评论

您的电子邮箱地址不会被公开。