報表開發之擴充GROUP BY,報表擴充groupby

來源:互聯網
上載者:User

報表開發之擴充GROUP BY,報表擴充groupby

在實際運用中,比如在資料倉儲中,經常需要對資料進行多維分析,不僅需要標準分組的結果(相當於

GROUP BY),還需要不同維度小計(簡單 GROUP BY 中取部分列分組)和合計(不分組),從而

提供多角度的資料分析,對於這種複雜分組需求,簡單 GROUP BY 很難達到這種目的,當然,我們可以

使用 UNION 或 UNION ALL 將不同維度分組結果聯合起來,但效能往往不好,此時,我們可以使用擴

展 GROUP BY 來滿足實際運用中出現的大部分多維分組問題。


1. 擴充 GROUP BY 概述擴充 GROUP BY 進行多維資料統計的工作,主要表現在:a. ROLLUP、CUBE、GROUPING SETS 擴充 GROUP BY 子句提供了豐富的多維分組統計功能;b. 3個擴充分組函數:GROUPING、GROUPING_ID、GROUP_ID 提供擴充 GROUP BY 的協助工具功能,例如,提供區別結果行屬於哪個分組層級、區分 NULL 值、建立有意義的報表、對匯總結果排序、過濾結果行等功能c. 對擴充 GROUP BY 允許按重複列分組、組合列分組、部分分組、串連分組等,另外 GROUPING SETS 可以接受 CUBE、ROLLUP 操作作為參數,這些功能使擴充 GROUP BY 更加強大。

2. ROLLUP2.1 UNION ALL 實現 ROLLUP 功能假設有這樣的需求:a. 統計每個部門每個職位的薪水和b. 統計每個部門所有職位的薪水小計c. 統計所有部門所有職位的薪水合計d. 需要顯示部門名、職位名和累加後的薪水值
-- 需求一實現select d.dname,e.job,sum(e.sal) sum_sal from dept d,emp ewhere d.deptno=e.deptno group by d.dname,e.jobunion all-- 需求二實現select d.dname,null,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by d.dnameunion all-- 需求三實現select null,null,sum(e.sal) sum_sal from dept d,emp ewhere d.deptno=e.deptno
上面的代碼通過執行計畫(set autotrace on)可以發現,需要多次訪問EMP、DEPT表的索引,如果實際運用中表的結構很複雜,將嚴重影響效能。
2.2 ROLLUP 分組從 Oracle 8i 開始,Oracle 使用 ROLLUP 對 GROUP BY 進行擴充,它允許計算標準分組及相應維度 的小計、合計。ROLLUP 的文法結構如下:SELECT ... GROUP BY ROLLUP(grouping_column_reference_liist)ROLLUP 後面指定的列以逗號分隔,ROLLUP 的計算和其後面指定列的順序有關,因為 ROLLUP 分組過程具有方向性,先計算標準分組,然後列從右向左遞減計算更高一級的小計,一直到列全部被選完,最後計算合計。如果 ROLLUP 中指定 n 列,則整個計算過程中的分組方式有n+1種。
-- 使用ROLLUP 實現 2.1 節的需求select d.dname,e.job,sum(e.sal) from dept d,emp e where d.deptno=e.deptno group by rollup(d.dname,e.job);
ROLLUP 分組具有方向性,從上面的結果可以看出,ROLLUP(d.dname,e.job) 分組的過程是:a. 標準分組:GROUP BY(d.dname,e.job),對每個部門每個職位進行分組;b. 從右至左遞減:GROUP BY(d.dname,null),其實這個null沒有必要使用,這裡只是方便分析,     這個過程是對上個層級分組的小計,也就是對每個 dname 值,計算橫跨所有 job 的小計;c. 最後合計:相當於 GROUP BY(null,null)。再例如 ROLLUP(a,b,c)

範例:實現以下需求a. 計算每個入職時間(年)、部門、職位的標準分組的薪水和b. 計算每個入職時間(年)、部門的所有職位的薪水小計c. 計算每個入職時間(年)的所有部門所有職位的薪水小計d. 最後合計薪水,顯示入職時間(年)、部門名、職位名
with t as (
  select to_char(e.hiredate,'yyyy') hireyear,d.dname,e.job,sum(e.sal) sum_sal from emp e,dept d

  where e.deptno=d.deptno group by rollup(to_char(e.hiredate,'yyyy'),d.dname,e.job))

  select rownum,t.* from t;

