GROUP BY 子句

最新推荐文章于 2022-09-07 14:32:43 发布

matrixzero

最新推荐文章于 2022-09-07 14:32:43 发布

阅读量1.2k

点赞数

文章标签： sql domain c object database null

本文链接：https://blog.csdn.net/matrixzero/article/details/2782164

版权

GROUP BY 子句
GROUP BY子句主要用于对WHERE中得到的结果进行分组，也就是说它是在WHERE子句之后执行，对经过WHERE筛选后的结果按照某些列进行分组，之后进行相应的处理工作。

Specify the GROUP BY clause if you want the database to group the selected rows based on the value of expr(s) for each row and return a single row of summary information for each group.

当使用聚集函数的时候，除非对整个语句的查询结果集进行聚集运算，否则都要通过指定GROUP BY子句来确定是对某类结果集进行聚集运算。请看下面的例子：

17:11:10 SQL> select count(object_name) num from t; --这里是对整个表进行count运算，不会出错。
   NUM
------
50599
17:11:17 SQL> select count(object_name) num from t where STATUS='VALID'; --这里对返回来的所有结果进行count运算，不会出错。
NUM
-----
50578
17:12:00 SQL> select owner,status,count(object_name) num from t; --这里是想对owner和status进行分组，计算出它们的count(object_name)值，没有使用group by来显示分组，出错。
select owner,status,count(object_name) num from t
   *
ERROR at line 1:
ORA-00937: not a single-group group function

17:13:36 SQL> select owner,status,count(object_name) num from t group by owner,status;
OWNER STATUS NUM
---------- ----------- -------
PUBLIC VALID 19968
CTXSYS VALID 339
..............................
NING VALID 1
PUBLIC INVALID 19
ORDSYS VALID 1669
28 rows selected.
17:23:13 SQL> select owner,status,count(object_name) num from t group by owner,status,temporary; --这里多出了表t中的列temporary
OWNER STATUS NUM
---------- ------------ ------
WMSYS VALID 1
SCOTT INVALID 2
..............................
ORDSYS VALID 1669
HR VALID 34
IX VALID 53
35 rows selected.
17:29:06 SQL> select owner,status,count(object_name) num from t group by owner; --GROUP BY子句的内容少了status列
select owner,status,count(object_name) num from t group by owner
   *
ERROR at line 1:
ORA-00979: not a GROUP BY expression

可以看到，如果想对某一类结果集进行聚集运算，就必须通过GROUP BY来指定这类结果集，我们还可以看到以下两个特点：

1、 Expressions in the GROUP BY clause can contain any columns of the tables, views, or materialized views in the FROM clause, regardless of whether the columns appear in the select list.
2、在SELECT列表中，除非是函数（主要是聚集函数）、常量以外，其他所有的列名或者表达式（在GROUP BY中必须也要是表达式的原样而不能是它的别名），都必须要在GROUP BY子句中出现。
3、 The GROUP BY clause groups rows but does not guarantee the order of the result set. To order the groupings, use the ORDER BY clause.

使用GROUP BY子句还有下面一些的限制：

1、你不能在子句中使用LOB/VARRAYS/NESTED TABLE。
2、子句中的表达式can be of any form. except scalar subquery expressions.
3、如果GROUP BY中引用了对象类型列，则这个查询就不能使用并行。

HAVING 子句

Use the HAVING clause to restrict the groups of returned rows to those groups for which the specified condition is TRUE. If you omit this clause, then the database returns summary rows for all groups.这里的condition是HAVING子句指定的筛选条件。

HAVING子句依附于GROUP BY子句而出现，也就是它不能单独出现。

18:00:14 SQL> select owner,status s,count(object_name) num from t
18:00:43 2 group by owner,status,temporary having count(object_name)>500;
OWNER S NUM
---------- --------- ----------
SYS VALID 23129
XDB VALID 682
SYSMAN VALID 1321
PUBLIC VALID 19968
MDSYS VALID 859
OLAPSYS VALID 706
ORDSYS VALID 1669
7 rows selected.
18:00:47 SQL> select owner,status s,count(object_name) num from t
18:01:58 2 group by owner,status,temporary having num>500; --可见这里不能使用别名
group by owner,status,temporary having num>500
*
ERROR at line 2:
ORA-00904: "NUM": invalid identifier

