【高级分组】使用ROLLUP部分分组和组合分组实现复杂统计需求-dingjun123-ChinaUnix博客

一ITPUB哥们问一统计需求 http://www.itpub.net/thread-1783767-1-1.html，如下：

create table test_h
(
id varchar(50), --和子表ID是匹配的
unit_code varchar(50), --单位编码
input_date varchar(50), --制单日期
account_time varchar(50)--会计日期
)

create table test_b
(
vouch_id varchar(50),--和主表ID是匹配的
digest varchar(50), --摘要
amount number,--金额
direct varchar(50),--借贷方向
item_code varchar(50)--项目编码
)
select * from test_h;
select * from test_b
--主表数据
insert into test_h (ID, UNIT_CODE, INPUT_DATE, ACCOUNT_TIME)values ('id01', '001', '2013-01-02', '2013-01-30');
insert into test_h (ID, UNIT_CODE, INPUT_DATE, ACCOUNT_TIME)values ('id02', '001', '2013-02-02', '2013-01-30');
insert into test_h (ID, UNIT_CODE, INPUT_DATE, ACCOUNT_TIME)values ('id03', '002', '2013-02-02', '2013-01-30');
insert into test_b (VOUCH_ID, DIGEST, AMOUNT, DIRECT, ITEM_CODE)values ('id01', '摘要1', 10, 'J', '项目a');
insert into test_b (VOUCH_ID, DIGEST, AMOUNT, DIRECT, ITEM_CODE)values ('id01', '摘要x', 10, 'D', '项目b');
insert into test_b (VOUCH_ID, DIGEST, AMOUNT, DIRECT, ITEM_CODE)values ('id02', '摘要d', 50, 'J', '项目c');
insert into test_b (VOUCH_ID, DIGEST, AMOUNT, DIRECT, ITEM_CODE)values ('id02', '摘要f', 50, 'D', '项目d');
insert into test_b (VOUCH_ID, DIGEST, AMOUNT, DIRECT, ITEM_CODE)values ('id03', '摘要d', 50, 'J', '项目c');
insert into test_b (VOUCH_ID, DIGEST, AMOUNT, DIRECT, ITEM_CODE)values ('id03', '摘要f', 50, 'D', '项目d');
COMMIT；

要求对表进行分组，对关联的结果集，按UNIT_CODE分组，每组都统计小计，合计。需要得到的结果如下：
UNIT_CODE                               DIGEST                                     ITEM_CODE                                     JPRICE    DPRICE
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------- ------
001                                           摘要1                                        项目a                                                 10
001                                           摘要x                                           项目b                                                             10
小计                                                                                                                                                          10       10
001                                           摘要d                                        项目c                                                 50
001                                           摘要f                                           项目d                                                             50
小计                                                                                                                                                          50       50
合计                                                                                                                                                          60       60
002                                           摘要d                                        项目c                                                 50
002                                           摘要f                                           项目d                                                             50
小计                                                                                                                                                          50       50
合计                                                                                                                                                          50       50
总合计                                                                                                                                                    110       110

如果对ROLLUP、CUBE、GROUPING SETS等分组组合知识不熟悉，那么做复杂报表是困难的。这种需求，是很困难的，可能用UNION ALL写一坨长长的SQL语句，其实遇到这种需求，立马应该想到高级分组知识，以及高级分组中的部分分组、组合分组、连接分组等，并且配合GRPUPING,GRPUPING_ID,GROUP_ID等，获得复杂的结果。有了这种知识基础，解决起来就简单了，很明显上面的需求，需要写 ROLLUP (a.unit_code,a.id,(b.digest,b.item_code))条件，也就是先进行全量分组，之后进行GROUP BY (a.unit_code,aid)进行每个a.unit_code,a.id的小计，然后进行针对a.unit_code的合计，最后总合计。这里的(b.digest,b.item_code)就是组合分组的用法。

那么SQL语句如下：

--注意grouping_id的使用，这是格式化报表
SELECT decode(grouping_id(a.unit_code,a.id,b.digest,b.item_code),3,'小计',7,'合计',15,'总合计',0,a.unit_code) unit_code,
b.digest,b.item_code,sum(decode(b.direct,'J',b.amount)) jprice,sum(decode(b.direct,'D',b.amount)) dprice
FROM test_h a,test_b b
WHERE a.id=b.vouch_id
GROUP BY ROLLUP (a.unit_code,a.id,(b.digest,b.item_code));

如果我们不想要总合计，那么也很简单，采用部分分组将unit_code拿到GROUP BY 之后就可以了，修改grouping_id格式化函数。

SQL> SELECT decode(grouping_id(a.id,b.digest,b.item_code),3,'小计',7,'合计',0,a.unit_code) unit_code,
2 b.digest,b.item_code,sum(decode(b.direct,'J',b.amount)) jprice,sum(decode(b.direct,'D',b.amount)) dprice
3 FROM test_h a,test_b b
4 WHERE a.id=b.vouch_id
5 GROUP BY a.unit_code,ROLLUP (a.id,(b.digest,b.item_code));

UNIT_CODE DIGEST ITEM_CODE JPRICE DPRICE
-------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------- ----------
001 摘要1 项目a 10
001 摘要x 项目b 10
小计 10 10
001 摘要d 项目c 50
001 摘要f 项目d 50
小计 50 50
合计 60 60
002 摘要d 项目c 50
002 摘要f 项目d 50
小计 50 50
合计 50 50

ORACLE高级分组在按维度进行统计中很常用，而且组合方式多样，可以实现很复杂的多维度报表。

阅读(3012) | 评论(2) | 转发(1) | 删除编辑置顶

上一篇：【统计信息】识别直方图引起执行计划不正确的问题

下一篇：【semi join】IN/EXISTS子查询的区别及其优化方式

给主人留下些什么吧！~~

lianglinaya2013-08-21 16:25:42

Group by C , rollup(A ,B)

产生的分组种数：
3
种；

第一种：
group by C,A,B

第二种：
group by C,A

第三种：
group by C,NULL
等价于
group by C

Group by rollup(A ,B,C)

产生的分组种数：
4
种；

第一种：
group by A,B,C

第二种：
group by A,B

第三种：
group by A

第四种：
group by N