5.扩展 GROUP BY
/*************************************************
主题：扩展 GROUP BY
CUBE ,ROLLUP,GROUPING SETS,GROUPING_ID,GROUP_ID
*************************************************/
1.ROLLUP ，按列的顺序从右到左递减分组小计(含合计)
-------------------------------------
从右到左递减分组
统计级别 ： SGNYEAR,AREACODE --1
SGNYEAR --2
NULL(合计) --3
-------------------------------------
SELECT SGNYEAR,AREACODE,SUM(CARGOVOLUME) S
FROM TEST3
WHERE SGNYEAR BETWEEN '2002' AND '2004'
GROUP BY ROLLUP(SGNYEAR,AREACODE) ;
SGNYEAR AREACODE S
--------- --------------------- ----------
2002 150000 37239
2002 230000 12919
2002 330000 2327
2002 360000 0 --年的某个AREACODE 小计
2002 52485 --年小计
2003 130000 1486
2003 440000 73087
2003 640000 0
2003 74573
2004 410000 0
2004 0
127058 --总计
--------- --------------------- ----------
2.ROLLUP部分分组， 分组剔除某些字段来的小计(不含合计)
SELECT SGNYEAR,AREACODE,SUM(CARGOVOLUME) S
FROM TEST3
WHERE SGNYEAR BETWEEN '2002' AND '2004'
GROUP BY SGNYEAR ,ROLLUP(AREACODE);--根据年分组，剔除AREARCODE的分组小计 与 整体的合计
SGNYEAR AREACODE S
--------- --------------------- ----------
2002 150000 37239
2002 230000 12919
2002 330000 2327
2002 360000 0
2002 52485
2003 130000 1486
2003 440000 73087
2003 640000 0
2003 74573
2004 410000 0
2004 0
--------- --------------------- ----------
3.CUBE，对不同维度的所有可能分组进行统计(各种可能性分组)
SELECT SGNYEAR,AREACODE,SUM(CARGOVOLUME) S
FROM TEST3
WHERE SGNYEAR BETWEEN '2002' AND '2004'
GROUP BY CUBE(SGNYEAR,AREACODE) ;
SGNYEAR AREACODE S
--------- --------------------- ----------
127058 --总计
130000 1486 --AREACODE的小计
150000 37239
230000 12919
330000 2327
360000 0
410000 0
440000 73087
640000 0
2002 52485 --SGNYEAR的小计
2002 150000 37239 --SGNYEAR,AREACODE的小计
2002 230000 12919
2002 330000 2327
2002 360000 0
2003 74573
2003 130000 1486
2003 440000 73087
2003 640000 0
2004 0
2004 410000 0
--------- --------------------- ----------
4.CUBE 部分分组
SELECT SGNYEAR,AREACODE,SUM(CARGOVOLUME) S
FROM TEST3
WHERE SGNYEAR BETWEEN '2002' AND '2004'
GROUP BY SGNYEAR,CUBE(AREACODE) ; --剔除对AREACODE的分组小计 与 整体的合计
SGNYEAR AREACODE S
--------- --------------------- ----------
2002 52485
2002 150000 37239
2002 230000 12919
2002 330000 2327
2002 360000 0
2003 74573
2003 130000 1486
2003 440000 73087
2003 640000 0
2004 0
2004 410000 0
--------- --------------------- ----------
5.GROUPING SETS 实现小计,没有合计
SELECT SGNYEAR,AREACODE,SUM(CARGOVOLUME) S
FROM TEST3
WHERE SGNYEAR BETWEEN '2002' AND '2004'
GROUP BY GROUPING SETS(SGNYEAR,AREACODE) ; --对SGNYEAR，AREACODE分别进行小计统计
SGNYEAR AREACODE S
--------- --------------------- ----------
2004 0
2002 52485
2003 74573
410000 0
330000 2327
640000 0
360000 0
150000 37239
230000 12919
440000 73087
130000 1486
--------- --------------------- ----------
6.GROUPING SETS 部分统计.对单列进行分组，统计其他维度的小计。
SELECT SGNYEAR,AREACODE,transtypecode,SUM(CARGOVOLUME) S
FROM TEST3
WHERE SGNYEAR BETWEEN '2002' AND '2005'
GROUP BY SGNYEAR,GROUPING SETS(AREACODE,transtypecode) ; --统计每年，每个AREACODE 的小计
--统计每年，每个TRANSTYPECODE 的小计
SGNYEAR AREACODE TRANSTYPECODE S
--------- --------------------- --------------------- ----------
2002 360000 0
2003 130000 1486
2003 640000 0
2002 330000 2327
2002 230000 12919
2004 410000 0
2003 440000 73087
2005 140000 47697
2005 620000 4085
2002 150000 37239
2002 3 15246
2002 5 0
2002 6 37239
2004 5 0
2005 3 47697
2005 2 4085
2003 7 0
2003 4 1486
2003 6 73087
--------- --------------------- --------------------- ----------
7.CUBE、ROLLUP 作为 GROUPING SETS 的参数,相当于对每个 CUBE 或 ROLLUP 的操作 UNION ALL
SELECT AREACODE,transtypecode,SUM(CARGOVOLUME) S
FROM TEST3
WHERE SGNYEAR BETWEEN '2002' AND '2005'
GROUP BY GROUPING SETS(CUBE(AREACODE),ROLLUP(transtypecode));
AREACODE TRANSTYPECODE S
--------------------- --------------------- ----------
130000 1486
140000 47697
150000 37239
230000 12919
330000 2327
360000 0
410000 0
440000 73087
620000 4085
640000 0
6 110326
2 4085
4 1486
3 62943
5 0
7 0
178840 --此处两个数据就是ROLLUP的UNION ALL
178840
--------------------- --------------------- ----------
8.组合列分组
列 得到结果的描述
--------------------- --------------------- ----------
ROLLUP(a,b,c) GROUP BY a,b,c
GROUP BY a,b
GROUP BY a
GROUP BY NULL
--------------------- --------------------- ----------
ROLLUP(a,(b,c)) GROUP BY a,b,c
GROUP BY a --此处将(b,c)看做一列,所以缩减了
GROUP BY NULL
--------------------- --------------------- ----------
--根据年进行分组，得到标准的分组统计
--得到 年的小计 ，与标准ROLLUP对比，少了年+编码的小计
SELECT SGNYEAR,AREACODE,transtypecode,SUM(CARGOVOLUME) S
FROM TEST3
WHERE SGNYEAR BETWEEN '2002' AND '2006'
GROUP BY ROLLUP(SGNYEAR,(AREACODE,transtypecode)) ;
SGNYEAR AREACODE TRANSTYPECODE S
--------- --------------------- --------------------- ----------
2002 150000 6 37239
2002 230000 3 12919 --年份、类型同
2002 330000 3 2327 --编码不同
2002 360000 5 0
2002 52485
2003 130000 4 1486
2003 440000 6 73087
2003 640000 7 0
2003 74573
2004 410000 5 0
2004 0
2005 140000 3 47697
2005 620000 2 4085
2005 51782
2006 120000 5 0
2006 140000 7 59
2006 350000 5 910
2006 500000 3 2000
2006 530000 5 171
2006 3140
--------- --------------------- --------------------- ----------
9.连接分组，允许 GROUP BY 后面有多个 ROLLUP、CUBE、GROUPING SETS,结果是分级别组成的笛卡尔积
SELECT SGNYEAR,AREACODE,transtypecode,SUM(CARGOVOLUME) S
FROM TEST3
WHERE SGNYEAR BETWEEN '2002' AND '2003'
GROUP BY ROLLUP(AREACODE,transtypecode) ,ROLLUP(SGNYEAR);
SGNYEAR AREACODE TRANSTYPECODE S
--------- --------------------- --------------------- ----------
130000 4 1486
130000 1486
150000 6 37239
150000 37239
230000 3 12919
230000 12919
330000 3 2327
330000 2327
360000 5 0
360000 0
440000 6 73087
440000 73087
640000 7 0
640000 0
127058
2002 150000 6 37239
2002 150000 37239
2002 230000 3 12919
2002 230000 12919
2002 330000 3 2327
2002 330000 2327
2002 360000 5 0
2002 360000 0
2002 52485
2003 130000 4 1486
2003 130000 1486
2003 440000 6 73087
2003 440000 73087
2003 640000 7 0
2003 640000 0
2003 74573
--------- --------------------- --------------------- ----------
10.重复列分组
SELECT SGNYEAR,AREACODE,transtypecode,SUM(CARGOVOLUME) S
FROM TEST3
WHERE SGNYEAR BETWEEN '2002' AND '2003'
GROUP BY AREACODE,ROLLUP(AREACODE,transtypecode) ,ROLLUP(SGNYEAR);
--得到的数据 AREACODE 的小计重复出现
CUBE(A,B,C)==ROLLUP(A),ROLLUP(B),ROLLUP(C)
11.GROUPING,对于小计或合计的列返回1，否则返回0.
SELECT SGNYEAR,AREACODE,SUM(CARGOVOLUME) S,GROUPING(AREACODE),
DECODE(GROUPING(AREACODE),1,'小计',NVL(AREACODE,'合计')) ITEM
FROM TEST3
WHERE SGNYEAR BETWEEN '2002' AND '2006'
GROUP BY ROLLUP(SGNYEAR,AREACODE) ;
SGNYEAR AREACODE S GROUPING(AREACODE) ITEM
--------- --------------------- ---------- ------------------ ---------------
2002 150000 37239 0 150000
2002 230000 12919 0 230000
2002 330000 2327 0 330000
2002 360000 0 0 360000
2002 52485 1 小计
2003 130000 1486 0 130000
2003 440000 73087 0 440000
2003 640000 0 0 640000
2003 74573 1 小计
2004 410000 0 0 410000
2004 0 1 小计
2005 140000 47697 0 140000
2005 620000 4085 0 620000
2005 51782 1 小计
2006 120000 0 0 120000
2006 140000 59 0 140000
2006 350000 910 0 350000
2006 500000 2000 0 500000
2006 530000 171 0 530000
2006 3140 1 小计
181980 1 小计
--------- --------------------- ---------- ------------------ ---------------
--过滤某些分组结果
SELECT SGNYEAR,AREACODE,SUM(CARGOVOLUME) S,DECODE(GROUPING(AREACODE),1,'小计',NVL(AREACODE,'合计')) ITEM
FROM TEST3
WHERE SGNYEAR BETWEEN '2002' AND '2006'
GROUP BY ROLLUP(SGNYEAR,AREACODE)
HAVING GROUPING(SGNYEAR)=1 OR GROUPING(AREACODE)=0;
12.GROUPING_ID,对多列进行计算，得到分组级别
分组 位向量（二进制） GROUPING_ID结果（根据二进制得到的十进制数）
-------- --------- --------------
A,B,C 000 0
A,B 001 1
A 011 3
NULL 111 7
SELECT SGNYEAR,AREACODE,SUM(CARGOVOLUME) S,GROUPING(AREACODE) YorN,
GROUPING_ID(SGNYEAR,AREACODE) ORDERS
FROM TEST3
WHERE SGNYEAR BETWEEN '2002' AND '2006'
GROUP BY ROLLUP(SGNYEAR,AREACODE) --括号内的内容保持一致
HAVING GROUPING_ID(SGNYEAR,AREACODE) IN (0,3); --括号内的内容保持一致,但是可以改变顺序来到达某些统计目的
SGNYEAR AREACODE S YORN ORDERS
--------- --------------------- ---------- ---------- ----------
2002 150000 37239 0 0
2002 230000 12919 0 0
2002 330000 2327 0 0
2002 360000 0 0 0
2003 130000 1486 0 0
2003 440000 73087 0 0
2003 640000 0 0 0
2004 410000 0 0 0
2005 140000 47697 0 0
2005 620000 4085 0 0
2006 120000 0 0 0
2006 140000 59 0 0
2006 350000 910 0 0
2006 500000 2000 0 0
2006 530000 171 0 0
181980 1 3
--------- --------------------- ---------- ---------- ----------
SELECT SGNYEAR,AREACODE,SUM(CARGOVOLUME) S,GROUPING(AREACODE) YorN,
GROUPING_ID(SGNYEAR,AREACODE) ORDERS
FROM TEST3
WHERE SGNYEAR BETWEEN '2002' AND '2006'
GROUP BY ROLLUP(SGNYEAR,AREACODE)
HAVING GROUPING_ID(AREACODE,SGNYEAR) IN (0,1);
13.GROUP_ID , 区分重复分组的结果，第一次出现为 0，后面每次+1.
SELECT SGNYEAR,AREACODE,transtypecode,SUM(CARGOVOLUME) S,GROUP_ID() GI,
GROUPING_ID(AREACODE,transtypecode,SGNYEAR) LV
FROM TEST3
WHERE SGNYEAR BETWEEN '2002' AND '2003'
GROUP BY ROLLUP(AREACODE,transtypecode,SGNYEAR) ,ROLLUP(SGNYEAR)
HAVING GROUP_ID()=0;
14.应用
SELECT SYMBOL,ENDDATE,ITEM,SUM(AMOUNT) ,GROUPING_ID(SYMBOL,ENDDATE,ITEM) LV,
DECODE(GROUPING_ID(SYMBOL,ENDDATE,ITEM) ,3,'公司总计',0 ,'标准小计',7,'总合计',1,'公司年合计') LVNAME,
GROUP_ID() GI
FROM INPUT.TBL_CHN_I_Notes_AdminExpense
GROUP BY ROLLUP(SYMBOL,ENDDATE,ITEM)
--HAVING GROUPING_ID(SYMBOL,ENDDATE,ITEM) IN (0,3,7)
ORDER BY SYMBOL,ENDDATE,ITEM;
--
SELECT SYMBOL,ENDDATE,ITEM,SUM(AMOUNT)--求每个公司每年，横跨项目计费用
FROM INPUT.TBL_CHN_I_Notes_AdminExpense
GROUP BY SYMBOL,ENDDATE,ROLLUP(ITEM)
--
