在日常数据统计报表的过程中，会遇到在统计数据的最后增加合计，那在Oracle中有什么函数来实现这个合计呢？下面我们就通过一些示例来看下。本文中主要对group by,rollup,cube,grouping,grouping_id,grouping sets等函数的用法做一定的介绍。

一、创建测试表并添加测试数据
--创建测试表
drop table my_test;
create table my_test(
city_id       varchar2(20),
city_name     varchar2(30),
sales_num     integer,
sales_price   number
)
;
--添加测试数据
insert into my_test
select '0851','贵阳',2000,1680 from dual
union all
select '0852','遵义',1200,1280 from dual
union all
select '0853','安顺',600,1880 from dual
union all
select '0854','黔南',300,680 from dual
union all
select '0855','黔东南',60,780 from dual
union all
select '0856','铜仁',120,1000 from dual
union all
select '0857','毕节',430,1230 from dual
union all
select '0858','六盘水',670,1150 from dual
union all
select '0859','黔西南',890,1670 from dual

--查看测试数据
select * from my_test;
CITY_ID              CITY_NAME                                                    SALES_NUM SALES_PRICE
-------------------- ------------------------------ --------------------------------------- -----------
0851                 贵阳                                                              2000        1680
0852                 遵义                                                              1200        1280
0853                 安顺                                                               600        1880
0854                 黔南                                                               300         680
0855                 黔东南                                                              60         780
0856                 铜仁                                                               120        1000
0857                 毕节                                                               430        1230
0858                 六盘水                                                             670        1150
0859                 黔西南                                                             890        1670


二、ROLLUP函数和CUBE函数的使用示例对比
--1、普通分组查询
select city_id,city_name,sum(sales_num)
from my_test
group by city_id,city_name
order by city_id;

CITY_ID              CITY_NAME                      SUM(SALES_NUM)
-------------------- ------------------------------ --------------
0851                 贵阳                                     2000
0852                 遵义                                     1200
0853                 安顺                                      600
0854                 黔南                                      300
0855                 黔东南                                     60
0856                 铜仁                                      120
0857                 毕节                                      430
0858                 六盘水                                    670
0859                 黔西南                                    890

--2、对city_id进行rollup和cube 对比查询
--2.1 对city_id进行普通的roolup操作
select city_id,city_name,grouping(city_id)g_city_id,grouping(city_name)g_city_name,sum(sales_num)
from my_test
group by rollup(city_id),city_name
order by city_id;

CITY_ID              CITY_NAME                       G_CITY_ID G_CITY_NAME SUM(SALES_NUM)
-------------------- ------------------------------ ---------- ----------- --------------
0851                 贵阳                                    0           0           2000
0852                 遵义                                    0           0           1200
0853                 安顺                                    0           0            600
0854                 黔南                                    0           0            300
0855                 黔东南                                  0           0             60
0856                 铜仁                                    0           0            120
0857                 毕节                                    0           0            430
0858                 六盘水                                  0           0            670
0859                 黔西南                                  0           0            890
                     铜仁                                    1           0            120
                     贵阳                                    1           0           2000
                     黔南                                    1           0            300
                     毕节                                    1           0            430
                     遵义                                    1           0           1200
                     安顺                                    1           0            600
                     黔东南                                  1           0             60
                     黔西南                                  1           0            890
                     六盘水                                  1           0            670

结果说明：grouping函数返回值有"0" 和"1"。
0表示此行对应的这列未参与ROLLUP函数分组汇总活动
1表示对应的列(例如city_id字段)是由于rollup函数所产生的空值对应的信息，即对此列进行汇总计算后的结果


--2.2 对city_id进行普通的cube操作
select city_id,city_name,grouping(city_id)g_city_id,grouping(city_name)g_city_name,sum(sales_num)
from my_test
group by cube(city_id),city_name
order by city_id;

CITY_ID              CITY_NAME                       G_CITY_ID G_CITY_NAME SUM(SALES_NUM)
-------------------- ------------------------------ ---------- ----------- --------------
0851                 贵阳                                    0           0           2000
0852                 遵义                                    0           0           1200
0853                 安顺                                    0           0            600
0854                 黔南                                    0           0            300
0855                 黔东南                                  0           0             60
0856                 铜仁                                    0           0            120
0857                 毕节                                    0           0            430
0858                 六盘水                                  0           0            670
0859                 黔西南                                  0           0            890
                     黔西南                                  1           0            890
                     黔东南                                  1           0             60
                     六盘水                                  1           0            670
                     黔南                                    1           0            300
                     遵义                                    1           0           1200
                     贵阳                                    1           0           2000
                     毕节                                    1           0            430
                     安顺                                    1           0            600
                     铜仁                                    1           0            120

