数据仓库开发报表常用函数—ROLLUP和GROUPING

 

在报表开发中常用到小记，合计这样统计功能，经常看到网上谈论GROUPING，GROUPING SET，ROLLUP，CUBE等函数的使用，当时也没有足够的数据试验，目前在项目里也时常用到这样的报表函数，有些心得就记录下来与大家分享下

其实各报表函数统计方法方式十分相似，掌握最基本的其他自然也就了然于胸。这里介绍下本人项目中实际使用过的报表函数ROLLUP和GROUPING和配合使用

项目中需要按支社，办事处统计出当月所有产品的销量情况。比如这里的报表格式是
 

支社CODE	支社	办事处CODE	办事处	TTL
						Qty	%

且需要三个类型的统计结果，1报告每月里支社和办事处的销量总和；2每月里支社内所有办事处的销量总和(小计)；3每月里所有支社和办事处的销量总和(总计)
因此一般的统计方法必然是

SELECT

,T1.BRNC_ID
,MAX(T1.BRNC_NM) BRNC_NM
,GROUPING(T1.BRNC_ID) GR_BRNC_ID
,T1.OFFC_ID
,MAX(T1.OFFC_NM) OFFC_NM
,GROUPING(T1.OFFC_ID) GR_OFFC_ID
,SUM(T1.PSI_VAL) TTL_VAL
,MAX(T1.PSI_VAL_OV_TTL)
TTL_VAL_OV
……
FROM T_MART T1
GROUP BY T1.BRNC_ID,T1.OFFC_ID
UNION ALL
SELECT
,T1.BRNC_ID
,MAX(T1.BRNC_NM) BRNC_NM
,GROUPING(T1.BRNC_ID) GR_BRNC_ID
,T1.OFFC_ID
,MAX(T1.OFFC_NM) OFFC_NM
,GROUPING(T1.OFFC_ID) GR_OFFC_ID
,SUM(T1.PSI_VAL) TTL_VAL
,MAX(T1.PSI_VAL_OV_TTL)
TTL_VAL_OV
FROM T_MART T1
GROUP BY T1.BRNC_ID
UNION ALL

SELECT

,T1.BRNC_ID
,MAX(T1.BRNC_NM) BRNC_NM
,GROUPING(T1.BRNC_ID) GR_BRNC_ID
,T1.OFFC_ID
,MAX(T1.OFFC_NM) OFFC_NM
,GROUPING(T1.OFFC_ID) GR_OFFC_ID
,SUM(T1.PSI_VAL) TTL_VAL
,MAX(T1.PSI_VAL_OV_TTL) TTL_VAL_OV
FROM T_MART T1
因此对这种重复的冗长的SQL就可以使用报表函数如ROLLUP，我们把上面sql改成ROLLUP语句
SELECT
T1.BRNC_ID
,MAX(T1.BRNC_NM) BRNC_NM
,GROUPING(T1.BRNC_ID)
GR_BRNC_ID
,T1.OFFC_ID
,MAX(T1.OFFC_NM) OFFC_NM
,GROUPING(T1.OFFC_ID) GR_OFFC_ID
,SUM(T1.PSI_VAL) TTL_VAL
,MAX(T1.PSI_VAL_OV_TTL) TTL_VAL_OV
……
FROM
T_MART T1
GROUP BY ROLLUP(T1.BRNC_ID,T1.OFFC_ID)
看看语句是不是少了很多，而SELECT 中有个grouping函数，这个就是用来确定统计结果中哪些统计的结果是按月统计的，哪些是小计，哪些是总和了，可以将grouping的字段输出查看对应统计结果 
  

GR_BRNC_ID	GR_OFFC_ID	BRNC_ID	OFFC_ID	BRNC_NM	OFFC_NM	TTL_VAL
1	1	SCIC	TTL	SCIC	TTL	1641201
0	1	S610	TTL	SEBJ	TTL	429502
0	0	S610	CB0002	SEBJ	北京	153110

可以看出三种统计类型的结果和对应grouping字段的标识标识的很清楚，grouping函数实际是对后面的字段用二进制组合的方式表示各种统计的类型。1就表示当前对此列汇总，就是说该列不在group by的后面；0表示相反不对本列汇总。这样如果ROLLUP后有两列，通过grouping函数标识这两列对应的汇总状态，通过0和1的组合来表示出对应统计结果的类型。这样我们可以控制对统计结果类型的过滤。

这样，根据GROUPING和ROLLUP的组合我们就能方便的完成很多复杂的统计功能。 

GR_BRNC_ID

GR_OFFC_ID

BRNC_ID

OFFC_ID

BRNC_NM

OFFC_NM

TTL_VAL

1

----------------------------------------------排版问题如下未能删除，可忽略-----------

1

SCIC

 
   

   

   

1641201

0

1

S6
 

   

   

429502

0

0

S610

 
   

 

 

 
 

 
 
 
 
 
 
 

 

 
 
 


 
 
 


 

 

 
 
 
  

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/750077/viewspace-1613115/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/750077/viewspace-1613115/