接下來分析上述代碼的結果:

因為 ROLLUP 分組過程具有方向性,所以通過改變 ROLLUP 中列的順序就可以達到改變報表結果和含義的目的,如現在需要查詢的是 標準分組、計算每個 job 的所有部門的小計、最後合計,則代碼為:select e.job,d.dname,sum(e.sal) sum_sal from emp e,dept dwhere e.deptno=d.deptno group by rollup(e.job,d.dname);
2.3 部分 ROLLUP 分組通過將部分列從 ROLLUP 中移出來,放在 GROUP BY 中,這樣合計肯定沒有了,某些小計也沒有了。需求:不需要每個入職時間(年)的所有部門所有職位的薪水小計,合計也不需要select to_char(hiredate,'yyyy'),d.dname,e.job,sum(e.sal) sum_sal from emp e,dept d where e.deptno=d.deptno group by to_char(hiredate,'yyyy'),d.dname,rollup(e.job);<=>select to_char(hiredate,'yyyy'),d.dname,e.job,sum(e.sal) sum_sal from emp e,dept dwhere e.deptno=d.deptno group by to_char(hiredate,'yyyy'),d.dname,e.jobunion allselect null,null,null,sum(e.sal) sum_sal from emp e,dept dwhere e.deptno=d.deptno group by to_char(hiredate,'yyyy'),d.dname;註:將 hiredate 和 dname 從 ROLLUP 中移出來,就可以將每個入職時間(年)的所有部門所有職位的薪水小計及合計剔除,最終只查詢標準分組和每個入職時間(年)、部門的所有職位的小計。           2.4 ROLLUP 總結先進行標準分組,在標準分組的基礎上通過將列從右向左移動,然後進行更高一級的小計,最後合計。                                                                                                                                     
3. CUBECUBE 是對不同維度所有可能分組進行統計,從而產生交叉報表;這種需求比 ROLLUP更加精細,包含了 ROLLUP 的統計結果,而且還有其他組合分組結果(小計)。3.1 CUBE 分組CUBE文法結構:SELECT ... GROUP BY CUBE(grouping_column_reference_list)如果 CUBE 中指定 n 列,則整個計算過程中的分組方式有 power(2,n) 種。

將 2.2 節使用 ROLLUP(dname,job) 替換為 CUBEselect d.dname,e.job,sum(e.sal) from dept d,emp ewhere d.deptno=e.deptno group by cube(d.dname,e.job);分析 CUBE(dname,job) 對應分組層級:

3.2 部分 CUBE 分組和 ROLLUP 一樣,也有部分 CUBE 操作,可以去掉合計及某些不需要的小計,比如上面的 GROUP BYCUBE(d.dname,e.job) 改為 GROUP BY d.dname CUBE(e.job) 則剔除了合計及GROUP BY job。select d.dname,e.job,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by d.dname,cube(e.job);<=>select d.dname,e.job,sum(e.sal) sum_sal from dept d,emp ewhere d.deptno=e.deptno group by d.dname,e.jobunion allselect null,null,sum(e.sal) sum_sal from dept d,emp ewhere d.deptno=e.deptno group by d.dname;
3.3 CUBE總結先進行合計,然後小計,最後再按標準分組