GROUP BY子句扩展
1、自动汇总ROLLUP与CUBE扩展
我们先来看看ROLLUP的官方定义：

The ROLLUP operation in the simple_grouping_clause groups the selected rows based on the values of the first n, n-1, n-2, … 0 expressions in the GROUP BY specification, and returns a single row of summary for each group. You can use the ROLLUP operation to produce subtotal values by using it with the SUM function.这里simple_grouping_clause表示GROUP BY子句。

这句话可以这样理解，如果是ROLLUP(A, B, C)的话，首先会对(A、B、C)进行GROUP BY，然后对(A、B)进行GROUP BY，然后是(A)进行GROUP BY，最后对全表进行GROUP BY操作（即GROUP BY(0)）。即: ROLLUP操作字，对group by子句的各字段从右到左进行再聚合，逐个减少字段。ROLLUP常用于统计小计。
请看一个例子：

18:28:41 SQL> select * from t;
A B C
------ -------- ------
a test1 1
a test1 2
a test2 1
a test2 2
18:28:53 SQL> select a,b,c,sum(c) sc from t group by a,b,c order by 1;
A B C SC
------ -------- ------ ------
a test1 1 1
a test1 2 2
a test2 1 1
a test2 2 2
18:28:57 SQL> select a,b,c,sum(c) sc from t group by rollup(a,b,c) order by 1;
A B C SC
----- --------- ------ ------
a test1 1 1
a test1 2 2
a test1 3 -- A
a test2 1 1
a test2 2 2
a test2 3 -- A
a 6 -- B
6 -- C
8 rows selected.

注意看上面最后一个选择的结果，它多了一些显示记录。很显然，这个A部分是通过对GROUP BY(A,B)而得到的，这个B部分是通过对GROUP BY(A)，而C部分是通过GROUP BY(0)而来。如果要分别得到这些值，我们可以对等使用下面的方法得到：

18:35:25 SQL> select a,b,sum(c) sc from t group by a,b order by 1;
A B SC
------ -------- ------
a test1 3
a test2 3
18:35:30 SQL> select a,sum(c) sc from t group by a order by 1;
A SC
------ --------
a 6
18:35:40 SQL> select sum(c) sc from t order by 1; --相当于GROUP BY(0)
SC
------
6

部分ROLLUP：你也可以使用Rollup包含有限的几个小计，其语法是：Group by A,Rollup(B,C)。这种情况下，group by 条款先计算rollup中的部分，然后加上非rollup的部分，故层次为(A,B,C)(A,B)(A)。这里去除A不看就是GROUP BY(B,C),GROUP BY(B),GROUP BY(0)，刚好就是ROLLUP（B,C）部分，然后再加上A，就得到GROUP BY A,ROLLUP(B,C)。

20:53:26 SQL> select * from t;
A B C
------ -------- ------
a test1 1
a test1 2
a test2 2
a test2 1
20:54:21 SQL> select a,b,c,sum(c) sc from t group by a,rollup(b,c) order by 1;
A B C SC
------ -------- ------ ------
a test1 1 1
a test1 2 2
a test1 3
a test2 1 1
a test2 2 2
a test2 3
a 6
已选择7行。

从上面的讨论可以得到执行优先级是ROLLUP>GROUP BY，即先执行出ROLLUP中的内容，然后把GROUP BY中剩余的部分“拼接”上去。
下面来探讨一下CUBE的使用。
先看看CUBE的官方定义：

The CUBE operation in the simple_grouping_clause groups the selected rows based on the values of all possible combinations of expressions in the GROUP BY specification. It returns a single row of summary information for each group. You can use the CUBE operation to produce cross-tabulation values. 这里simple_grouping_clause表示GROUP BY子句。

这个定义可以这样理解：如果是GROUP BY CUBE(A, B, C)，则首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，最后对全表进行GROUP BY操作（即GROUP BY(0)）。即：CUBE操作字，除完成ROLLUP的功能外，再对ROLLUP后的结果集从右到左再聚合，逐个减少字段。CUBE常用于需要产生交叉报表的地方。
请看一个例子

