GROUP BY中ROLLUP/CUBE/GROUPING/GROUPING SETS使用示例

迈不过友情╰ 2023-10-09 10:00 35阅读 0赞

转自：[https://www.cnblogs.com/pangblog/p/3402557.html][https_www.cnblogs.com_pangblog_p_3402557.html]

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个。

SCOTT@bys1>SELECT nvl(to\_char(deptno),'heji') as deptno,job,avg(sal) FROM emp  GROUP BY rollup(deptno,job);   
DEPTNO                                   JOB         AVG(SAL)   
\---------------------------------------- --------- ----------   
10                                       CLERK           1300   
10                                       MANAGER         2450   
10                                       PRESIDENT       5000   
10                                                 2916.66667   
20                                       CLERK            950   
20                                       ANALYST         3000   
20                                       MANAGER         2975   
20                                                       2175   
30                                       CLERK            950   
30                                       MANAGER         2850   
30                                       SALESMAN        1400   
30                                                 1566.66667  
heji                                               2073.21429   
\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#\#

### 3.在GROUP BY中使用CUBE: ###

使用CUBE操作符时，在生成原有统计结果基础上，生成纵向小计结果。   
返回所有列组合的小计信息，同时在最后显示总计信息   
下面两个语句只用到了一个分组列，所以返回的是一个总计。--ROLLUP总计在下面，CUBE的统计结果在上面。   
SCOTT@bys1>SELECT nvl(to\_char(deptno),'zongji'), avg(sal) FROM emp  GROUP BY cube(deptno);   
NVL(TO\_CHAR(DEPTNO),'ZONGJI')              AVG(SAL)   
\---------------------------------------- ----------   
zongji                                   2073.21429   
10                                       2916.66667   
20                                             2175   
30                                       1566.66667   
下面语句用到了两个列。   
可以看到下面查询产生了如下结果行：   
1.是对(deptno,job)进行GROUP BY，即按部门、同一部门相同岗位GROUP BY。统计了同一部门相同岗位的平均工资及同一部门所有岗位的平均工资。   
2.又对每个(job)进行GROUP BY，即不管部门，对表中所有行按JOB列进行GROUP BY。统计了各个岗位的平均工资

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个。

SCOTT@bys1>SELECT nvl(to\_char(deptno),'zongji') as deptno,job,avg(sal) FROM emp  GROUP BY cube(deptno,job);   
DEPTNO                                   JOB         AVG(SAL)   
\---------------------------------------- --------- ----------   
zongji                                             2073.21429   
**zongji                                   CLERK         1037.5  
zongji                                   ANALYST         3000  
zongji                                   MANAGER   2758.33333  
zongji                                   SALESMAN        1400  
zongji                                   PRESIDENT       5000**   
10                                                 2916.66667   
10                                       CLERK           1300   
10                                       MANAGER         2450   
10                                       PRESIDENT       5000   
20                                                       2175   
20                                       CLERK            950   
20                                       ANALYST         3000   
20                                       MANAGER         2975   
30                                                 1566.66667   
30                                       CLERK            950   
30                                       MANAGER         2850   
30                                       SALESMAN        1400   
**实验总结：**ROLLUP非常高效，对一个查询增加的开销非常少；CUBE相对更耗费资源。

在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

### **5.grouping** SETS函数 ###

只返回统计信息，就是上一个查询中提出两个 GROUPING 的两个列全为0或全为1的都过滤掉。

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

### 6.GROUPING\_ID(column\_name1,column\_name2…)----这个没看懂，也没实验 ###

这个返回一个整数，最小为0，这个整数怎么确定，将上面的输入的列，分配以bit，column\_name1 的在column\_name2的左边，

这样就形成了一个二进制数，将它转为10进制就是获得的数了，

怎么确定每位的0和1？

每位的值，和 GROUPING(column\_name)的值是一样的，

例如上面的GROUPING(column\_name1) GROUPING(column\_name2) 为1 和0

则获得的值为 0b10 ，即2.

转载于:https://www.cnblogs.com/sharpest/p/11240349.html

[https_www.cnblogs.com_pangblog_p_3402557.html]: https://www.cnblogs.com/pangblog/p/3402557.html