备注:测试数据库版本为Oracle 11g R2
这个blog我们来聊聊常见的Oracle的高级分组函数
测试数据:
DROP TABLE testa;
CREATE TABLE testa (area VARCHAR2 (20), month VARCHAR2 (20),
amount NUMBER);
insert into testa values ('上海', '1月', 199);
insert into testa values ('上海', '2月', 199);
insert into testa values ('上海', '3月', 155);
insert into testa values ('上海', '3月', 155);
insert into testa values ('上海', '4月', 125);
insert into testa values ('广州', '1月', 75);
insert into testa values ('广州', '2月', 67);
insert into testa values ('北京', '1月', 235);
insert into testa values ('北京', '2月', 330);
Commit;
一.group by 语句
求每个区域，每个月的销售额
SELECT area, month, SUM (amount) FROM testa
GROUP BY area, month ORDER BY area, month;
SQL> SELECT area, month, SUM (amount) FROM testa
2 GROUP BY area, month ORDER BY area, month;
AREA MONTH SUM(AMOUNT)
-------------------- -------------------- -----------
北京 1月 235
北京 2月 330
广州 1月 75
广州 2月 67
上海 1月 199
上海 2月 199
上海 3月 310
上海 4月 125
8 rows selected
二.rollup语句
rollup语法
--可跟一个到多个表达式
--记表达式数量为n，会生成n+1组分类汇总的统计结果
--rollup(a)=(a) union all (_ALL)
--rollup(a,b)=(a,b) union all (a) union all (_ALL)
--rollup(a,b,c)=(a,b,c) union all (a,b) union all (a) union all (_ALL)
求每个区域每个月份汇总以及地区的汇总及总汇总
SELECT NVL (area, '地区合计') area, NVL (month, '月合计') month, SUM (amount)
FROM testa
GROUP BY ROLLUP (area, month);
SQL> SELECT NVL (area, '地区合计') area, NVL (month, '月合计') month, SUM (amount)
2 FROM testa
3 GROUP BY ROLLUP (area, month);
AREA MONTH SUM(AMOUNT)
-------------------- -------------------- -----------
北京 1月 235
北京 2月 330
北京 月合计 565
广州 1月 75
广州 2月 67
广州 月合计 142
上海 1月 199
上海 2月 199
上海 3月 310
上海 4月 125
上海 月合计 833
地区合计 月合计 1540
12 rows selected
三.cube语句
cube语法
--可跟一个到多个表达式
--记表达式数量为n，会生成power(2,n)组分类汇总的统计结果
--cube(a)=(a) union all (_ALL),
--cube(a,b)=(a,b) union all (a) union all (b) union all (_ALL)
–cube(a,b,c)=(a,b,c) union all (a,b) union all (a,c) union all (b,c) union all (a) union all (b)
union all (c) union all (_ALL)
求每个区域每个月份汇总以及地区的汇总及每个月份的汇总及总汇总
将rollup修改为cube后，多了每个月销售额的汇总数据
SELECT NVL (area, '地区合计') area, NVL (month, '月合计') month, SUM (amount)
FROM testa
GROUP BY cube(area, month);
SQL> SELECT NVL (area, '地区合计') area, NVL (month, '月合计') month, SUM (amount)
2 FROM testa
3 GROUP BY cube(area, month);
AREA MONTH SUM(AMOUNT)
-------------------- -------------------- -----------
地区合计 月合计 1540
地区合计 1月 509
地区合计 2月 596
地区合计 3月 310
地区合计 4月 125
北京 月合计 565
北京 1月 235
北京 2月 330
广州 月合计 142
广州 1月 75
广州 2月 67
上海 月合计 833
上海 1月 199
上海 2月 199
上海 3月 310
上海 4月 125
16 rows selected
四.group_id()
group_id()语法
-- 区分相同分组标准的分组统计结果，有n组，返回的数字从0到n-1
-- 正常情况下比较是少有重复的分组
-- 但是对于一些特定的场景，会存在重复
--正常分组
SELECT area, month, SUM (amount) s_amount,group_id() groupid FROM testa
GROUP BY (area, month)
order by area,month;
--分组后再来rollup
SELECT area, month, SUM (amount) s_amount,group_id() groupid FROM testa
GROUP BY (area, month),rollup(area,month)
order by area,month;
SQL> --正常分组
--group_id()全部是0，表示没有重复的分组
SQL> SELECT area, month, SUM (amount) s_amount,group_id() groupid FROM testa
2 GROUP BY (area, month)
3 order by area,month;
AREA MONTH S_AMOUNT GROUPID
-------------------- -------------------- ---------- ----------
北京 1月 235 0
北京 2月 330 0
广州 1月 75 0
广州 2月 67 0
上海 1月 199 0
上海 2月 199 0
上海 3月 310 0
上海 4月 125 0
8 rows selected
SQL> --分组后再来rollup
--group_id()对于重复的分组进行了标记
--最多有3个重复的组,所以标记为0,1,2
SQL> SELECT area, month, SUM (amount) s_amount,group_id() groupid FROM testa
2 GROUP BY (area, month),rollup(area,month)
3 order by area,month;
AREA MONTH S_AMOUNT GROUPID
-------------------- -------------------- ---------- ----------
北京 1月 235 1
北京 1月 235 2
北京 1月 235 0
北京 2月 330 2
北京 2月 330 0
北京 2月 330 1
广州 1月 75 1
广州 1月 75 2
广州 1月 75 0
广州 2月 67 1
广州 2月 67 2
广州 2月 67 0
上海 1月 199 0
上海 1月 199 1
上海 1月 199 2
上海 2月 199 0
上海 2月 199 2
上海 2月 199 1
上海 3月 310 1
上海 3月 310 0
上海 3月 310 2
上海 4月 125 0
上海 4月 125 1
上海 4月 125 2
24 rows selected
五.grouping
grouping 语法
grouping(expr)
-- 若是expr的汇总，则返回1，否则返回0
判断是否是area列的分组
SELECT NVL (area, '地区合计') area, NVL (month, '月合计') month, SUM (amount) s_amount,grouping(area) grping
FROM testa
GROUP BY cube(area, month);
SQL> SELECT NVL (area, '地区合计') area, NVL (month, '月合计') month, SUM (amount) s_amount,grouping(area) grping
2 FROM testa
3 GROUP BY cube(area, month);
AREA MONTH S_AMOUNT GRPING
-------------------- -------------------- ---------- ----------
地区合计 月合计 1540 1
地区合计 1月 509 1
地区合计 2月 596 1
地区合计 3月 310 1
地区合计 4月 125 1
北京 月合计 565 0
北京 1月 235 0
北京 2月 330 0
广州 月合计 142 0
广州 1月 75 0
广州 2月 67 0
上海 月合计 833 0
上海 1月 199 0
上海 2月 199 0
上海 3月 310 0
上海 4月 125 0
16 rows selected
六.grouping_id
grouping_id语法:
grouping_id(expr1,expr2,expr3,......)
-- 为n个expr构成的分组标准产生的分组统计结果生成区分ID
-- 可将每个表达式想象成一个二进制数位，将这些二进制数位之和转为十进制
-- 若本行是某expr的汇总，那么该expr对应的二进制数是1，否则为0
-- 可以，grouping 是grouping_id的简化版本
cube总共合计是0~power(2,n)-1,如果配合上grouping_id的话，值的范围相同
grouping_id(a,b)的话，所有的合计为3，b的合计为2，a的合计为1，a，b的合计取值为0
SELECT NVL (area, '地区合计') area, NVL (month, '月合计') month, SUM (amount) s_amount,grouping_id(area,month) grping
FROM testa
GROUP BY cube(area, month);
SQL> SELECT NVL (area, '地区合计') area, NVL (month, '月合计') month, SUM (amount) s_amount,grouping_id(area,month) grping
2 FROM testa
3 GROUP BY cube(area, month);
AREA MONTH S_AMOUNT GRPING
-------------------- -------------------- ---------- ----------
地区合计 月合计 1540 3
地区合计 1月 509 2
地区合计 2月 596 2
地区合计 3月 310 2
地区合计 4月 125 2
北京 月合计 565 1
北京 1月 235 0
北京 2月 330 0
广州 月合计 142 1
广州 1月 75 0
广州 2月 67 0
上海 月合计 833 1
上海 1月 199 0
上海 2月 199 0
上海 3月 310 0
上海 4月 125 0
16 rows selected
--grouping可以很好的解决cube的时候是按照什么进行分组的
SELECT CASE GROUPING (area) || GROUPING (month) WHEN '00' THEN '按地区与月份'
WHEN '10' THEN '按月份' WHEN '01' THEN '按地区' WHEN '11' THEN '总合计'
END
AS GROUPING,
CASE GROUPING_ID (area, month) WHEN 0 THEN '按地区与月份' WHEN 2 THEN '按月份'
WHEN 1 THEN '按地区' WHEN 3 THEN '总合计'
END
AS GROUPING_ID,
area, month,
SUM (amount) FROM testa
GROUP BY CUBE (area, month);
SQL> SELECT CASE GROUPING (area) || GROUPING (month) WHEN '00' THEN '按地区与月份'
2 WHEN '10' THEN '按月份' WHEN '01' THEN '按地区' WHEN '11' THEN '总合计'
3 END
4 AS GROUPING,
5 CASE GROUPING_ID (area, month) WHEN 0 THEN '按地区与月份' WHEN 2 THEN '按月份'
6 WHEN 1 THEN '按地区' WHEN 3 THEN '总合计'
7 END
8 AS GROUPING_ID,
9 area, month,
10 SUM (amount) FROM testa
11 GROUP BY CUBE (area, month);
GROUPING GROUPING_ID AREA MONTH SUM(AMOUNT)
------------ ------------ -------------------- -------------------- -----------
总合计 总合计 1540
按月份 按月份 1月 509
按月份 按月份 2月 596
按月份 按月份 3月 310
按月份 按月份 4月 125
按地区 按地区 北京 565
按地区与月份 按地区与月份 北京 1月 235
按地区与月份 按地区与月份 北京 2月 330
按地区 按地区 广州 142
按地区与月份 按地区与月份 广州 1月 75
按地区与月份 按地区与月份 广州 2月 67
按地区 按地区 上海 833
按地区与月份 按地区与月份 上海 1月 199
按地区与月份 按地区与月份 上海 2月 199
按地区与月份 按地区与月份 上海 3月 310
按地区与月份 按地区与月份 上海 4月 125
16 rows selected
七.grouping set
有时候分组需求无需rollup和cube那么多
例如我只想要 A的分组和B的分组、A的分组和BC的分组
这个时候我只能 group by A union all group by B、 group by A union all group by B,C
这个时候grouping set的出现就可以很好的解决这个问题
GROUP BY GROUPING SETS (A,B) 等价与 GROUP BY A UNION ALL GROUP BY B
GROUP BY GROUPING SETS (A,B,C) 等价与 GROUP BY A UNION ALL GROUP BY B UNION ALL GROUP BY C
GROUP BY GROUPING SETS ((A,B,C)) 等价与 GROUP BY A,B,C
GROUP BY GROUPING SETS (A,(B,C)) 等价与 GROUP BY A UNION ALL GROUP BY B,C
SELECT NVL (area, '地区合计') area, NVL (month, '月合计') month, SUM (amount) s_amount,grouping_id(area,month) grping
FROM testa
GROUP BY grouping sets(area, month);
SQL> SELECT NVL (area, '地区合计') area, NVL (month, '月合计') month, SUM (amount) s_amount,grouping_id(area,month) grping
2 FROM testa
3 GROUP BY grouping sets(area, month);
AREA MONTH S_AMOUNT GRPING
-------------------- -------------------- ---------- ----------
北京 月合计 565 1
广州 月合计 142 1
上海 月合计 833 1
地区合计 2月 596 2
地区合计 4月 125 2
地区合计 1月 509 2
地区合计 3月 310 2
7 rows selected
这里还有一个grouping set、rollup、group_id()联合使用的例子
求每个区域的汇总、每个月份的汇总以及所有的汇总
--通过rollup来构造总合计
SELECT NVL (area, '地区合计') area,
NVL (month, '月合计') month,
SUM (amount)
FROM testa
GROUP BY grouping sets( ROLLUP (area), ROLLUP( month))
;
--去掉其中一个rollup及可以去掉一个重复的总合计
SELECT NVL (area, '地区合计') area,
NVL (month, '月合计') month,
SUM (amount)
FROM testa
GROUP BY grouping sets( ROLLUP (area), month)
;
--当然我们也可以用上面学的group_id()进行去重
SELECT NVL (area, '地区合计') area,
NVL (month, '月合计') month,
SUM (amount)
FROM testa
GROUP BY grouping sets( ROLLUP (area), ROLLUP( month))
having group_id() = 0
;
SQL> --通过rollup来构造总合计
SQL> SELECT NVL (area, '地区合计') area,
2 NVL (month, '月合计') month,
3 SUM (amount)
4 FROM testa
5 GROUP BY grouping sets( ROLLUP (area), ROLLUP( month))
6 ;
AREA MONTH SUM(AMOUNT)
-------------------- -------------------- -----------
北京 月合计 565
广州 月合计 142
上海 月合计 833
地区合计 2月 596
地区合计 4月 125
地区合计 1月 509
地区合计 3月 310
地区合计 月合计 1540
地区合计 月合计 1540
9 rows selected
SQL> --去掉其中一个rollup及可以去掉一个重复的总合计
SQL> SELECT NVL (area, '地区合计') area,
2 NVL (month, '月合计') month,
3 SUM (amount)
4 FROM testa
5 GROUP BY grouping sets( ROLLUP (area), month)
6 ;
AREA MONTH SUM(AMOUNT)
-------------------- -------------------- -----------
北京 月合计 565
广州 月合计 142
上海 月合计 833
地区合计 月合计 1540
地区合计 2月 596
地区合计 4月 125
地区合计 1月 509
地区合计 3月 310
8 rows selected
SQL> --当然我们也可以用上面学的group_id()进行去重
SQL> SELECT NVL (area, '地区合计') area,
2 NVL (month, '月合计') month,
3 SUM (amount)
4 FROM testa
5 GROUP BY grouping sets( ROLLUP (area), ROLLUP( month))
6 having group_id() = 0
7 ;
AREA MONTH SUM(AMOUNT)
-------------------- -------------------- -----------
北京 月合计 565
广州 月合计 142
上海 月合计 833
地区合计 2月 596
地区合计 4月 125
地区合计 1月 509
地区合计 3月 310
地区合计 月合计 1540
8 rows selected