18:42:01 SQL> select a,b,c,sum(c) sc from t group by a,b,c order by 1;
A B C SC
------ -------- ------ ------
a test1 1 1
a test1 2 2
a test2 1 1
a test2 2 2
18:42:11 SQL> select a,b,c,sum(c) sc from t group by cube(a,b,c) order by 1;
A B C SC
------ -------- ------ ------
a test1 1 1
a test1 2 2
a test1 3 -- A
a test2 1 1
a test2 2 2
a test2 3 -- A
a 1 2 -- B
a 2 4 -- B
a 6 -- C
   test1 1 1 -- D
   test1 2 2 -- D
   test1 3 -- E
   test2 1 1 -- D
   test2 2 2 -- D
   test2 3 -- E
   1 2 -- F
   2 4 -- F
   6 -- G
18 rows selected.

很显然，上面A部分是通过对GROUP BY(A,B)而得到，B部分是通过对GROUP BY(A,C)而得到，C部分是通过对GROUP BY(A)而得到，D部分是通过对GROUP BY(B,C)而得到，E部分是通过对GROUP BY(B)而得到，F部分是通过对GROUP BY(C)而得到，G部分是通过对GROUP BY(0)而得到。如果要分别得到这些值，我们可以对等使用下面的方法得到：

18:58:24 SQL> select a,b,sum(c) sc from t group by a,b order by 1,2;
A B SC
------ -------- ------
a test1 3
a test2 3
19:01:10 SQL> select a,c,sum(c) sc from t group by a,c order by 1,2;
A C SC
------ -------- ------
a 1 2
a 2 4
19:01:28 SQL> select a,sum(c) sc from t group by a order by 1;
A SC
------ ------
a 6
19:01:41 SQL> select b,c,sum(c) sc from t group by b,c order by 1,2;
B C SC
------ ------ --------
test1 1 1
test1 2 2
test2 1 1
test2 2 2
19:01:55 SQL> select b,sum(c) sc from t group by b order by 1;
B SC
---------- ----------
test1 3
test2 3
19:02:04 SQL> select c,sum(c) sc from t group by c order by 1;
   C SC
------ ------
   1 2
   2 4
19:02:13 SQL> select sum(c) sc from t;
   SC
------
   6

部分CUBE：CUBE也可以和ROLLUP一样，只包含GROUP BY子句中的部分列，其语法形式为：GROUP BY A,CUBE(B,C)。这种情况下，group by 条款先计算cube中的部分，然后加上非cube的部分，故层次为(A,B,C)(A,B)(A，C)(A)。这里去除A不看就是GROUP BY(B,C),GROUP BY(B), GROUP BY(C),GROUP BY(0)，刚好就是COBE（B,C）部分，然后再加上A，就得到GROUP BY A,CUBE(B,C)。

20:53:26 SQL> select * from t;
   A B C
------ ------ ------
   a test1 1
   a test1 2
   a test2 2
   a test2 1
20:54:30 SQL> select a,b,c,sum(c) sc from t group by a,cube(b,c) order by 1;
   A B C SC
------ ------ ------ -------
   a test1 1 1
   a test1 2 2
   a test1 3
   a test2 1 1
   a test2 2 2
   a test2 3
   a 1 2
   a 2 4
   a 6
已选择9行。

从上面的讨论可以得到执行优先级是CUBE>GROUP BY，即先执行出CUBE中的内容，然后把GROUP BY中剩余的部分“拼接”上去。
由于CUBE是对ROLLUP从右到左进行再聚合，因此显然执行优先级是CUBE>ROLLUP，故得到执行优先级CUBE>ROLLUP>GROUP BY。
当它们符合起来使用的时候会产生很多情况，下面仅给个例子，不做详细说明：

