扩展GROUP BY之ROLLUP

IT那活儿 2022-12-17

514

点击上方“IT那活儿”公众号，关注后了解更多内容，不管IT什么活儿，干就完了！！！

一

扩展GROUP BY概述

在本章的开头已经简单描述了扩展GROUP BY的应用场景，Oracle扩展GROUP BY允许使用SQL语句对数据汇总结果进行多维展现，从而生成复杂的报表，为决策者提供有效的数据支持。最重要的是，Oracle9i之后，扩展GROUP BY的功能已经趋于完善，能够满足大部分多维数据分析统计的工作。

主要表现在：

ROLLUP、CUBE、GROUPING SETS扩展GROUP BY子句提供了不同多维分组统计功能。
3个扩展分组函数：GROUPING、GROUPING_ID、GROUP_ID提供扩展GROUP BY的辅助功能：提供区别结果行属于哪个分组级别，区分NULL值，建立有意义的报表，对汇总结果排序，过滤结果行等功能。
对扩展GROUP BY允许按重复列分组、组合列分组、部分分组、连接分组等复杂功能，另外GROUPING SETS可以接受CUBE、ROLLUP操作作为参数，这些功能使扩展GROUP BY更加强大。

通过SQL语句对上述功能的组合使用，就可以实现制作复杂的多维分析报表的功能。针对不同维度的报表统计，使用扩展GROUP BY的强大功能很容易实现，而且SQL编写更简单，性能也比同等的UNION ALL更好，在后面的内容中，我们会见识到强大的扩展GROUP BY功能。

还需要强调一点，扩展GROUP BY还是需要符合GROUP BY的相关语法语义规则的。比如SELECT中引用的列必须是分组列。

本章的表DEPT和EMP都来自于SCOTT用户下，虽然表比较简单，但是足以能说明扩展GROUP BY的功能。下面开始进入主要内容，探讨强大的扩展GROUP BY功能。

二

ROLLUP

在数据仓库系统中，一般多维分析报表，光有标准分组还不行，小计和合计往往是报表的核心内容，这种需求使用ROLLUP很常见，如果使用同等的UNION ALL实现，ROLLUP中的列越多，则需要写的SQL语句就越复杂，所以与UNION ALL相比，ROLLUP写法简单，而且性能一般更好。本章主要探讨普通ROLLUP和部分ROLLUP提供的强大功能。

2.1 UNION ALL实现ROLLUP功能

假设有这样的需求：

1）统计每个部门每个职位的薪水和
2) 统计每个部门所有职位的薪水小计
3）统计所有部门所有职位的薪水合计
4）需要显示部门名、职位名和累加后的薪水值

如果不知道Oracle中有扩展GROUP BY功能，那么很容易想起使用UNION ALL解决，的确在Oracle8i之前，没有扩展GROUP BY功能，就要使用UNION ALL解决了，写3条SQL分别完成3个分组功能，然后用UNION ALL连接：

--需求1实现
SELECT  a.dname,b.job,SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP  BY a.dname,b.job
UNION ALL

--需求2实现
SELECT  a.dname,NULL, SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP  BY a.dname
UNION ALL

--需求3实现
SELECT  NULL,NULL, SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno;

显示结果如下：

DNAME JOB SUM_SAL
---------------------------- ------------------ ----------
SALES MANAGER 2850
SALES CLERK                     950
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000
ACCOUNTING CLERK                    1300
SALES SALESMAN 5600
RESEARCH MANAGER 2975
RESEARCH ANALYST 3000
RESEARCH CLERK                     800
ACCOUNTING 8750
RESEARCH 6775
SALES 9400
24925

已选择13行。

执行计划：

