Oracle基础——group by 、group by rollup、grouping、grouping_id等的用法

group by,group by rollup：

    GROUP BY 语句用于结合合计函数(sum(),count()...)，根据一个或多个列对结果集进行分组。group by 与group by rollup都可以用来对查询结果进行分组；例如：

select province_id,city_id,sum(county) from table group by province_id,city_id;

select province_id,city_id,sum(county) from table group by rollup(province_id,city_id);

    Oracle数据库中的rollup配合group by命令使用，可以提供信息汇总功能（与"小计"相似）；用了rollup的group by子句所产生的所谓的超级聚合就是指在在产生聚合时会从右向左逐个对每一列进行小结，并在结果中生成独立的一行，同时也会对聚合列生成一个合计列。这里的group by后面我们仅仅接了2列，实际上我们可以使用更多列的，这样的话oracle就会以从右向左的方式来进行逐个小结。但是我们大多数情况下需要在查询的结果集的汇总列加上“合计”，怎么办呢？用grouping和grouping_id函数啦，然后再用decode函数判断一下是否为空就可以了
select decode(grouping_id(job,deptno),1,'合计',job||deptno) as group_col,sum(sal) total_sal from emp group by rollup(job,deptno);

grouping:

    GROUPING函数可以接受一列，返回0或者1。如果列值为空，那么GROUPING()返回1；如果列值非空，那么返回0。GROUPING只能在使用ROLLUP或CUBE的查询中使用。当需要在返回空值的地方显示某个值时，GROUPING()就非常有用。注意：grouping中只能有一个参数。例如：

select province,sum(is_pass) from sta_rule_rev_exp_diff2017 group by rollup (province) order by province

PROVINCE	SUM(IS_PASS)
...	...
640000	9514
650000	39191
NULL	1823806

加上GROUPING来看看:

select GROUPING(province),province,sum(is_pass) from sta_rule_rev_exp_diff2017 group by rollup (province) order by province

GROUPING(PROVINCE)	PROVINCE	SUM(IS_PASS)
0	...	...
0	640000	9514
0	650000	39191
1		1823806

使用CASE转换GROUPING()的返回值

select case GROUPING(province) when 1 then '总计' else province end as province,sum(is_pass) from sta_rule_rev_exp_diff2017 group by rollup (province) order by province

PROVINCE	SUM(IS_PASS)
...	...
640000	9514
650000	39191
总计	1823806

使用CASE转换GROUPING()的返回值，转换一列跟多列类似；

select case grouping(province) when 1 then '总计' else province end province,
case grouping(city) when 1 then '省总' else city end city,sum(is_pass)
from  sta_rule_rev_exp_diff2017 group by rollup (province,city) order by province,city

使用GROUPING SETS子句，使用GROUPING SETS子句可以只返回小计记录。

select province,city,sum(is_pass) from sta_rule_rev_exp_diff2017 group by grouping sets(province,city) order by province,city。

grouping_id:

    grouping_id可以接受一列或者多列，接受多列时，grouping_id()的返回值也不同。如下表：

select grouping(province),grouping(city),grouping_id(province,city),province,city,sum(is_pass) from
sta_rule_rev_exp_diff2017 group by rollup (province,city)

GROUPING(PROVINCE)	GROUPING(CITY)	GROUPING_ID(PROVINCE,CITY)	PROVINCE	CITY	SUM(IS_PASS)
...	...	...	...	...	...
0	1	1	110000	NULL	4563
...	...	...	...	...	...
1	1	3	NULL	NULL	1826451

select grouping(province),grouping(city),grouping_id(city,province),province,city,sum(is_pass) from
sta_rule_rev_exp_diff2017 group by rollup (province,city)

GROUPING(PROVINCE)	GROUPING(CITY)	GROUPING_ID(CITY,PROVINCE)	PROVINCE	CITY	SUM(IS_PASS)
...	...	...	...	...	...
0	1	2	110000	NULL	4563
...	...	...	...	...	...
1	1	3	NULL	NULL	1826451

，因为GROUPING_ID()函数可以接受一列或多列，返回GROUPING位向量的十进制值。GROUPING位向量的计算方法是将按照顺序对每一列调用GROUPING函数的结果组合起来，所以说01和10的值不一样的

GOURPING位向量计算如下表：

GROUPING(province)	GROUPING(city)	位向量	GROUPING_ID（）返回值
0(非空)	0（非空）	00	0
0（非空）	1（空）	01	1
1（非空）	0（空）	10	2
1（空）	1（空）	11	3

    使用grouping_id()时，使显示结果更加人性化。如下：

select province,decode(grouping_id(province,city),'0',city,'1','省计','3','总计') city，sum(is_pass) from
sta_rule_rev_exp_diff2017 group by rollup(province,city) order by province,city

PROVINCE	CITY	SUM(IS_PASS)
...640000	...641201	...456
650000	省计	39191
null	总计	1824915

还可以去除不是小计或总计的值。如下：

select province,decode(grouping_id(province,city),'0',city,'1','省计','3','总计') city，sum(is_pass) from
sta_rule_rev_exp_diff2017 group by rollup(province,city) having grouping_id(province,city)>0 order by province,city

PROVINCE	CITY	SUM(IS_PASS)
...	...	...
650000	省计	39191
null	总计	1824915