21:20:05 SQL> select * from t;
A B C D E
- ------ -- --- ----
a test1 1 11 111
a test1 2 11 111
a test2 2 22 222
a test2 1 22 222
21:20:08 SQL> select a,b,c,d,e,sum(c) sc from t group by a,rollup(b,c),cube(d,e);
A B C D E SC
- ------ -- ---- ---- ---
a test1 1 11 111 1
a test1 2 11 111 2
a test2 1 22 222 1
a test2 2 22 222 2
a test1 11 111 3
a 11 111 3
a test2 22 222 3
a 22 222 3
a test1 1 11 1
a test1 2 11 2
a test1 11 3
a 11 3
a test2 1 22 1
a test2 2 22 2
a test2 22 3
a 22 3
a test1 111 3
a 111 3
a test2 222 3
a 222 3
a test1 1 1
a test1 2 2
a test1 3
a test2 1 1
a test2 2 2
a test2 3
a 6
a test1 1 111 1
a test1 2 111 2
a test2 1 222 1
a test2 2 222 2
已选择31行。

例子中GROUP BY A,ROLLUP(B,C),CUBE(D,E)，可第一步分解CUBE为：

a,rollup(b,c),d,e
a,rollup(b,c),d
a,rollup(b,c),e
a,rollup(b,c)

第二步分解ROLLUP而得到最终所有情况为：

a,b,c,d,e
a,b,d,e
a,d,e
a,b,c,d
a,b,d
a,d
a,b,c,e
a,b,e
a,e
a,b,c
a,b
a

2、GROUPING SETS扩展
首先，我们还是来看下官方对GROUPING SETS的阐述：

GROUPING SETS are a further extension of the GROUP BY clause that let you specify multiple groupings of data. Doing so facilitates efficient aggregation by pruning the aggregates you do not need. Oracle Database computes all groupings specified in the GROUPING SETS clause and combines the results of individual groupings with a UNION ALL operation.

也就是通过它，你就可以控制GROUP BY后面的内容。举个例子来说，如果GROUP BY A,GROUPING SETS(B,C),那么实际上就想当于GROUP BY A,B UNION ALL GROUP BY A,C。
请看下面的这个例子：

22:04:03 SQL> select * from t;
A B C D E
- ------ -- --- ----
a test1 1 11 111
a test1 2 11 222
a test2 2 22 111
a test2 1 22 222
22:04:06 SQL> select a,b,c,d,e,sum(c) sc from t group by a,grouping sets((b,c),(d,e));
A B C D E SC
- ---------- -- --- ---- ---
a test1 2 2 -- F
a test1 1 1 -- F
a test2 2 2 -- F
a test2 1 1 -- F
a 22 111 2 -- G
a 11 222 2 -- G
a 22 222 1 -- G
a 11 111 1 -- G
已选择8行。
22:04:12 SQL> select a,b,c,d,e,sum(c) sc from t group by a,grouping sets(b,c);
select a,b,c,d,e,sum(c) sc from t group by a,grouping sets(b,c)
*
第 1 行出现错误:
ORA-00979: 不是 GROUP BY 表达式

22:07:41 SQL> select a,b,c,d,e,sum(c) sc from t group by a,(b,c),(d,e);
A B C D E SC
- ------ -- ---------- ---------- ----------
a test2 2 22 111 2
a test1 2 11 222 2
a test1 1 11 111 1
a test2 1 22 222 1

由例子，可以很显然的看出，F部分是GROUP BY A,B,C的结果，G部分是GROUP BY A,D,E的结果，整个GROUP BY A GROUPING SETS((B,C),(D,E))就是GROUP BY A,B,C和GROUP BY A,D,E的UNION ALL的结果，注意UNION ALL会产生重复值，尽管本例没有重复值。
还是从例子，我们可以看出，如果整个GROUP BY后面的表达式没有全部包括SELECT列表中的表达式（除了常量和函数）是出错的，这和前面我们讨论的结果一致。
还是从例子（最后一个选择语句），可以得到GROUP BY子句后面的表达式组合的多样性，即你可以通过GROUPING SETS来随意组合GROUP BY中的表达式。看看官方说法：

