爱站程序员基地文章目录
- 一.group by 语句
- 二.rollup语句
- 三.cube语句
- 四.group_id()
- 五.grouping
- 六.grouping_id
- 七.grouping set
备注:测试数据库版本为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 testaGROUP BY area, month ORDER BY area, month;
SQL> SELECT area, month, SUM (amount) FROM testa2 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月 1258 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 testaGROUP BY ROLLUP (area, month);
SQL> SELECT NVL (area, \'地区合计\') area, NVL (month, \'月合计\') month, SUM (amount)2 FROM testa3 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地区合计 月合计 154012 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 © union all (_ALL)
求每个区域每个月份汇总以及地区的汇总及每个月份的汇总及总汇总
将rollup修改为cube后，多了每个月销售额的汇总数据
SELECT NVL (area, \'地区合计\') area, NVL (month, \'月合计\') month, SUM (amount)FROM testaGROUP BY cube(area, month);
SQL> SELECT NVL (area, \'地区合计\') area, NVL (month, \'月合计\') month, SUM (amount)2 FROM testa3 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月 12516 rows selected
四.group_id()
group_id()语法
– 区分相同分组标准的分组统计结果，有n组，返回的数字从0到n-1
– 正常情况下比较是少有重复的分组
– 但是对于一些特定的场景，会存在重复
--正常分组SELECT area, month, SUM (amount) s_amount,group_id() groupid FROM testaGROUP BY (area, month)order by area,month;--分组后再来rollupSELECT area, month, SUM (amount) s_amount,group_id() groupid FROM testaGROUP 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 testa2 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 08 rows selectedSQL> --分组后再来rollup--group_id()对于重复的分组进行了标记--最多有3个重复的组,所以标记为0,1,2SQL> SELECT area, month, SUM (amount) s_amount,group_id() groupid FROM testa2 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 224 rows selected
五.grouping
grouping 语法
grouping(expr)
– 若是expr的汇总，则返回1，否则返回0
判断是否是area列的分组
SELECT NVL (area, \'地区合计\') area, NVL (month, \'月合计\') month, SUM (amount) s_amount,grouping(area) grpingFROM testaGROUP BY cube(area, month);
SQL> SELECT NVL (area, \'地区合计\') area, NVL (month, \'月合计\') month, SUM (amount) s_amount,grouping(area) grping2 FROM testa3 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 016 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) grpingFROM testaGROUP BY cube(area, month);
SQL> SELECT NVL (area, \'地区合计\') area, NVL (month, \'月合计\') month, SUM (amount) s_amount,grouping_id(area,month) grping2 FROM testa3 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 016 rows selected
--grouping可以很好的解决cube的时候是按照什么进行分组的SELECT CASE GROUPING (area) || GROUPING (month) WHEN \'00\' THEN \'按地区与月份\'WHEN \'10\' THEN \'按月份\' WHEN \'01\' THEN \'按地区\' WHEN \'11\' THEN \'总合计\'ENDAS GROUPING,CASE GROUPING_ID (area, month) WHEN 0 THEN \'按地区与月份\' WHEN 2 THEN \'按月份\'WHEN 1 THEN \'按地区\' WHEN 3 THEN \'总合计\'ENDAS GROUPING_ID,area, month,SUM (amount) FROM testaGROUP 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 END4 AS GROUPING,5 CASE GROUPING_ID (area, month) WHEN 0 THEN \'按地区与月份\' WHEN 2 THEN \'按月份\'6 WHEN 1 THEN \'按地区\' WHEN 3 THEN \'总合计\'7 END8 AS GROUPING_ID,9 area, month,10 SUM (amount) FROM testa11 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月 12516 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) grpingFROM testaGROUP BY grouping sets(area, month);
SQL> SELECT NVL (area, \'地区合计\') area, NVL (month, \'月合计\') month, SUM (amount) s_amount,grouping_id(area,month) grping2 FROM testa3 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 27 rows selected
这里还有一个grouping set、rollup、group_id()联合使用的例子
求每个区域的汇总、每个月份的汇总以及所有的汇总
--通过rollup来构造总合计SELECT NVL (area, \'地区合计\') area,NVL (month, \'月合计\') month,SUM (amount)FROM testaGROUP BY grouping sets( ROLLUP (area), ROLLUP( month));--去掉其中一个rollup及可以去掉一个重复的总合计SELECT NVL (area, \'地区合计\') area,NVL (month, \'月合计\') month,SUM (amount)FROM testaGROUP BY grouping sets( ROLLUP (area), month);--当然我们也可以用上面学的group_id()进行去重SELECT NVL (area, \'地区合计\') area,NVL (month, \'月合计\') month,SUM (amount)FROM testaGROUP 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 testa5 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地区合计 月合计 15409 rows selectedSQL> --去掉其中一个rollup及可以去掉一个重复的总合计SQL> SELECT NVL (area, \'地区合计\') area,2 NVL (month, \'月合计\') month,3 SUM (amount)4 FROM testa5 GROUP BY grouping sets( ROLLUP (area), month)6 ;AREA MONTH SUM(AMOUNT)-------------------- -------------------- -----------北京 月合计 565广州 月合计 142上海 月合计 833地区合计 月合计 1540地区合计 2月 596地区合计 4月 125地区合计 1月 509地区合计 3月 3108 rows selectedSQL> --当然我们也可以用上面学的group_id()进行去重SQL> SELECT NVL (area, \'地区合计\') area,2 NVL (month, \'月合计\') month,3 SUM (amount)4 FROM testa5 GROUP BY grouping sets( ROLLUP (area), ROLLUP( month))6 having group_id() = 07 ;AREA MONTH SUM(AMOUNT)-------------------- -------------------- -----------北京 月合计 565广州 月合计 142上海 月合计 833地区合计 2月 596地区合计 4月 125地区合计 1月 509地区合计 3月 310地区合计 月合计 15408 rows selected
