資料倉儲開發報表常用函式—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/，如需轉載，請註明出處，否則將追究法律責任。