4. GROUPING SETS 實現小計前面所說的兩種多維資料統計的方法,即 ROLLUP 和 CUBE,它們的輸出結果是由對應分組的行伴隨著小計行產生的,它們會產生標準分組、各種小計及總計,但是有時候我們只關心某個單列分組,從而得到其它維度小計的資訊,這樣就需要使用 GROUPING SETS擴充分組,它是Oracle9i提供的。比如 GROUP BY GROUPING SETS(a,b,c) 相當於 GROUP BY a、GROUP BY b、GROUP BY c 這三個單列分組,從而得到其他維度小計資訊。n列的 GROUPING SETS 的分組總類有 n 個。4.1 GROUPING SETS 分組文法結構:SELECT ... GROUP BY GROUPING SETS(grouping_column_reference_list)將2.2節中的 ROLLUP 改為 GROUPING SETSselect to_char(e.hiredate,'yyyy') hireyear,d.dname,e.job,sum(e.sal) sum_sal from emp e,dept dwhere e.deptno=d.deptno group by grouping sets(to_char(e.hiredate,'yyyy'),d.dname,e.job);註:GROUPING SETS 的結果是分別按單列分組後 UNION ALL的結果;        GROUPING SETS 的結果和列的順序沒有關係,而且結果的順序也是無序的。
4.2 部分 GROUPING SETS 分組select d.dname,to_char(e.hiredate,'yyyy') hireyear,e.job,sum(e.sal) sum_sal from dept d,emp ewhere d.deptno=e.deptno group by d.dname,grouping sets(to_char(e.hiredate,'yyyy'),e.job);<=>select d.dname,to_char(e.hiredate,'yyyy') hiredate,null job,sum(e.sal) sum_sal from dept d,emp e
where d.deptno=e.deptno group by d.dname,to_char(e.hiredate,'yyyy')union allselect d.dname,null,e.job,sum(e.sal) sum_sal from dept d,emp e
where d.deptno=e.deptno group by d.dname,e.job;
上述語句統計的是:對於每個部門每個入職時間(年),對所有職位進行小計及                             對於每個部門每個職位,對每個入職時間(年)進行小計。
4.3 CUBE、GROUPING 作為 GROUPING SETS 的參數GROUPING SETS 操作能夠接受 ROLLUP 和 CUBE 作為它的參數,GROUPING SETS 操作只對單列進行分組,而不提供合計的功能,如果需要 GROUPING SETS 提供合計的功能,那麼可以使用 ROLLUP 或 CUBE 作為 GROUPING SETS 的參數。改寫前面的 GROUPING SETS(d.dname,e.job),提供合計功能。select d.dname,e.job,sum(e.sal) from dept d,emp ewhere d.deptno=e.deptno group by grouping sets(rollup(d.dname),rollup(e.job));<=>select d.dname,null job,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by rollup(d.dname)union allselect null dname,e.job,sum(e.sal) sum_sal from dept d,emp ewhere d.deptno=e.deptno group by rollup(e.job);註:上述語句會產生兩個合計行,因為 ROLLUP 或 CUBE 作為 GROUPING SETES 的參數,相當       於對每個 ROLLUP 或 CUBE 操作的 UNION ALL;       可使用 DISTINCT 剔除重複行;       ROLLUP 和 CUBE 不能接受 GROUPING SETS 作為參數,ROLLUP 和 CUBE 之間相互作為       參數也不可以。
4.4 GROUPING SETS 總結GROUPING SETS 的結果和列的順序沒有關係,而且結果的順序也是無序的。

5. 組合列分組、串連分組、重複列分組組合列分組、串連分組、重複列分組都是Oracle  9i 中才有的特性。組合列也就是將多個列用括弧括起來,從而將多個列當做整體對待,比如 GROUP BY ROLLUP((a,b),c) 相當於 GROUP BY ROLLUP(x,c),其中 x 相當於 (a,b) 這個組合列。組合列一般在 in 條件中比較常見,比如:-- where in 中使用組合列select empno,ename,job from emp where (empno,ename) in ((7369,'SMITH'),(7499,'ALLEN'));是普通列 ROLLUP 和組合列 ROLLUP 的對比(CUBE、GROUPING SETS類似)

的組合列分組達到了剔除某些小計的功能,且保證了最終結果又合計行。
串連分組允許在 GROUP BY 之後出現多個 ROLLUP、CUBE、GROUPING SETS 操作,這樣分組層級更多,報表更加精細。