结果说明：grouping函数返回值有"0" 和"1"。
0 表示此行对应的这列未参与cube函数分组汇总活动
1 表示对应的列(例如city_id字段)是由于rollup函数所产生的空值对应的信息，即对此列进行汇总计算后的结果


--3、ROLLUP 和cube 两列的情况
--3.1 rollup两列的情况
select decode(grouping_id(city_id,city_name),2,'小计',3,'总计',city_id)city_id,
       decode(grouping_id(city_id,city_name),2,'小计',3,'总计',city_name)city_name,
       grouping(city_id)g_city_id,
       grouping(city_name)g_city_name,
       grouping_id(city_id,city_name)gp_id,
       sum(sales_num),sum(sales_price)
from my_test
group by rollup(city_id,city_name)
order by city_id;

CITY_ID              CITY_NAME                       G_CITY_ID G_CITY_NAME      GP_ID SUM(SALES_NUM) SUM(SALES_PRICE)
-------------------- ------------------------------ ---------- ----------- ---------- -------------- ----------------
0851                 贵阳                                    0           0          0           2000             1680
0851                                                         0           1          1           2000             1680
0852                 遵义                                    0           0          0           1200             1280
0852                                                         0           1          1           1200             1280
0853                 安顺                                    0           0          0            600             1880
0853                                                         0           1          1            600             1880
0854                 黔南                                    0           0          0            300              680
0854                                                         0           1          1            300              680
0855                 黔东南                                  0           0          0             60              780
0855                                                         0           1          1             60              780
0856                 铜仁                                    0           0          0            120             1000
0856                                                         0           1          1            120             1000
0857                 毕节                                    0           0          0            430             1230
0857                                                         0           1          1            430             1230
0858                 六盘水                                  0           0          0            670             1150
0858                                                         0           1          1            670             1150
0859                 黔西南                                  0           0          0            890             1670
0859                                                         0           1          1            890             1670
总计                 总计                                    1           1          3           6270            11350


--3.2 rollup两列的情况(rollup的另类用法)
select decode(grouping_id(city_id,city_name),2,'小计',3,'总计',city_id)city_id,
       decode(grouping_id(city_id,city_name),2,'小计',3,'总计',city_name)city_name,
       grouping(city_id)g_city_id,
       grouping(city_name)g_city_name,
       grouping_id(city_id,city_name)gp_id,
       sum(sales_num),sum(sales_price)
from my_test
group by rollup((city_id,city_name))
order by city_id;

CITY_ID              CITY_NAME                       G_CITY_ID G_CITY_NAME      GP_ID SUM(SALES_NUM) SUM(SALES_PRICE)
-------------------- ------------------------------ ---------- ----------- ---------- -------------- ----------------
0851                 贵阳                                    0           0          0           2000             1680
0852                 遵义                                    0           0          0           1200             1280
0853                 安顺                                    0           0          0            600             1880
0854                 黔南                                    0           0          0            300              680
0855                 黔东南                                  0           0          0             60              780
0856                 铜仁                                    0           0          0            120             1000
0857                 毕节                                    0           0          0            430             1230
0858                 六盘水                                  0           0          0            670             1150
0859                 黔西南                                  0           0          0            890             1670
总计                 总计                                    1           1          3           6270            11350


--3.3 cube两列的情况
select decode(grouping_id(city_id,city_name),2,'小计',3,'总计',city_id)city_id,
       decode(grouping_id(city_id,city_name),2,'小计',3,'总计',city_name)city_name,
       grouping(city_id)g_city_id,
       grouping(city_name)g_city_name,grouping_id(city_id,city_name)gp_id,
       sum(sales_num),sum(sales_price)
from my_test
group by cube(city_id,city_name)
order by city_id;

CITY_ID              CITY_NAME                       G_CITY_ID G_CITY_NAME      GP_ID SUM(SALES_NUM) SUM(SALES_PRICE)
-------------------- ------------------------------ ---------- ----------- ---------- -------------- ----------------
0851                 贵阳                                    0           0          0           2000             1680
0851                                                         0           1          1           2000             1680
0852                 遵义                                    0           0          0           1200             1280
0852                                                         0           1          1           1200             1280
0853                                                         0           1          1            600             1880
0853                 安顺                                    0           0          0            600             1880
0854                 黔南                                    0           0          0            300              680
0854                                                         0           1          1            300              680
0855                 黔东南                                  0           0          0             60              780
0855                                                         0           1          1             60              780
0856                 铜仁                                    0           0          0            120             1000
0856                                                         0           1          1            120             1000
0857                                                         0           1          1            430             1230
0857                 毕节                                    0           0          0            430             1230
0858                                                         0           1          1            670             1150
0858                 六盘水                                  0           0          0            670             1150
0859                                                         0           1          1            890             1670
0859                 黔西南                                  0           0          0            890             1670
小计                 小计                                    1           0          2            670             1150
小计                 小计                                    1           0          2            300              680
小计                 小计                                    1           0          2            120             1000
小计                 小计                                    1           0          2           1200             1280
小计                 小计                                    1           0          2            890             1670
小计                 小计                                    1           0          2            430             1230
小计                 小计                                    1           0          2            600             1880
小计                 小计                                    1           0          2           2000             1680
小计                 小计                                    1           0          2             60              780
总计                 总计                                    1           1          3           6270            11350