Within the GROUP BY clause, you can combine expressions in various ways:
i、 To specify composite columns, group columns within parentheses so that the database treats them as a unit while computing ROLLUP or CUBE operations.
ii、 To specify concatenated grouping sets, separate multiple grouping sets, ROLLUP, and CUBE operations with commas so that the database combines them into a single GROUP BY clause. The result is a cross-product of groupings from each grouping set.

3、三个grouping函数扩展
三个grouping函数是为了更清楚的显示哪些行是再聚合的结果，因此它们都只支持GROUP BY ROLLUP/CUBE/GROUPING SETS()子句，它们有着“美化”这三个子句的效果。
GROUP_ID()

GROUP_ID distinguishes duplicate groups resulting from a GROUP BY specification. It returns an Oracle NUMBER to uniquely identify duplicate groups. If n duplicates exist for a particular grouping, then GROUP_ID returns numbers in the range 0 to n-1.

一句话，就是这个函数用来标记经过GROUP BY ROLLUP/CUBE筛选之后的重复记录的行数。
先看个简单的例子：

23:13:31 SQL> select job,comm,count(*) num,group_id() from emp group by job,rollup(comm,job) order by 1,2,num;
JOB COMM NUM GROUP_ID()
--------- ----- ---- ----------
ANALYST 2 0
ANALYST 2 0
ANALYST 2 1
CLERK 4 1
CLERK 4 0
CLERK 4 0
MANAGER 3 0
MANAGER 3 1
MANAGER 3 0
PRESIDENT 1 1
PRESIDENT 1 0
PRESIDENT 1 0
SALESMAN 0 2 1
SALESMAN 0 2 0
SALESMAN 300 2 1
SALESMAN 300 2 0
SALESMAN 4 0
已选择17行。

上面这个例子证明了GROUP_ID的含义，再请看下面这个例子

23:06:06 SQL> select * from t;
A B C D E
- ------ - -- ----
a test1 1 11 111
a test1 2 11 111
23:06:42 SQL> select a,b,count(*) num,group_id() from t group by rollup(a,b);
A B NUM GROUP_ID()
- ------ ---- ----------
a test1 2 0
a 2 0
2 0
23:07:18 SQL> select a,b,count(*) num,group_id() from t group by rollup(a,b,d);
A B NUM GROUP_ID()
- ----- --- ----------
a test1 2 0 -- 第一行
a test1 2 0 -- 第二行
a 2 0
2 0

这里最后一个查询的前两行不是一摸一样吗？为什么在第二行的GROUP_ID()还是0而不是1呢？请注意看这里 group by rollup(a,b,d)，多了个d列，由此可以知道：假设第一行是通过GROUP BY A,B,D而来，那么第二行就是通过GROUP BY A,B而来，这是两个不同的GROUP BY筛选条件了，即使得到同样的结果，GROUP_ID()也不认为它们是重复的。
因此，GROUP_ID()函数是标记相同的GROUP BY条件筛选出来的结果的重复值。

GROUPING(exp)

GROUPING distinguishes superaggregate rows from regular grouped rows. GROUP BY extensions such as ROLLUP and CUBE produce superaggregate rows where the set of all values is represented by null.

也就是说，GROUPING函数是标记出除了exp而外的GROUP BY中表达式的被再聚合的行，即exp IS NULL并且是其他列被再聚合的行，而不管这种再聚合是ROLLUP还是CUBE。exp为NULL的时候，就标记为1，否则为0。
下面看个例子来说明这个问题：

23:33:11 SQL> select index_type,status,count(*) num,grouping(status) from test group by rollup(index_type,status);
INDEX_TYPE STATUS NUM GROUPING(STATUS)
---------------------- -------- ----- ----------------
LOB N/A 1 0
LOB VALID 566 0
LOB 567 1 * -- 第一行，因为status为null，故GROUPING(STATUS)标记为1
BITMAP N/A 7 0
BITMAP VALID 8 0
BITMAP 15 1 *
DOMAIN VALID 1 0
DOMAIN 1 1 *
NORMAL N/A 56 0
NORMAL VALID 1565 0
NORMAL 1621 1 *
CLUSTER VALID 10 0
CLUSTER 10 1 *
IOT - TOP VALID 114 0
IOT - TOP 114 1 *
FUNCTION-BASED DOMAIN VALID 1 0
FUNCTION-BASED DOMAIN 1 1 *
FUNCTION-BASED NORMAL VALID 17 0
FUNCTION-BASED NORMAL 17 1 *
2346 1 *
已选择20行。