----------------------------------------------------------
Plan hash value: 3113041979

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |    17 | 423 |    13  (70)| 00:00:01 |
| 1 |  UNION-ALL | |       | |            | |
| 2 |   HASH GROUP BY | |    12 | 336 |     5  (20)| 00:00:01 |
| 3 |    NESTED LOOPS | |    12 | 336 |     4   (0)| 00:00:01 |
| 4 |     TABLE ACCESS FULL | EMP |    12 | 180 |     3   (0)| 00:00:01 |
| 5 |     TABLE ACCESS BY INDEX ROWID| DEPT |     1 | 13 |     1   (0)| 00:00:01 |
|* 6 |      INDEX UNIQUE SCAN | PK_DEPT |     1 | |     0   (0)| 00:00:01 |
| 7 |   HASH GROUP BY | |     4 | 80 |     5  (20)| 00:00:01 |
| 8 |    NESTED LOOPS | |    12 | 240 |     4   (0)| 00:00:01 |
| 9 |     TABLE ACCESS FULL | EMP |    12 | 84 |     3   (0)| 00:00:01 |
| 10 |     TABLE ACCESS BY INDEX ROWID| DEPT |     1 | 13 |     1   (0)| 00:00:01 |
|* 11 |      INDEX UNIQUE SCAN | PK_DEPT |     1 | |     0   (0)| 00:00:01 |
| 12 |   SORT AGGREGATE | |     1 | 7 |            | |
|* 13 |    TABLE ACCESS FULL | EMP |    12 | 84 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

6 - access("A"."DEPTNO"="B"."DEPTNO")
11 - access("A"."DEPTNO"="B"."DEPTNO")
13 - filter("B"."DEPTNO" IS NOT NULL)

从执行计划可以看出，为了实现这样的需求，需要多次访问EMP、DEPT表以及DEPT表的索引，这里的测试数据很少而且表结构简单，实际应用中表结构可能很复杂，经常是多表关联，数据量可能达到百万级，千万级，甚至上亿，那么使用UNION ALL，明显性能低下，如果使用WITH子句将常规分组的结果固定下来，然后在此基础上再聚合，效率可能比单纯UNION ALL好，但是还是不够完美，现在的需求是对2列进行多维分析，如果是很多列呢?必然增加语句复杂度，类似地，CUBE,GROUPING SETS用UNION ALL改写也有此类问题，何况扩展GROUP BY还提供了很多复杂功能，用UNION ALL改写就更加麻烦了。

试想对于上面的需求，如果Oracle能提供一个这样的分组功能就好了：

直接把分组的列按顺序写在一起，提供一个简单的语法结构。
此语法结构先进行全分组：标准分组。
然后这个语法结构从右到左递减列，做对应维度的分组，实现小计和合计。

如果有实现这3个要求的语法结构，那么上述需求就能很容易地实现，的确，Oracle提供了这样的功能，那就是ROLLUP分组。

2.2 ROLLUP分组

Oracle使用ROLLUP对GROUP BY进行扩展：它允许计算标准分组以及相应维度的小计、合计功能。ROLLUP的语法结构很简单：

SELECT … GROUP BY ROLLUP(grouping_column_reference_list)

ROLLUP后面指定的列以逗号分割，ROLLUP的计算结果和其后面指定列的顺序有关，因为ROLLUP分组过程具有方向性：先计算标准分组，然后列从右到左递减计算更高一级的小计，一直到列全部被选完，最后计算合计。这样如果ROLLUP中指定n列，则整个计算过程中的分组方式有n+1种。下面就使用ROLLUP实现1.2.1的需求，如下：

SELECT  a.dname,b.job, SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP  BY ROLLUP(a.dname,b.job);

显示结果如下：

DNAME JOB SUM_SAL
---------------------------- ------------------ ----------
SALES CLERK                     950
SALES MANAGER 2850
SALES SALESMAN 5600
SALES 9400
RESEARCH CLERK                     800
RESEARCH ANALYST 3000
RESEARCH MANAGER 2975
RESEARCH 6775
ACCOUNTING CLERK                    1300
ACCOUNTING MANAGER 2450
ACCOUNTING PRESIDENT 5000
ACCOUNTING 8750
       24925

已选择13行。

执行计划：

