Oracle 聚合实现小计、合计 (GROUP BY ROLLUP)

1.先创建一个测试表

-- Create table 水果月销售记录表
create table C_FRUITS_SALES_RECORD_T
(
  quarter VARCHAR2(20),--季度
  month   VARCHAR2(20),--月份
  apple   NUMBER,--苹果销量
  banana  NUMBER,--香蕉销量
  orange  NUMBER--橘子销量
)

导入测试数据

	QUARTER	MONTH	APPLE	BANANA	ORANGE
1	第一季度	1	50	90	80
2	第一季度	2	65	94	60
3	第一季度	3	80	110	55
4	第二季度	4	120	115	50
5	第二季度	5	75	120	45
6	第二季度	6	65	125	50
7	第三季度	7	80	115	50
8	第三季度	8	80	130	60
9	第三季度	9	95	110	80
10	第四季度	10	120	90	120
11	第四季度	11	135	85	160
12	第四季度	12	80	80	120

现在我们对销售数据出一张统计报表，要求对每季度数据进行小计，所有数据进行合计应该怎么做呢？

我们可能想到使用要用union对明细数据和统计数据进行合并输出，或者在前端进行遍历后生成统计数据

2.使用ROLLUP字句

现在我们有更好的选择，使用RollUp

RollUp是Group By函数的字句，用于对分组数据产生统计数据

针对上述需求我们可以用一下SQL实现

SELECT NVL(T.QUARTER, '合计') AS QUARTER,
       NVL(T.MONTH, '小计') AS MONTH,
       SUM(T.APPLE) AS APPLE,
       SUM(T.BANANA) AS BANANA,
       SUM(T.ORANGE) AS ORANGE
  FROM C_FRUITS_SALES_RECORD_T T
 GROUP BY ROLLUP(T.QUARTER, T.MONTH)
 ORDER BY T.QUARTER, T.MONTH

查询结果如下：

	QUARTER	MONTH	APPLE	BANANA	ORANGE
1	第一季度	1	50	90	80
2	第一季度	2	65	94	60
3	第一季度	3	80	110	55
4	第一季度	小计	195	294	195
5	第三季度	7	80	115	50
6	第三季度	8	80	130	60
7	第三季度	9	95	110	80
8	第三季度	小计	255	355	190
9	第二季度	4	120	115	50
10	第二季度	5	75	120	45
11	第二季度	6	65	125	50
12	第二季度	小计	260	360	145
13	第四季度	10	120	90	120
14	第四季度	11	135	85	160
15	第四季度	12	80	80	120
16	第四季度	小计	335	255	400
17	合计	小计	1045	1264	930

那假如我们只需要合计，不要小计呢？也很简单，使用下边的代码

SELECT NVL(T.QUARTER, '合计') AS QUARTER,
       NVL(T.MONTH, '小计') AS MONTH,
       SUM(T.APPLE) AS APPLE,
       SUM(T.BANANA) AS BANANA,
       SUM(T.ORANGE) AS ORANGE
  FROM C_FRUITS_SALES_RECORD_T T
 GROUP BY ROLLUP((T.QUARTER, T.MONTH))
 ORDER BY T.QUARTER, T.MONTH

大家有注意到区别在哪了吗？

第一个是：GROUP BY ROLLUP(T.QUARTER, T.MONTH)

第二个是：GROUP BY ROLLUP((T.QUARTER, T.MONTH))

我们可以理解为 ROLLUP 括号 中的每个栏位都是一个维度，ROLLUP会按照顺序为 维度生成统计数据，添加在结果集中，而二级括号可以把几个栏位看做一个整体当做一个维度。

如：ROLLUP(A,B,C,D)会在结果集中添加(A,B,C,D)、 (A,B,C)、(A,B)、(A)、(null) 五种 统计数据

    而ROLLUP((A,B),C,D）会在结果集中添加 (A,B,C,D)、(A,B,C)、(A,B)、(null) 四种 统计数据

你也可以使用  GROUP BY  C, D ,ROLLUP((A, B)) 等方法去实现具体需求。

如果觉得有些乱,可以用一下代码,调整  GROUP BY 和 ROLLUP中的栏位看看输出结果

SELECT A, B, C, D, SUM(N)
  FROM (SELECT 1 AS A, 2 AS B, 3 AS C, 4 AS D, 5 AS N FROM DUAL)
 GROUP BY ROLLUP(A, B, C, D)

3.辅助函数

（1）GROUPING()函数：传入一列，用于判断是否为统计行，如上边示例的NVL(T.QUARTER, '合计') AS QUARTER,NVL(T.MONTH, '小计') AS MONTH 可以写作DECODE(GROUPING(T.QUARTER), 1, '合计', T.QUARTER) AS QUARTER,DECODE(GROUPING(T.MONTH), 1, '小计', T.MONTH) AS MONTH

（2）GROUP_ID()函数：无参数，相同分组出现的次数，可以用于过滤重复数据

（3）GROUPING_ID()函数：多列参数，对多列依次使用GROUPING()并对多列结果进行拼接成二进制数值,再转十进制输出结果,如上边 GROUPING_ID(T.QUARTER, T.MONTH) 小计行返回1,合计行返回3

4.相关函数

（1）PIVOT：行转列函数，假如你表格中每种水果不是单独存储，是用一列 种类 的栏位来标示时，你可能需要用到PIVOT函数，具体使用方法可以搜索一下，后边有时间我会详细写一下