数据仓库开发报表常用函数—ROLLUP和GROUPING-阿里云开发者社区

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

2015-04-30 1134

版权

本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。

简介： 在报表开发中常用到小记，合计这样统计功能，经常看到网上谈论GROUPING，GROUPING SET，ROLLUP，CUBE等函数的使用，当时也没有足够的数据试验，目前在项目里也时常用到这样的报表函数，有些心得就记录下来与大家分享下其实各报表函数统计方法方式十分相似，掌握最基本的其他自然也就了然于胸。

在报表开发中常用到小记，合计这样统计功能，经常看到网上谈论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
因此对这种重复的冗长的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的组合来表示出对应统计结果的类型。这样我们可以控制对统计结果类型的过滤。