高级分组函数之ROLLUP，CUBE，GROUPING SETS

最新推荐文章于 2023-12-13 22:04:49 发布

云博

最新推荐文章于 2023-12-13 22:04:49 发布

阅读量451

点赞数 1

分类专栏： oracle

oracle 专栏收录该内容

3 篇文章

订阅专栏

本文深入探讨了SQL中的ROLLUP，CUBE和GROUPINGSETS函数，这些函数在数据分组和汇总方面提供了强大的功能，相较于传统GROUP BY更高效且灵活。文章通过实例展示了如何使用这些函数来实现复杂的数据分析需求，包括累计累加、交叉组合和区分原始值与统计项的方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

ROLLUP，CUBE，GROUPING SETS 函数可以理解为Group By 分组函数封装后的精简用法，相当于多个union all 的组合显示效果，但是要比多个 union all 的效率要高。

1.group by 函数适用场景：

1）聚合函数，比如 count ,sum,avg等等。

2）筛选只能 having，不能是 where。

3）不能对 clob 类型项目进行 group by。

2.GROUPING SETS，一般的group by 是对选中的列进行分组，如group by （A,B）代表根据列A和B为条件进行分组，而 group by GROUPING SETS(A,B)代表的是分别对列A和B为条件进行分组。

创建一张表 temp

CREATE TABLE emp (
   ID            NUMBER PRIMARY KEY,
   NAME          NVARCHAR2(30),
   sex           VARCHAR2(10),
   birthday      DATE,
   work_location VARCHAR2(30),
   sal           NUMBER(10)
);
COMMENT ON TABLE  emp               IS '员工信息表';
COMMENT ON COLUMN emp.id            IS '员工id';
COMMENT ON COLUMN emp.name          IS '员工姓名';
COMMENT ON COLUMN emp.sex           IS '性别';
COMMENT ON COLUMN emp.birthday      IS '出生日期';
COMMENT ON COLUMN emp.work_location IS '工作地点';
COMMENT ON COLUMN emp.sal           IS '工资';

INSERT INTO emp(ID, NAME, sex, birthday, work_location, sal)
VALUES(1, '小王','男', to_date('1994-12-08','yyyy-mm-dd'), '杭州', 4500);
INSERT INTO emp(ID, NAME, sex, birthday, work_location, sal)
VALUES(2, '小瑞','男', to_date('1995-02-01','yyyy-mm-dd'), '杭州', 8000);
INSERT INTO emp(ID, NAME, sex, birthday, work_location, sal)
VALUES(3, '小倩子','女', to_date('1994-07-08','yyyy-mm-dd'), '上海', 6000);
INSERT INTO emp(ID, NAME, sex, birthday, work_location, sal)
VALUES(4, '小权','男', to_date('1993-01-01','yyyy-mm-dd'), '北京', 8000);
INSERT INTO emp(ID, NAME, sex, birthday, work_location, sal)
VALUES(5, '小优子','男', to_date('1994-12-08','yyyy-mm-dd'), '深圳', 9000);
INSERT INTO emp(ID, NAME, sex, birthday, work_location, sal)
VALUES(6, '小游子','女', to_date('1994-07-08','yyyy-mm-dd'), '深圳', 7500);

3.rollup（累计累加）

1）按列从右递减分组

2）先小计后合计

3）自动排序 order by(1, 2, 3, …, n)

4）若有 N 列，则 group by N+1 次

如： group by rollup(A, B, C)，首先对（A，B，C）进行 group by，然后对（A，B）进行 group by，然后对（A）进行 group by，最后对全表进行 group by。

SELECT A, B, C, SUM（D） FROM table_name GROUP BY ROLLUP(A, B, C)；
等同于
SELECT * from (
SELECT A, B, C, SUM（D） FROM table_name GROUP BY（A, B, C）
UNION ALL
SELECT A, B, null, SUM（D） FROM table_name GROUP BY(A, B, null)
UNION ALL
SELECT A, null, null, SUM（D） FROM table_name GROUP BY (A, null, null)
UNION ALL
SELECT null, null, null, SUM（D） From table_name group by (null, null, null)
) order by 1, 2, 3

这里写图片描述

4.cube(交叉列表)

1）交叉组合，与顺序无关（rollup 则与顺序有关）

2）若有 N 列，则 group by 2^N 次

它比 rollup 扩展更加精细，组合类型更多，对于 cube 来说，列的名字只要一样，那么顺序无所谓，结果都是一样的，因为 cube 是各种可能情况的组合,只不过统计的结果顺序不同而已。但是对于 rollup 来说，列的顺序不同，则结果不同。

如果是GROUP BY CUBE(A, B, C)，GROUP BY顺序：
(A、B、C)
(A、B)
(A、C)
(A)，
(B、C)
(B)
(C)，
最后对全表进行GROUPBY操作

这里写图片描述

5 grouping 用于区分 原有值 和 统计项

1）參数仅仅有一个,并且必须为group by中出现的某一列。

2）grouping = 0 : 数据库中本来的值。

3）grouping = 1 : 统计的结果。

这里写图片描述

6. grouping_id 区分 小计项 和 合计项。Grouping_id()的返回值事实上就是參数中的每列的grouping()值的二进制向量。假设grouping(a)=1，grouping(b)=1，则grouping_id(A,B)的返回值就是二进制的11。转成10进制就是3。

1）參数能够是多个，但必须为group by中出现的列。

2）grouping 用于判断 小计项（grouping(A) = 1）

3）grouping_id 用于判断 合计项（grouping(A, B) = 1 + 1 = 3；小计 + 小计 = 合计）

这里写图片描述