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

 一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) | 删除 编辑 置顶

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