数据仓库开发报表常用函数—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




 
   

 




 




 
 

 
 
 
 
 
 
 

 


 
 
 

 
 
 

 

 


 
 
 
  

 



目录
相关文章
|
9月前
|
SQL 存储 大数据
数据仓库(10)数仓拉链表开发实例
拉链表是数据仓库中特别重要的一种方式,它可以保留数据历史变化的过程,这里分享一下拉链表具体的开发过程。 维护历史状态,以及最新状态数据的一种表,拉链表根据拉链粒度的不同,实际上相当于快照,只不过做了优化,去除了一部分不变的记录,通过拉链表可以很方便的还原出拉链时点的客户记录。
403 13
数据仓库(10)数仓拉链表开发实例
|
7月前
|
运维 监控 Cloud Native
云原生数据仓库使用问题之是否有处理URL的函数
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
|
7月前
|
Cloud Native 关系型数据库 MySQL
云原生数据仓库使用问题之集群支持的数值函数有哪些
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
|
8月前
|
Cloud Native 关系型数据库 OLAP
云原生数据仓库AnalyticDB产品使用合集之 orcale的行转列函数wm_concat 在pg版本有对应的相关函数吗
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
|
9月前
|
数据采集 SQL 数据挖掘
数据仓库的设计开发应用(三)
数据仓库的设计开发应用(三)
73 4
|
9月前
|
存储 数据采集 SQL
数据仓库的设计开发应用(二)
数据仓库的设计开发应用(二)
63 3
|
9月前
|
数据采集 前端开发 数据管理
数据仓库的设计开发应用(一)
数据仓库的设计开发应用(一)
95 3
|
SQL Oracle 关系型数据库
【SQL开发实战技巧】系列(二十一):数据仓库中时间类型操作(进阶)识别重叠的日期范围,按指定10分钟时间间隔汇总数据
如何识别重叠的日期范围、日期出现次数、确定当前记录和下一条记录之间相差的天数【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作,毕竟SQL开发在数据分析场景非常重要且基础,面试也会经常问SQL开发和调优经验,相信当我写完这一系列文章,也能再有所收获,未来面对SQL面试也能游刃有余~。例如:以上就是今天要讲的内容,本文仅仅简单介绍了pandas的使用,而pandas提供了大量能使我们快速便捷地处理数据的函数和方法。
【SQL开发实战技巧】系列(二十一):数据仓库中时间类型操作(进阶)识别重叠的日期范围,按指定10分钟时间间隔汇总数据
|
SQL BI Serverless
【SQL开发实战技巧】系列(二十):数据仓库中时间类型操作(进阶)获取季度开始结束时间以及如何统计非连续性时间的数据
本篇文章讲解的主要内容是:***汇总报表时常要求按季度分类汇总这就需要通过给定年份获取对应的季度开始结束时间、业务数据不连续的情况下如何统计所有年份数据、如何统计相同月份与周内日期聘用的员工、如何返回2月或12月聘用的所有员工以及周二聘用的所有员工***
【SQL开发实战技巧】系列(二十):数据仓库中时间类型操作(进阶)获取季度开始结束时间以及如何统计非连续性时间的数据
【SQL开发实战技巧】系列(十九):数据仓库中时间类型操作(进阶)如何一个SQL打印当月或一年的日历?如何确定某月内第一个和最后—个周内某天的日期?
如何一个SQL打印出当月日历或当年日历???如何统计一年内属于周内某一天的所有日期???如何确定某月内第一个和最后—个周内某天的日期???【SQL开发实战技巧】这一系列博主当作复习旧知识来进行写作,毕竟SQL开发在数据分析场景非常重要且基础,面试也会经常问SQL开发和调优经验,相信当我写完这一系列文章,也能再有所收获,未来面对SQL面试也能游刃有余~。本例要求返回当月内第一个星期一与最后一个星期一,我们分别找上月末及当月末之前七天的下一周周一即可。
【SQL开发实战技巧】系列(十九):数据仓库中时间类型操作(进阶)如何一个SQL打印当月或一年的日历?如何确定某月内第一个和最后—个周内某天的日期?