實際上不管是同類型的串連分組還是不同類型的串連分組之間,最後的分組層級種類都是每個擴充分組層級種類的乘積,分組層級是笛卡爾積。比如同類型串連分組 ROLLUP(a,b),ROLLUP(c) 最終結果有 3*2=6 種分組層級,不同類型串連分組 ROLLUP(a,b),GROUPING SETS(c) 有3*1=3 種分組層級。
重複列分組就是 GROUP BY 中允許重複列,比如在 ROLLUP 中使用複雜的複合列分組可能會用到,比如 GROUP BY ROLLUP(a,(a,b))、GROUP BY a,ROLLUP(a,b) 都屬於重複列。
5.1 組合列分組組合列分組有過濾某些小計或計算一些額外的小計等功能。前面的部分 ROLLUP、部分CUBE 都沒有合計,使用組合列可以實現部分 ROLLUP、部分 CUBE的功能,還能有合計。需求:a. 對部門、入職時間(年)、職位進行標準分組b. 對每個部門計算橫跨入職時間(年)和職位的小計c. 最後合計select d.dname,to_char(e.hiredate,'yyyy') hireyear,e.job,sum(e.sal) sum_sal from dept d,emp ewhere d.deptno=e.deptno group by rollup(d.dname,(to_char(e.hiredate,'yyyy'),e.job));CUBE 和 ROLLUP 操作都可以用組合列分組轉為對應的 GROUPING SETS, 例如,ROLLUP(a,b,c) 轉為等價的 GROUPING SETS 是 GROUPING SETS((a,b,c),(a,b),(a),NULL);CUBE(a,b,c) 轉為等價的 GROUPING SETS 是 GROUPING SETS((a,b,c),(a,b),(a,c),(b,c),(a),(b),(c),NULL);
5.2 串連分組串連分組是Oracle 9i 才有的功能,它允許 GROUP BY後面有多個 ROLLUP、CUBE、GROUPING SETS,串連分組的分組層級是由每個 ROLLUP、CUBE、GROUPING SETS 分組組成的笛卡爾積。比如 ROLLUP(a,b),ROLLUP(c,d,e) 共有分組統計層級為 3*4=12 種。select d.dname,e.job,to_char(e.hiredate,'yyyy') hireyear,sum(sal) sum_sal from dept d,emp ewhere d.deptno=e.deptno group by rollup(d.dname,e.job),rollup(to_char(e.hiredate,'yyyy'));GROUP BY ROLLUP(d.dname,e.job),ROLLUP(to_char(e.hiredate),'yyyy') 實現了 6 種分組結果,相當於兩個 ROLLUP 的笛卡爾積,如下表:

CUBE、GROUPING SETS 都類似,利用串連分組,CUBE 可以用 ROLLUP轉換:a. 當只有一列的時候,比如 ROLLUP(a) 與 CUBE(a) 是一樣的,都有兩種統計方式;b. 當有 n 列的時候,比如 CUBE(a,b,c) 可以轉為 ROLLUP(a),ROLLUP(b),ROLLUP(c) 的串連分組表示,也就是有 n 列的 CUBE 轉為 ROLLUP 則需要拆開,轉為單列 ROLLUP的串連分組即可。select d.dname,e.job,to_char(e.hiredate,'yyyy') hireyear,sum(e.sal) sum_sal from dept d,emp  ewhere d.deptno=e.deptno group by rollup(d.dname),rollup(e.job),rollup(to_char(e.hiredate,'yyyy'));<=> group by cube(d.dname,e.job,to_char(hiredate,'yyyy'));註:串連分組一般是同類型的串連分組,不同類型的串連分組比如 GROUP BY ROLLUP...CUBE... 等是不常用的,        除非有複雜需求。
5.3 重複列分組重複列分組也是Oracle 9i 才有的,也就是 GROUP BY 後面允許重複列。select d.dname,e.job,sum(e.sal) sum_sal from dept d,emp ewhere d.deptno=e.deptno group by d.dname,rollup(d.dname,e.job);<=>select d.dname,e.job,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by d.dname,e.jobunion allselect null,null,sum(e.sal) sum_sal from dept d,emp ewhere d.deptno=e.deptno group by d.dnameunion allselect null,null,sum(e.sal) sum_sal from dept d,emp ewhere d.deptno=e.deptno group by d.dname;
5.4 組合列分組、串連分組、重複列分組總結a. 組合列主要實現剔除某些不必要的小計保留合計;b. 串連分組按每個擴充分組的分組層級的笛卡爾積形式進行操作,分組類型更多更細,    比如 ROLLUP 串連分組就實現了類似 CUBE 的功能。