注意看上面的加“*”部分，被标记为1的都是对除了status列外GROUP BY表达式中剩余部分，即IDNEX_TYPE列，进行再聚合的行。我以第一行做为讲例，它是通过GROUP BY INDEX_TYPE，即GROUP BY ROLLUP(INDEX_TYPE,STATUS)中GROUP BY (INDEX)。所有被标记为1的都是GROUP BY ROLLUP(INDEX_TYPE,STATUS)去除STATUS后GROUP BY ROLLUP(INDEX_TYPE)情况。
这里其实就是标记出INDEX_TYPE的小计。
我们来看相反的情况，把INDEX_TYPE列设置为要去除的列，看看结果：

23:36:33 SQL> select index_type,status,count(*) num,grouping(index_type) from test group by rollup(index_type,status);
INDEX_TYPE STATUS NUM GROUPING(INDEX_TYPE)
---------------------- ------- ---- --------------------
LOB N/A 1 0
LOB VALID 566 0
LOB 567 0
BITMAP N/A 7 0
BITMAP VALID 8 0
BITMAP 15 0
DOMAIN VALID 1 0
DOMAIN 1 0
NORMAL N/A 56 0
NORMAL VALID 1565 0
NORMAL 1621 0
CLUSTER VALID 10 0
CLUSTER 10 0
IOT - TOP VALID 114 0
IOT - TOP 114 0
FUNCTION-BASED DOMAIN VALID 1 0
FUNCTION-BASED DOMAIN 1 0
FUNCTION-BASED NORMAL VALID 17 0
FUNCTION-BASED NORMAL 17 0
2346 1 *
已选择20行。

完全正确，因为GROUP BY ROLLUP(INDEX_TYPE,STAUS)去除INDEX_TYPE而得到的GROUP BY ROLLUP(STATUS)，它只要GROUP BY(0)这种情况。
下面我们看看CUBE，去除STATUS，即STATUS IS NULL 对INDEX_TYPE的再聚合情况

23:33:25 SQL> select index_type,status,count(*) num,grouping(status) from test group by cube(index_type,status);
INDEX_TYPE STATUS NUM GROUPING(STATUS)
---------------------- ------- ----- ----------------
   2346 1
   N/A 64 0
   VALID 2282 0
LOB 567 1
LOB N/A 1 0
LOB VALID 566 0
BITMAP 15 1
BITMAP N/A 7 0
BITMAP VALID 8 0
DOMAIN 1 1
DOMAIN VALID 1 0
NORMAL 1621 1
NORMAL N/A 56 0
NORMAL VALID 1565 0
CLUSTER 10 1
CLUSTER VALID 10 0
IOT - TOP 114 1
IOT - TOP VALID 114 0
FUNCTION-BASED DOMAIN 1 1
FUNCTION-BASED DOMAIN VALID 1 0
FUNCTION-BASED NORMAL 17 1
FUNCTION-BASED NORMAL VALID 17 0
已选择22行。

下面是去除INDEX_TYPE，即INDEX_TYPE IS NULL对STATUS的再聚合情况

23:39:02 SQL> select index_type,status,count(*) num,grouping(index_type) from test group by cube(index_type,status);
INDEX_TYPE STATUS NUM GROUPING(INDEX_TYPE)
---------------------- ------- ------ --------------------
   2346 1
   N/A 64 1
   VALID 2282 1
LOB 567 0
LOB N/A 1 0
LOB VALID 566 0
BITMAP 15 0
BITMAP N/A 7 0
BITMAP VALID 8 0
DOMAIN 1 0
DOMAIN VALID 1 0
NORMAL 1621 0
NORMAL N/A 56 0
NORMAL VALID 1565 0
CLUSTER 10 0
CLUSTER VALID 10 0
IOT - TOP 114 0
IOT - TOP VALID 114 0
FUNCTION-BASED DOMAIN 1 0
FUNCTION-BASED DOMAIN VALID 1 0
FUNCTION-BASED NORMAL 17 0
FUNCTION-BASED NORMAL VALID 17 0
已选择22行。