--3.4 cube两列的情况(cube的另类用法)
select decode(grouping_id(city_id,city_name),2,'小计',3,'总计',city_id)city_id,
       decode(grouping_id(city_id,city_name),2,'小计',3,'总计',city_name)city_name,
       grouping(city_id)g_city_id,
       grouping(city_name)g_city_name,grouping_id(city_id,city_name)gp_id,
       sum(sales_num),sum(sales_price)
from my_test
group by cube((city_id,city_name))
order by city_id;

CITY_ID              CITY_NAME                       G_CITY_ID G_CITY_NAME      GP_ID SUM(SALES_NUM) SUM(SALES_PRICE)
-------------------- ------------------------------ ---------- ----------- ---------- -------------- ----------------
0851                 贵阳                                    0           0          0           2000             1680
0852                 遵义                                    0           0          0           1200             1280
0853                 安顺                                    0           0          0            600             1880
0854                 黔南                                    0           0          0            300              680
0855                 黔东南                                  0           0          0             60              780
0856                 铜仁                                    0           0          0            120             1000
0857                 毕节                                    0           0          0            430             1230
0858                 六盘水                                  0           0          0            670             1150
0859                 黔西南                                  0           0          0            890             1670
总计                 总计                                    1           1          3           6270            11350


--4、rollup单列分组合计项和cube单列分组合计项
--4.1 rollup另类合计统计
select decode(grouping_id(city_id),1,'总计',city_id)city_id,
       decode(grouping_id(city_name),1,'总计',city_name)city_name,
       sum(sales_num)sales_num,sum(sales_price)sales_price
from my_test
group by rollup((city_id,city_name));
CITY_ID              CITY_NAME                       SALES_NUM SALES_PRICE
-------------------- ------------------------------ ---------- -----------
0851                 贵阳                                 2000        1680
0852                 遵义                                 1200        1280
0853                 安顺                                  600        1880
0854                 黔南                                  300         680
0855                 黔东南                                 60         780
0856                 铜仁                                  120        1000
0857                 毕节                                  430        1230
0858                 六盘水                                670        1150
0859                 黔西南                                890        1670
总计                 总计                                 6270       11350

--4.2 rollup单列分组后的合计项
select a.city_id,b.city_name,a.sales_num,a.sales_price
from
(
select decode(grouping_id(city_id),1,'总计',city_id)city_id,
       sum(sales_num)sales_num,sum(sales_price)sales_price
from my_test
group by rollup(city_id)
)a
left join my_test b
on a.city_id=b.city_id
order by a.city_id;

CITY_ID              CITY_NAME                       SALES_NUM SALES_PRICE
-------------------- ------------------------------ ---------- -----------
0851                 贵阳                                 2000        1680
0852                 遵义                                 1200        1280
0853                 安顺                                  600        1880
0854                 黔南                                  300         680
0855                 黔东南                                 60         780
0856                 铜仁                                  120        1000
0857                 毕节                                  430        1230
0858                 六盘水                                670        1150
0859                 黔西南                                890        1670
总计                                                      6270       11350


--方式4.2 的另一种处理方法(union all查询方式)
select city_id,city_name,sum(sales_num)sales_num,sum(sales_price)sales_price
from my_test
group by city_id,city_name
union all
select '总计' as city_id,null as city_name,sum(sales_num)sales_num,sum(sales_price)sales_price
from my_test
order by city_id;

CITY_ID              CITY_NAME                       SALES_NUM SALES_PRICE
-------------------- ------------------------------ ---------- -----------
0851                 贵阳                                 2000        1680
0852                 遵义                                 1200        1280
0853                 安顺                                  600        1880
0854                 黔南                                  300         680
0855                 黔东南                                 60         780
0856                 铜仁                                  120        1000
0857                 毕节                                  430        1230
0858                 六盘水                                670        1150
0859                 黔西南                                890        1670
总计                                                      6270       11350


--4.3 cube单列分组后的合计项
select a.city_id,b.city_name,a.sales_num,a.sales_price
from
(
select decode(grouping_id(city_id),1,'总计',city_id)city_id,
       sum(sales_num)sales_num,sum(sales_price)sales_price
from my_test
group by cube(city_id)
)a
left join my_test b
on a.city_id=b.city_id
order by a.city_id;

