GROUP BY中ROLLUP/CUBE/GROUPING/GROUPING SETS使用示例
oracle group by中rollup和cube的区别：
Oracle的GROUP BY语句除了最基本的语法外，还支持ROLLUP和CUBE语句。CUBE ROLLUP 是用于统计数据的。
实验使用SCOTT用户的EMP表测试
1.仅使用GROUP BY分组，GROUP BY后的单列可以用括号，也可以不用。以下两种写法作用一样：SCOTT@bys1>SELECT deptno, avg(sal) FROM emp GROUP BY deptno;
SCOTT@bys1>SELECT deptno, avg(sal) FROM emp GROUP BY (deptno);
DEPTNO AVG(SAL)
---------- ----------
30 1566.66667
20 2175
10 2916.66667
正常分组，GROUP BY后的多个列之前要用逗号隔开，列名可以写到 括号里，也可以不使用括号。
下面三种写法作用一样：
SCOTT@bys1>SELECT nvl(to_char(deptno),'heji') ,job, avg(sal) FROM emp GROUP BY (job,deptno);
SCOTT@bys1>SELECT deptno,job, avg(sal) FROM emp GROUP BY (job,deptno);
SCOTT@bys1>SELECT deptno,job, avg(sal) FROM emp GROUP BY job,deptno;
DEPTNO JOB AVG(SAL)
---------- --------- ----------
20 MANAGER 2975
10 PRESIDENT 5000
10 CLERK 1300
30 SALESMAN 1400
20 ANALYST 3000
30 MANAGER 2850
10 MANAGER 2450
30 CLERK 950
20 CLERK 950
##################################################################################
2.在GROUP BY语句中使用ROLLUP：使用ROLLUP操作符时，在生成原有统计结果基础上，生成横向小计结果。
为每个分组返回一条小计记录，并为全部分组返回总计。
下面两个语句只用到了一个分组列，所以返回的是一个总计。
SCOTT@bys1>SELECT deptno, avg(sal) FROM emp GROUP BY rollup(deptno);
DEPTNO AVG(SAL)
---------- ----------
10 2916.66667
20 2175
30 1566.66667
2073.21429
使用NVL，如果deptno列为NULL，则用'heji'。可以使结果更美观。
SCOTT@bys1>SELECT nvl(to_char(deptno),'heji') as deptno, avg(sal) FROM emp GROUP BY rollup(deptno);
DEPTNO AVG(SAL)
---------------------------------------- ----------
10 2916.66667
20 2175
30 1566.66667
heji 2073.21429
下面语句用到了两个列。
可以看到下面查询产生了如下结果行：-----它按照grouping list列从右到左进行更高层的聚合
1.对(deptno,job)进行GROUP BY，即按部门分组，相同部门里再按相同岗位进行分组。聚合统计同一部门相同岗位的平均工资。
2.对分组后的(deptno）进行GROUP BY，即相同部门的分组聚合统计。这里就是统计同一部门所有人的平均工资
3.对所有员工的平均工资聚合统计
这里如果是GROUP BY rollup(a,b,c);对(a,b,c)三列分组的话，就是先对(a,b,c)进行GROUP BY，再对(a,b)进行GROUP BY，再对(a)进行GROUP BY，再对全表GROUP BY。
即ROLLUP(1,2,N)时，GROUP BY的所有可能的GROUP BY数是2+N个，比如CUBE(a,b,c);时，总共有4个。
3.最后统计了所有员工的平均工资。即统计的第2步分组后的各种岗位的平均工资--也可能是全部员工的工资平均。
如果是GROUP BY CUBE(a,b,c);首先会对(A、B、C)进行GROUP BY，然后依次是(A、B)，(A、C)，(A)，(B、C)，(B)，(C)，再对全表进行GROUP BY。
即CUBE(1,2,N)时，GROUP BY的所有可能的GROUP BY数是2的N次方，比如CUBE(a,b,c);时，总共有8个。
在GROUP BY子句有列（a,b)两列时，ROLLUP统计（a,b)，（a)；而CUBE统计了（a,b)，（a)，（b)。
在此实验中就是：
ROLLUP统计了按(deptno,job)分组汇总，按(deptno)分组汇总，最后对全表进行GROUP BY操作。
CUBE统计了按(deptno,job)分组汇总，按(deptno)分组汇总，按(job)分组汇总，最后对全表进行GROUP BY操作。
#############################################################################################
4.grouping函数，解决在返会的结果中如何能准确区分出那些是小计，哪些是汇总数据。GROUPING只能在使用ROLLUP或CUBE的查询中使用。对输入列返回0或1，如果该行数据使用了数据的列中的信息，即此列数据参与ROLLUP/CUBE函数分组汇总活动，则输出0；没有用到则输出1
或者说，对于该行得出的统计数据，需要从输入列中选择数据的话，输出0；不需要选择数据的就输出1
如下：GROUP BY rollup(deptno,job)时，可以看到在不同聚合统计列deptno,job字段的使用情况。0使用该字段，1未使用。
SCOTT@bys1>SELECT nvl(to_char(deptno),'zongji') as deptno,job,avg(sal),grouping(deptno),grouping(job) FROM emp GROUP BY rollup(deptno,job);
DEPTNO JOB AVG(SAL) GROUPING(DEPTNO) GROUPING(JOB)
---------------------------------------- --------- ---------- ---------------- -------------
10 CLERK 1300 0 0
10 MANAGER 2450 0 0
10 PRESIDENT 5000 0 0
10 2916.66667 0 1
20 CLERK 950 0 0
20 ANALYST 3000 0 0
20 MANAGER 2975 0 0
20 2175 0 1
30 CLERK 950 0 0
30 MANAGER 2850 0 0
30 SALESMAN 1400 0 0
30 1566.66667 0 1
zongji 2073.21429 1 1
如下：GROUP BY cube(deptno,job)时，可以看到在不同聚合统计列deptno,job字段的使用情况。0使用该字段，1未使用。
SCOTT@bys1>SELECT nvl(to_char(deptno),'zongji') as deptno,job,avg(sal),grouping(deptno),grouping(job) FROM emp GROUP BY cube(deptno,job);
DEPTNO JOB AVG(SAL) GROUPING(DEPTNO) GROUPING(JOB)
---------------------------------------- --------- ---------- ---------------- -------------
zongji 2073.21429 1 1
zongji CLERK 1037.5 1 0
zongji ANALYST 3000 1 0
zongji MANAGER 2758.33333 1 0
zongji SALESMAN 1400 1 0
zongji PRESIDENT 5000 1 0
10 2916.66667 0 1
10 CLERK 1300 0 0
10 MANAGER 2450 0 0
10 PRESIDENT 5000 0 0
20 2175 0 1
20 CLERK 950 0 0
20 ANALYST 3000 0 0
20 MANAGER 2975 0 0
30 1566.66667 0 1
30 CLERK 950 0 0
30 MANAGER 2850 0 0
30 SALESMAN 1400 0 0
SCOTT@bys1>SELECT nvl(to_char(deptno),'zongji') as deptno,job,avg(sal),grouping(deptno),grouping(job) FROM emp GROUP BY grouping sets(deptno,job);
DEPTNO JOB AVG(SAL) GROUPING(DEPTNO) GROUPING(JOB)
---------------------------------------- --------- ---------- ---------------- -------------
zongji CLERK 1037.5 1 0
zongji SALESMAN 1400 1 0
zongji PRESIDENT 5000 1 0
zongji MANAGER 2758.33333 1 0
zongji ANALYST 3000 1 0
30 1566.66667 0 1
20 2175 0 1
10 2916.66667 0 1
不过当查询只有一个聚合列时，是将全表统计的给过滤了：
SCOTT@bys1>SELECT deptno, avg(sal),grouping(deptno) FROM emp GROUP BY cube(deptno);
DEPTNO AVG(SAL) GROUPING(DEPTNO)
---------- ---------- ----------------
2073.21429 1
10 2916.66667 0
20 2175 0
30 1566.66667 0
SCOTT@bys1>SELECT deptno, avg(sal),grouping(deptno) FROM emp GROUP BY grouping sets(deptno);
DEPTNO AVG(SAL) GROUPING(DEPTNO)
---------- ---------- ----------------
30 1566.66667 0
20 2175 0
10 2916.66667 0
这个返回一个整数，最小为0，这个整数怎么确定，将上面的输入的列，分配以bit，column_name1 的在column_name2的左边，
这样就形成了一个二进制数，将它转为10进制就是获得的数了，
怎么确定每位的0和1？
每位的值，和 GROUPING(column_name)的值是一样的，
例如上面的GROUPING(column_name1) GROUPING(column_name2) 为1 和0
则获得的值为 0b10 ，即2.
