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

开发者社区> flzhang> 正文

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

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

 

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

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

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

支社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函数标识这两列对应的汇总状态,通过01的组合来表示出对应统计结果的类型。这样我们可以控制对统计结果类型的过滤。

这样,根据GROUPINGROLLUP的组合我们就能方便的完成很多复杂的统计功能。 


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




 
  

 




 




 
 

 
 
 
 
 
 
 

 


 
 
 

 
 
 

 

 


 
 
 
  

 



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

相关文章
Python编程语言学习:利用locals函数判断某个变量参数之前是否已经被定义/存在/出现
Python编程语言学习:利用locals函数判断某个变量参数之前是否已经被定义/存在/出现
25 0
PolarDB-X 1.0-SQL 手册-函数-Grouping Sets、Rollup和Cube扩展
在关系型数据库中,通常需要使用多个SELECT + UNION语句来实现按照多组维度的结果分组,PolarDB-X新增支持通过Grouping Sets、Rollup和Cube扩展来实现这一目的。此外,PolarDB-X还支持在SELECT命令或HAVING子句中使用GROUPING函数和GROUPING_ID函数,来帮助解释使用上述扩展时的结果。本文将介绍相关语法和示例。
21 0
十分钟上线-基于函数计算开发 Restful web api & asp.net core web app
.NET Core是一个开源通用的开发框架,支持跨平台, 阿里云函数计算推出了 dotnetcore2.1 runtime, 使用 C# 编写 serverless 函数, 除了很好地支持通常意义上的函数外, 还可以基于函数计算开发 asp.
4528 0
ORACLE PL/SQL编程之六:把过程与函数说透(穷追猛打,把根儿都拔起!)
原文:ORACLE PL/SQL编程之六:把过程与函数说透(穷追猛打,把根儿都拔起!) ORACLE PL/SQL编程之六: 把过程与函数说透(穷追猛打,把根儿都拔起!)   继上篇:ORACLE PL/SQL编程之八:把触发器说透 得到了大家的强力支持,感谢。
769 0
安卓开发之ScrollView
当界面不足以将所有的内容显示出来的时候便导致下面的部分内容无法显示出来 所有加上ScrollView 来讲要显示的内容放入之中便可以实现上下滚动界面内容 但是当要显示多个控件的时候会出错  原因是ScrollView只能有一个子控件  解决方法就是 用一个LinearLayout(或者别的布局)...
548 0
安卓开发ScrollView嵌套ListView只显示一行
在用列表控件做一个“更多功能”的界面的时候 1 2 7 8 12 13 17 18 23 27 28 33 37 38 39 原本用ScrollView嵌套...
937 0
+关注
flzhang
某外企dba一枚,擅长java,shell自动化运维与SQL优化等,乐于钻研技术和分享技术
81
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《Nacos架构&原理》
立即下载
《看见新力量:二》电子书
立即下载
云上自动化运维(CloudOps)白皮书
立即下载