CITY_ID              CITY_NAME                       SALES_NUM SALES_PRICE
-------------------- ------------------------------ ---------- -----------
0851                 贵阳                                 2000        1680
0852                 遵义                                 1200        1280
0853                 安顺                                  600        1880
0854                 黔南                                  300         680
0855                 黔东南                                 60         780
0856                 铜仁                                  120        1000
0857                 毕节                                  430        1230
0858                 六盘水                                670        1150
0859                 黔西南                                890        1670
总计                                                      6270       11350

--4.4 cube的另类合计统计
select decode(grouping_id(city_id),1,'总计',city_id)city_id,
       decode(grouping_id(city_name),1,'总计',city_name)city_id,
       sum(sales_num)sales_num,sum(sales_price)sales_price
from my_test
group by cube((city_id,city_name));

CITY_ID              CITY_ID                         SALES_NUM SALES_PRICE
-------------------- ------------------------------ ---------- -----------
0851                 贵阳                                 2000        1680
0852                 遵义                                 1200        1280
0853                 安顺                                  600        1880
0854                 黔南                                  300         680
0855                 黔东南                                 60         780
0856                 铜仁                                  120        1000
0857                 毕节                                  430        1230
0858                 六盘水                                670        1150
0859                 黔西南                                890        1670
总计                 总计                                 6270       11350



--5、grouping sets 函数的使用
--5.1 prouping sets 两列的情况
select city_id,city_name,grouping(city_id),grouping(city_name),sum(sales_num)
from my_test
group by grouping sets(city_id,city_name)
order by city_id;

CITY_ID              CITY_NAME                      GROUPING(CITY_ID) GROUPING(CITY_NAME) SUM(SALES_NUM)
-------------------- ------------------------------ ----------------- ------------------- --------------
0851                                                                0                   1           2000
0852                                                                0                   1           1200
0853                                                                0                   1            600
0854                                                                0                   1            300
0855                                                                0                   1             60
0856                                                                0                   1            120
0857                                                                0                   1            430
0858                                                                0                   1            670
0859                                                                0                   1            890
                     黔西南                                         1                   0            890
                     黔东南                                         1                   0             60
                     黔南                                           1                   0            300
                     铜仁                                           1                   0            120
                     安顺                                           1                   0            600
                     毕节                                           1                   0            430
                     六盘水                                         1                   0            670
                     贵阳                                           1                   0           2000
                     遵义                                           1                   0           1200
                     
--5.2 grouping sets 另类统计(把(city_id,city_name)当着一个列来处理)
select city_id,city_name,grouping(city_id),grouping(city_name),sum(sales_num)
from my_test
group by grouping sets((city_id,city_name))
order by city_id;

CITY_ID              CITY_NAME                      GROUPING(CITY_ID) GROUPING(CITY_NAME) SUM(SALES_NUM)
-------------------- ------------------------------ ----------------- ------------------- --------------
0851                 贵阳                                           0                   0           2000
0852                 遵义                                           0                   0           1200
0853                 安顺                                           0                   0            600
0854                 黔南                                           0                   0            300
0855                 黔东南                                         0                   0             60
0856                 铜仁                                           0                   0            120
0857                 毕节                                           0                   0            430
0858                 六盘水                                         0                   0            670
0859                 黔西南                                         0                   0            890

结果说明：GROUPING SETS与GROUPING的作用是不同的。
Oracle服务器计算GROUPING SETS子句中所有的组并将结果通过UNION ALL组合成一个结果集；
GROUPING SETS的效果:
1.只需要访问一次基表；
2.不需要写很复杂的UNION语句。


三、CUBE与ROLLUP之间的细微差别
rollup((a,b)) 统计列包含：(a,b)
rollup(a,b)   统计列包含：(a,b)、(a)、全表
rollup(a,b,c) 统计列包含：(a,b,c)、(a,b)、(a)、全表
以此类推……
cube((a,b))   统计列包含：(a,b)
cube(a,b)     统计列包含：(a,b)、(a)、(b)、全表
cube(a,b,c)   统计列包含：(a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、(c)、全表
以此类推……


四、小结
CUBE在ROLLUP的基础上进一步从各种维度上给出细化的统计汇总结果。
ROLLUP在数据统计和报表生成过程中带来极大的便利，而且效率比起来Group By + Union组合方法效率高得多。这也体现了Oracle在SQL统计分析上人性化、自动化、高效率的特点。
ROLLUP与GROUP BY的关系可以参考Oracle官方文档中的例子，链接如下：《ROLLUP Extension to GROUP BY》http://docs.oracle.com/cd/E11882_01/server.112/e25554/aggreg.htm#DWHSG8608


本文参考资料：http://www.askoracle.org/oracle/SQL/865.html
http://docs.oracle.com/cd/E11882_01/server.112/e25554/aggreg.htm#DWHSG8608