----------------------------------------------------------
Plan hash value: 503922295

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |    12 | 336 |     5  (20)| 00:00:01 |
| 1 |  SORT GROUP BY ROLLUP | |    12 | 336 |     5  (20)| 00:00:01 |
| 2 |   NESTED LOOPS | |    12 | 336 |     4   (0)| 00:00:01 |
| 3 |    TABLE ACCESS FULL | EMP |    12 | 180 |     3   (0)| 00:00:01 |
| 4 |    TABLE ACCESS BY INDEX ROWID| DEPT |     1 | 13 |     1   (0)| 00:00:01 |
|* 5 |     INDEX UNIQUE SCAN | PK_DEPT |     1 | |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

5 - access("A"."DEPTNO"="B"."DEPTNO")

从上面的结果可以看出，使用ROLLUP只需要访问EMP表一次，通过DEPT表的主键获得ROWID回表查询相关行，比前面的UNION ALL性能要好，而且从执行计划上看出有一个特别的操作：SORT GROUP BY ROLLUP，这说明：这里的ROLLUP的计算结果是有顺序的，所以展示的结果很直观，但是一般还是要显式排序的，因为默认排序很多时候不符合业务需求，另外，默认排序是受执行计划影响的，可能有的ROLLUP就无序了，在重复列分组中就会说到没有排序的情况，对于显式排序，后面说到的GROUPING_ID函数会专门解决扩展分组排序问题。

ROLLUP分组具有方向性，从上面的结果看出，ROLLUP(a.dname,b.job)分组的过程是这样的：

标准分组：GROUP BY(a.dname,b.job)，对每个部门每个职位进行分组(我分析的时候经常对GROUP BY加上括号，其实没有必要，只是为了更直观而已)。
从右到左递减：GROUP BY(a.dname,NULL),其实这个NULL没有必要使用，这里只是为了方便分析。这个过程是对上个级别分组的小计,也就是对每个dname值，计算横跨所有job的小计。
最后合计：相当于GROUP BY (NULL,NULL)。

上面的ROLLUP只用了两个列，如果有n个列，那么结果就是n+1种GROUP BY的组合，从右到左递减的过程中，下一个分组就是对上一个分组的小计，最后合计，比如ROLLUP(a,b,c)，那么有：

分组级别	描述
a,b,c	标准分组
a,b	对于每个a,b列值，计算横跨c列的小计
a	对于每个a列值，计算横跨b,c列的小计
合计汇总	合计

另外提一下，其实ROLLUP操作，如果使用HINT: expand_gset_to_union，那么则优化器会将ROLLUP转为对应的UNION ALL操作，其它的GROUPING SETS、CUBE也可以，有兴趣的可以试一下。

ROLLUP语法简单，而且具体处理过程也很简单，除了第1个是标准分组，然后就是列从右到左递减的分组，最后合计。下面实现需求：

计算每个入职时间(年)、部门、职位的标准分组的薪水和。
计算每个入职时间(年)、部门的所有职位的薪水小计。
计算每个入职时间(年)的所有部门所有职位的薪水小计。
最后合计薪水，显示入职时间(年)、部门名、职位名。

从上面学到的ROLLUP知识，可以很容易地编写SQL实现上述需求，将hire_date格式化到年，部门名dname,职位名job按顺序放到ROLLUP中即可。

SELECT to_char(b.hiredate,'yyyy') hire_year,a.dname,b.job, SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY ROLLUP(to_char(b.hiredate,'yyyy'),a.dname,b.job);

结果如下：

HIRE_YEA DNAME JOB SUM_SAL
-------- ---------------------------- ------------------ ----------
1980     RESEARCH CLERK                     800
1980     RESEARCH 800
1980                                                            800
1981     SALES CLERK                     950
1981     SALES MANAGER 2850
1981     SALES SALESMAN 5600
1981     SALES 9400
1981     RESEARCH ANALYST 3000
1981     RESEARCH MANAGER 2975
1981     RESEARCH 5975
1981     ACCOUNTING MANAGER 2450
1981     ACCOUNTING PRESIDENT 5000
1981     ACCOUNTING 7450
1981                                                          22825
1982     ACCOUNTING CLERK                    1300
1982     ACCOUNTING 1300
1982                                                           1300
                                         24925

从结果看出，编写的SQL是实现了上述需求的。下面选些结果中的典型记录对此结果进行分析，其它行分析类似：