下面是说明GROUPING函数的参数只能是一个表达式

23:39:19 SQL> select index_type,status,count(*) num,grouping(index_type,status) from test group by cube(index_type,status);
select index_type,status,count(*) num,grouping(index_type,status) from test group by cube(index_type,status)
*
第 1 行出现错误:
ORA-00909: 参数个数无效

23:40:12 SQL> select index_type,status,count(*) num,grouping(status,index_type) from test group by cube(index_type,status);
select index_type,status,count(*) num,grouping(status,index_type) from test group by cube(index_type,status)
*
第 1 行出现错误:
ORA-00909: 参数个数无效
通过运用GROUPING(exp)，就可以很简单的找出被再聚合的那些列的聚合结果，即GROUPING(exp)=1行,反应了GROUP BY子句中其它列的聚合结果。通俗的说法，即GROUPING(exp)=1所在的那一行，是GROUP BY子句中除了exp外其它部分的一个小计统计。具体请看上面的例子。

GROUPING_ID(exp[,…])

GROUPING_ID is functionally equivalent to taking the results of multiple GROUPING functions and concatenating them into a bit vector (a string of ones and zeros).
GROUPING_ID returns a number corresponding to the GROUPING bit vector associated
with a row. GROUPING_ID is applicable only in a SELECT statement that contains a GROUP BY extension, such as ROLLUP or CUBE, and a GROUPING function.

它的含义是:
GROUPING_ID(exp1,exp2,…,expN)={GROUPING(exp1)||GROUPING(exp2)||…||GROUPING(expN)}变成十进制数，如：
如果GROUPING(A)=1,GROUPING(B)=0,GROUPING(C)=1，那么
GROUPING_ID(A,B,C) = [101]二进制 = 5，
GROUPING_ID(B,A,C) = [011]二进制 = 3.
因此，你就可以很简单得通过GROUPING_ID函数来得到列的再聚合情况，如上面的GROUPING_ID(A,B,C) = 5，并且我们假设查询语句的GROUP BY子句是GROUP BY CUBE(A,B,C)，那么它的含义就表示对A,C列进行了GROUPING结果，即B列进行的聚合小计（除了A,C以外的GROUP BY中的剩余列的聚合小计，这个是根据GROUPING函数的含义而来）。
我们来看个例子：

08:39:20 SQL> select * from t;
A B C
- ------ --
a test1 1
a test1 2
a test2 1
a test2 2
08:39:26 SQL> select a,b,sum(c) sc from t group by rollup(a,b);
A B SC
- ------ ---
a test1 3
a test2 3
a 6
6
08:40:11 SQL> select a,b,sum(c) sc,grouping(a),grouping(b),grouping_id(a,b),grouping_id(b,a) from t group by rollup(a,b);
A B SC GROUPING(A) GROUPING(B) GROUPING_ID(A,B) GROUPING_ID(B,A)
- ------ --- ----------- ----------- ---------------- ----------------
a test1 3 0 0 0 0
a test2 3 0 0 0 0
a 6 0 1 1 2 #
6 1 1 3 3

注意这里的加#部分，GROUPING_ID(B,A)=2，可推导出GROUPING(B)=1, GROUPING(A)=0，而查询语句中GROUP BY ROLLUP(A,B)，这就说明这行是对A做了聚合小计（除B以外的GROUP BY子句中的列，A，做聚合小计）。
同样，也可以这样推导：GROUPING_ID(A,B)=1,可推导出GROUPING(A)=0, GROUPING(B)=1,而查询语句中GROUP BY ROLLUP(A,B)，这就说明这行是对A做了聚合小计（除B以外的GROUP BY子句中的列，A，做聚合小计）。
看看CUBE的情况，根据GROUPING_ID的解释，也很明了。