6. 3個擴充分組函數:GROUPING、GROUPING_ID、GROUP_ID 主要內容有:a. 使用 GROUPING 函數製作有意義的報表,以及對結果進行過濾;b. 使用 GROUPING_ID 函數對結果進行過濾及排序;c. 使用 GROUP_ID 函數剔除重複行。
6.1 GROUPING 函數對擴充 GROUP BY 子句來說,比如 ROLLUP、CUBE 會產生標準分組、一系列小計及合計,這樣查詢結果中,有些行的列值就會存在 NULL。NULL 在擴充 GROUP BY 中有特殊的意義,結果行中的列值為 NULL,一般就意味著是此列的小計或合計,但是 NULL 也有可能是未經處理資料存在的 NULL(如 emp.mgr=NULL),所以引入了 GROUPING 函數專門處理擴充GROUP BY 分組結果中 NULL 的問題:a. 它只接受一個參數,此參數來自 ROLLUP、CUBE、GROUPING SETS 中的列;b. GROUPING 函數對於是小計或合計的列返回 1,否則返回 0。如果小計或合計列的值是 NULL,但是原始     資料可能也存在 NULL,則常使用 GROUPING 函數來區分最終結果行中的 NULL 是未經處理資料中存在的,     還是小計或合計列的值,常和 DECODE 函數配合使用。6.1.1 用於格式化報表,產生有意義的報表select d.dname,e.mgr,sum(e.sal) from dept d,emp e where d.deptno=e.deptno group by rollup(d.dname,e.mgr);   <== 對於每個dname,計算橫跨 mgr 列的小計上述結果中第 9 行和第 11 行的 mgr 列都為 NULL,無法區分哪個列是小計,此時就可以使用 GROUPING函數進行區分。select d.dname,e.mgr,sum(e.sal),grouping(mgr) from dept d,emp e where d.deptno=e.deptno group by rollup(d.dname,e.mgr);  上述結果中第 8 行的 GROUPING(mgr)=0,第 11 行為 1,所有第 8 行的 mgr 列不是小計列,11 行才是。下面使用DECODE + GROUPING 來製作有意義的報表:select decode(grouping(d.dname),1,'TOTAL_DEPT',d.dname) dname,            decode(grouping(e.mgr),1,'SUBTOTAL_DEPT',nvl(to_char(e.mgr),'BOSS')) mgr,            sum(e.sal) sum_sal from dept d,emp ewhere d.deptno=e.deptno group by rollup(d.dname,e.mgr);如果要將所有的 mgr 列小計一起放在後面顯示,位置在合計之間,這種需求如何解決?(詳見GROUPING_ID)6.1.2 過濾某些分組結果一般使用 GROUPING_ID 代替需求:對 group by rollup(d.dname,e.mgr,e.job) 的結果保留合計和標準分組select d.dname,e.mgr,e.job,sum(e.sal) sum_sal  from dept d,emp e where d.deptno=e.deptno group by rollup(d.dname,e.mgr,e.job)having grouping(d.dname)=1 or grouping(e.job)=0;
6.2 GROUPING_ID 函數GROUPING 函數用來產生有意義的報表及過濾一些分組層級;GROUPING_ID 函數主要用來過濾分組層級和排序結果(顯示排序)。不管 ROLLUP、CUBE、GROUPING SETS 的結果是否有預設順序,都是不可靠的。GROUPING_ID 函數可以接受對個參數,這些參數來自於 ROLLUP、CUBE、GROUPING SETS中的列(參數來源和 GROUPING 函數一致),按列從左至右順序計算,如果此列是分組列則為 0 ,如果是對此列的小計或合計則為 1,然後按列順序將計算結果組成二進位序列(位向量),最後將位向量轉為十進位數。如CUBE(a,b),則GROUPING_ID(a,b) 的結果如所示:

GROUPING_ID的好處就是可以對多列進行計算,從而得到此列的分組層級。從可以看出,GROUPING_ID(column_list) 中的 colum_list 和擴充分組保持一致,那麼 GROUPING_ID值得種類必須與對應擴充分組數目保持一致:比如 CUBE(a,b,c) 的GROUPING_ID(a,b,c)的值有8 種,ROLL(a,b,c)的GROUPING_ID(a,b,c) 的值有4 種。
GROUPING_ID 的取值範圍都一樣,和列的數目有關,比如有 n 列,則 GROUPING_ID的取值範圍在[ 0-2^n-1 ]6.2.1 GROUPING_ID 函數過濾某些分組結果需求:改寫6.1 節 GROUPING 過濾結果的例子,用 GROUPING_ID 實現同等功能。先分析對於 ROLLUP(d.dname,e.mgr,e.job) 使用 GROUPING_ID 函數的結果,注意的是,一般使用GROUPING_ID函數,列的順序要與 ROLLUP、CUBE、GROUPING SETS 中的順序保持一致。

從表中就可以清楚的看出,實現這個需求只要 GROUPING_ID(d.dname,e.mgr,e.job) 取 0 和 7 即可。select d.dname,e.mgr,e.job,sum(e.sal) sum_sal from dept d,emp e where d.deptno=e.deptno group by rollup(d.dname,e.mgr,e.job)having grouping_id(d.dname,e.mgr,e.job) in (0,7)

































































































金蝶K3ERP銷售報告開發:第一行顯示日期怎在第二行開始統計數量

set nocount on SELECT CASE WHEN GROUPING(v1.Fdate)=1 THEN 1 ELSE 0 END AS Fdate_Grouping,
CONVERT(varchar,v1.Fdate) AS Fdate_NOGrouping,
CASE WHEN GROUPING(v1.Fdate)=1 THEN 101
ELSE 0 END AS FSumSort ,SUM(CASE WHEN t4.FName ='(河南)散戶' THEN IsNull(u1.Fauxqty,0) ELSE 0 END) AS Fauxqty1,
SUM(CASE WHEN t4.FName ='(華東)華潤萬家有限公司' THEN IsNull(u1.Fauxqty,0) ELSE 0 END) AS Fauxqty2,
SUM(CASE WHEN t4.FName ='(華南)華潤萬家有限公司' THEN IsNull(u1.Fauxqty,0) ELSE 0 END) AS Fauxqty3,
SUM(IsNull(u1.Fauxqty,0)) AS Fauxqty281, IDENTITY(int,1,1) AS column_name into #CrossTab FROM ICStockBill v1 Inner Join ICStockBillEntry u1 on v1.FInterID=u1.FInterID
Inner Join t_Organization t4 on v1.FSupplyID=t4.FItemID
left outer join t_SubMessage t7 on v1.FSaleStyle=t7.FInterID
Inner Join t_Stock t8 on u1.FDCStockID=t8.FItemID
left outer join t_Emp t9 on v1.FFManagerID=t9.FItemID
left outer join t_Emp t10 on v1.FSManagerID=t10.FItemID
Inner Join t_User t11 on v1.FBillerID=t11.FUserID
Inner Join t_ICItem t14 on u1.FItemID=t14.FItemID
Inner Join t_MeasureUnit t17 on u1.FUnitID=t17.FItemID
left outer join t_User t24 on v1.Fcheckerid=t24.FUserID
Inner Join t_MeasureUnit t30 on t14.FUnitID=t30.FItemID
left outer join t_SubMessage t40 on v1.FMarketingStyle=t40.FInterID
left outer join v_ICTransType t70 on u1.FSourceTranType=t70.FID
left outer join ICVoucherTpl t16 on v1.FPlanVchTplID=t16.FInterID
left outer join ICVoucherTpl t13 on v1.FActualVchTplID=t13.FInterID
left out......餘下全文>>
 
Java有關開發報表的問題,協助

增加列取別名;
select 月份 ,類別, count(‘投訴類’) as ‘投訴類統計’,count(‘抱怨類’) as ‘抱怨類統計’ ,count(‘舉報類’) as ‘舉報類統計’ ,count(總) as 總計 from 表;
對了如過投訴是業務的子表,吧相應的count()換成( select count() from 字表);
 

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.