相关行	描述
第7行	对第4、5、6行的所有JOB进行小计
第14行	对第7行、第13行的所有DNAME、JOB进行小计，当然也相当于对4-6、8-12行所有DNAME、JOB的小计。
最后一行	对所有入职日期(精确到年)、DNAME、JOB进行合计

因为ROLLUP分组过程具有方向性，所以通过改变ROLLUP中列的顺序就可以达到改变报表结果和含义的目的。比如将前面的ROLLUP(dname,job)改为ROLLUP(job,dname)则含义就发生了变化，现在需要查询的就是标准分组、计算每个job的所有部门的小计、最后合计，这里就两个列，也就是小计的含义发生了变化，请看：

SELECT  b.job,a.dname, SUM(b.sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP  BY ROLLUP(b.job,a.dname);

显示结果如下：

JOB DNAME SUM_SAL
------------------ ---------------------------- ----------
CLERK              SALES 950
CLERK              RESEARCH 800
CLERK              ACCOUNTING 1300
CLERK                                                 3050
ANALYST RESEARCH 3000
ANALYST 3000
MANAGER SALES 2850
MANAGER RESEARCH 2975
MANAGER ACCOUNTING 2450
MANAGER 8275
SALESMAN SALES 5600
SALESMAN 5600
PRESIDENT ACCOUNTING 5000
PRESIDENT 5000
24925

已选择15行。

从结果看出，与ROLLUP(dname,job)的确发生了变化，小计是对每个职位的所有部门进行小计，一定要牢记ROLLUP列的顺序对结果的影响，才能灵活地使用ROLLUP实现多维分析统计。

2.3 部分ROLLUP分组

2.2中的ROLLUP是普通的ROLLUP，有标准分组、多种小计、合计。如果现在的报表不需要某些小计、合计，那么怎么办呢?有办法，Oracle提供了部分ROLLUP分组功能，也就说将部分列从ROLLUP中移出来，放在GROUP BY中，这样合计肯定没有了，某些小计也没有了。如ROLLUP(to_char(b.hiredate,'yyyy'),a.dname,b.job)，现在不需要每个入职时间(年)的所有部门所有职位的薪水小计，合计也不需要，那么改写为：

SELECT to_char(b.hiredate,'yyyy') hire_year,a.dname,b.job, SUM(sal) sum_sal
FROM dept a,emp b
WHERE a.deptno = b.deptno
GROUP BY to_char(b.hiredate,'yyyy'),a.dname,ROLLUP(b.job);

将hiredate和dname从ROLLUP中移出来，就可以将每个入职时间(年)的所有部门所有职位的薪水小计以及合计剔除。最终只查询标准分组和每个入职时间(年)、部门的所有职位的小计。部分ROLLUP提供了报表的额外选择，当然，很多情况下合计还是需要的，能不能既剔除部分小计，又能保留合计呢?有很多方法，有时候可以很容易用组合列分组、连接分组等解决，有时候使用扩展分组函数剔除比较简单，根据具体情况使用合适方法，部分CUBE类似。

ROLLUP总结

在数据仓库系统中，经常需要对相关表进行多维汇总统计，如果分组有规律，比如先进行常规分组，在常规分组基础上通过将列从右到左移动，然后进行更高一级的小计，最后合计，这样一般就需要使用ROLLUP，ROLLUP结果和列的顺序有关，顺序不同结果则不同，这为报表实现提供了很大的灵活性。

ROLLUP中指定n列，则根据ROLLUP的分组过程可以知道，分组方式有n+1种。另外部分ROLLUP可以剔除某些不需要的小计和合计，只要根据需求将相关列从ROLLUP中移出，放到GROUP BY中即可。

本文作者：丁俊(上海新炬中北团队）

本文来源：“IT那活儿”公众号

sql分组

文章转载自IT那活儿，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。

扩展GROUP BY之ROLLUP

2.1 UNION ALL实现ROLLUP功能

2.3 部分ROLLUP分组

ROLLUP总结

本文作者：丁 俊(上海新炬中北团队）

本文来源：“IT那活儿”公众号

评论

本文作者：丁俊(上海新炬中北团队）