08:40:48 SQL> select a,b,sum(c) sc,grouping(a),grouping(b),grouping_id(a,b),grouping_id(b,a) from t group by cube(a,b);
A B SC GROUPING(A) GROUPING(B) GROUPING_ID(A,B) GROUPING_ID(B,A)
- ------ --- ----------- ----------- ---------------- ----------------
6 1 1 3 3
test1 3 1 0 2 1
test2 3 1 0 2 1
a 6 0 1 1 2
a test1 3 0 0 0 0
a test2 3 0 0 0 0
6 rows selected.

我们常将GROUPING_ID函数结合HAVING子句运用，把部署聚合小计的部分去除掉，如：

08:56:04 SQL> select a,b,sum(c) sc,grouping(a),grouping(b),grouping_id(a,b)
10:34:39 2 from t group by rollup(a,b) having grouping_id(a,b)>0;
A B SC GROUPING(A) GROUPING(B) GROUPING_ID(A,B)
- -- --- ----------- ----------- ----------------
a 6 0 1 1
6 1 1 3

这样就能很方便的查看聚合小计。
FAQ
我们可以通过灵活运用GROUPING SETS,ROLLUP,CUBE来实现显示不同层次的聚合。
下面来看这个例子，在这个句子中GROUPING SETS相当于ROLLUP：

10:34:40 SQL> select a,b,sum(c) sc,
10:36:30 2 grouping(a),grouping(b),grouping_id(a,b)
10:40:49 3 from t group by rollup(a,b);
A B SC GROUPING(A) GROUPING(B) GROUPING_ID(A,B)
- ------ --- ----------- ----------- ----------------
a test1 3 0 0 0
a test2 3 0 0 0
a 6 0 1 1
6 1 1 3

10:40:50 SQL> select a,b,sum(c) sc,
10:40:53 2 grouping(a),grouping(b),grouping_id(a,b)
10:40:57 3 from t group by grouping sets((a,b),(a),null);
A B SC GROUPING(A) GROUPING(B) GROUPING_ID(A,B)
- ------ --- ----------- ----------- ----------------
a test1 3 0 0 0
a test2 3 0 0 0
a 6 0 1 1
6 1 1 3

下面这个例子是GROUPING SETS相当于CUBE：

10:42:58 SQL> select a,b,sum(c) sc,
10:43:26 2 grouping(a),grouping(b),grouping_id(a,b)
10:43:26 3 from t

group by cube(a,b)
10:43:26    4 order by 1,2,3;
A B SC GROUPING(A) GROUPING(B) GROUPING_ID(A,B)
- ------ -- ----------- ----------- ----------------
a test1 3 0 0 0
a test2 3 0 0 0
a 6 0 1 1
test1 3 1 0 2
test2 3 1 0 2
   6 1 1 3
6 rows selected.

10:43:27 SQL>   select a,b,sum(c) sc,
10:43:34    2         grouping(a),grouping(b),grouping_id(a,b)
10:43:34    3   from t

group by grouping sets((a,b),(a),(b),())
10:43:34 4 order by 1,2,3;
A B SC GROUPING(A) GROUPING(B) GROUPING_ID(A,B)
- ------ -- ----------- ----------- ----------------
a test1 3 0 0 0
a test2 3 0 0 0
a 6 0 1 1
test1 3 1 0 2
test2 3 1 0 2
6 1 1 3
6 rows selected.

这个里面需要说明的就是GROUPING SETS的最后一个表达式，即null和（），它是两种不同的表达方式，其实就是相当于做GROUP BY(0),请看操作例子：

10:46:32 SQL> select null,null,sum(c) sc,null,null,null
10:46:49 2 from t
10:46:49 3 order by 1,2,3;
N N SC N N N
- - --- - - -
6

参考文献
1、 official documents
2、 http://xsb.itpub.net/post/419/29159
3、 http://tomszrp.itpub.net/post/11835/64788

matrixzero

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
GROUP BY 子句

GROUP BY 子句GROUP BY子句主要用于对WHERE中得到的结果进行分组，也就是说它是在WHERE子句之后执行，对经过WHERE筛选后的结果按照某些列进行分组，之后进行相应的处理工作。Specify the GROUP BY clause if you want the database to group the selected rows based on the value of
复制链接

扫一扫