CALL VPD_PKG.SET_CONTEXT_COMPID('-1');
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),COUNT(*)
FROM XTBILL2011 A
GROUP BY ROLLUP(A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'));
--2)部分rollup分組
--對標準分組後，對a.typeid進行小計
SELECT A.DWDH,A.YEAR,A.TYPEID,COUNT(*)
FROM XTYWBILL A
GROUP BY A.DWDH,A.YEAR,ROLLUP(A.TYPEID); 2、CUBE
--rollup只能對“從右至左遞減”，如需要全方位的維度進行統計，需要用到cube函數
--1)
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),COUNT(*)
FROM XTBILL2011 A
GROUP BY CUBE(A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'));
--2) 部分cube:可以去掉合計與某些不需要的小計。
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),COUNT(*)
FROM XTBILL2011 A
GROUP BY A.VPD_COMPID,CUBE(TO_CHAR(A.TTIME,'MM'));
3、GROUPING SETS
--說明：僅關注單列分組，某些維度小計
--group by grouping sets(a,b,c)相當於group by a,group by b,group by c
--這三組的union all結果
--1)
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),COUNT(*)
FROM XTBILL2011 A
GROUP BY GROUPING SETS(A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'));
--2)部分grouping sets分組
--在group by的基礎上進行小計，僅關注小計的情況
SELECT A.DWDH,A.YEAR,A.TYPEID,COUNT(*)
FROM XTYWBILL A
GROUP BY A.DWDH,GROUPING SETS(A.YEAR,A.TYPEID);
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),COUNT(*)
FROM XTBILL2011 A
GROUP BY A.VPD_COMPID,GROUPING SETS(TO_CHAR(A.TTIME,'MM')); 4、CUBE,ROLLUP作為GROUPING SETS的參數
--grouping sets操作只對單列進行分組，而不提供合計的功能，如果需要grouping sets提供合計的功能，
--那麼可以使用rollup或cube作為grouping sets的參數，比如下面的語句提供合計功能：
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM') AS TTIME,COUNT(*)
FROM XTBILL2011 A
GROUP BY GROUPING SETS(ROLLUP(A.VPD_COMPID),ROLLUP(TO_CHAR(A.TTIME,'MM')));
--這條語句產生了兩個合計行，因為rollup或cube作為grouping sets的參數，相當於對每個
--rollup與cube操作的union all。所以上面的語句等價於：
SELECT A.VPD_COMPID,NULL AS TTIME,COUNT(*)
FROM XTBILL2011 A
GROUP BY ROLLUP(A.VPD_COMPID)
UNION ALL
SELECT NULL,TO_CHAR(A.TTIME,'MM'),COUNT(*)
FROM XTBILL2011 A
GROUP BY ROLLUP(TO_CHAR(A.TTIME,'MM'));
5、組合列分組簡介：
--分組方式： rollup(a,b,c)<=>group by a,b,c; group by a,b; group by null
--分組方式： rollup(a,(b,c))<=>group by a,b,c; group by a; group by null
--分組方式： rollup(a,b),rollup(c)<=>group by a,b,c; group by a,b; group by a,c; group by a; group by c; group by null
--分組方式： rollup(a,b),grouping sets(c)<=>group by a,b,c; group by a,c; group by c
--分組方式： rollup(a),rollup(b),rollup(c)<=>group by a; group by b; group by c; group by a,b; group by a,c; group by b,c; group by a,b,c; group by null 6、GROUPING函數
--為了區別哪些是小計，grouping函數派上用場了。
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),COUNT(*),GROUPING(A.VPD_COMPID),GROUPING(TO_CHAR(A.TTIME,'MM')),
DECODE(GROUPING(A.VPD_COMPID),1,'所有單位',A.VPD_COMPID) VPD_COMPID,
DECODE(TO_CHAR(A.TTIME,'MM'),1,'所有月份',TO_CHAR(A.TTIME,'MM')) TTIME
FROM XTBILL2011 A
GROUP BY ROLLUP(A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'));
--過濾某些分組結果
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),COUNT(*),GROUPING(A.VPD_COMPID),GROUPING(TO_CHAR(A.TTIME,'MM')),
DECODE(GROUPING(A.VPD_COMPID),1,'所有單位',A.VPD_COMPID) VPD_COMPID,
DECODE(TO_CHAR(A.TTIME,'MM'),1,'所有月份',TO_CHAR(A.TTIME,'MM')) TTIME
FROM XTBILL2011 A
GROUP BY ROLLUP(A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'))
HAVING GROUPING(A.VPD_COMPID)=1 OR GROUPING(TO_CHAR(A.TTIME,'MM'))=0; 7、GROUPING_ID函數
--可用rollup或cube與grouping_id組合運用，過濾出想要的分組統計資訊
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'),GROUPING_ID(A.VPD_COMPID,TO_CHAR(A.TTIME,'MM')),COUNT(*)
FROM XTBILL2011 A
GROUP BY CUBE(A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'))
HAVING GROUPING_ID(A.VPD_COMPID,TO_CHAR(A.TTIME,'MM'))=2; --1,2,3,0
--GROUPING_ID(a,b,c)過濾分組結果
分組層級 位向量 GROUPING_ID結果
a,b,C 0 0 0 0
a,B 0 0 1 1
A 0 1 1 3
匯總 1 1 1 7 8、GROUP_ID函數
--判斷重複的分組
SELECT A.VPD_COMPID,TO_CHAR(A.TTIME,'MM') AS TTIME,GROUP_ID() ID,COUNT(*)
FROM XTBILL2011 A
GROUP BY GROUPING SETS(ROLLUP(A.VPD_COMPID),ROLLUP(TO_CHAR(A.TTIME,'MM')))
-- HAVING GROUP_ID()=0;
9、執行個體應用說明：
DROP TABLE T;
CREATE TABLE t(
ORDER_DATE DATE, --訂購日期
ORDER_NO NUMBER, --訂購號
ORDER_BOOK VARCHAR2(10), --訂購書籍
ORDER_FEE NUMBER, --訂單總金額
ORDER_NUM NUMBER
);
INSERT INTO T
SELECT TO_DATE('2010-05-01','YYYY-MM-DD')+LEVEL,
TRUNC(DBMS_RANDOM.value*1000),
'book1',100+LEVEL,LEVEL
FROM DUAL
CONNECT BY LEVEL<5;
INSERT INTO T
SELECT TO_DATE('2010-06-01','YYYY-MM-DD')+LEVEL,
TRUNC(DBMS_RANDOM.value*1000),
'book2',200+LEVEL,LEVEL
FROM DUAL
CONNECT BY LEVEL<5;
--要求：每組order_book內，按日期升序排列（order_no排序不管），常規分組在前，小計在後，合計最後。
SELECT DECODE(GROUPING_ID(ORDER_DATE,ORDER_NO,ORDER_BOOK),6,ORDER_BOOK||'小計',
7,'合計',
TO_CHAR(ORDER_DATE,'YYYY-MM-DD')
) ORDER_DATE1,
ORDER_NO,
DECODE(GROUPING_ID(ORDER_DATE,ORDER_NO,ORDER_BOOK),6,NULL,ORDER_BOOK) ORDER_BOOK1,
SUM(ORDER_FEE) ORDER_FEE,
SUM(ORDER_NUM) ORDER_NUM
FROM T
GROUP BY ROLLUP(ORDER_BOOK,(ORDER_DATE,ORDER_NO))
ORDER BY ORDER_BOOK,ORDER_DATE;
--二、分析函數
--over()開窗函數 按compid分組，按ttime排序，對記錄的一個遞增統計處理
SELECT COUNT(SUMMONEY) OVER(PARTITION BY COMPID ORDER BY TTIME) CNT_SAL,
SUm(SUMMONEY) OVER(PARTITION BY COMPID ORDER BY TTIME) SUM_SAL,
MAX(SUMMONEY) OVER(PARTITION BY COMPID ORDER BY TTIME) MAX_SAL,
MIN(SUMMONEY) OVER(PARTITION BY COMPID ORDER BY TTIME) MIN_SAL,
AVG(SUMMONEY) OVER(PARTITION BY COMPID ORDER BY TTIME) AVG_SAL
FROM XTYWBILL WHERE COMPID IN ('2601','2602');
--1、rows,range改變視窗範圍的函數：
--視窗範圍為該分區的第一行和該分區的最後一行
--sum(summoney) over(partition by compid order by ttime
-- rows between unbounded preceding and unbounded following) sum_1
--視窗範圍為該分區內大於本記錄ttime-365天，並且截止到目前記錄的所有的金額累計
--sum(summoney) over(partition by compid order by ttime
-- range 365/*value_expr*/ preceding) sum_2 --2、keep的用法
SELECT BILLID,VPD_COMPID,TTIME,ZJE,
DENSE_RANK() OVER(PARTITION BY VPD_COMPID ORDER BY TTIME) DENSE_RANK,
MIN(ZJE) KEEP(DENSE_RANK FIRST ORDER BY TTIME) OVER(PARTITION BY VPD_COMPID) MIN_FIRST,
MIN(ZJE) KEEP(DENSE_RANK LAST ORDER BY TTIME) OVER(PARTITION BY VPD_COMPID) MIN_LAST,
MAX(ZJE) KEEP(DENSE_RANK LAST ORDER BY TTIME) OVER(PARTITION BY VPD_COMPID) MAX_LAST
FROM XTBILL2011;
--min(zje) dense_rank first order by ttime 是從時間最早的記錄中找到最小的金額.
--max(zje) dense_rank last order by ttime 是從時間最晚的記錄中找到最大的金額.
--注意:keep只能與dense_rank first\dense_rank last搭配使用. --3、統計函數：
SELECT BILLID,VPD_COMPID,TTIME,ZJE,
MIN(ZJE) OVER(PARTITION BY VPD_COMPID ORDER BY TTIME) AS COMP_MIN,
MAX(ZJE) OVER(PARTITION BY VPD_COMPID ORDER BY TTIME) AS COMP_MAX,
AVG(ZJE) OVER(PARTITION BY VPD_COMPID ORDER BY TTIME) AS COMP_AVG,
SUM(ZJE) OVER(PARTITION BY VPD_COMPID ORDER BY TTIME) AS COMP_SUM,
COUNT(*) OVER(PARTITION BY VPD_COMPID ORDER BY ZJE) AS COUNT_BY,
COUNT(*) OVER(PARTITION BY VPD_COMPID ORDER BY ZJE RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) AS BY_RANGE
FROM XTBILL2011;
--4、排序函數：
--問：rank()\dense_rank()區別：
RANK() DENSE_RANK()
1 1
1 1
3 2
--LOG(zje,1,0) --找到前面記錄的值，1:表示找前一個的值，0：表示找不到 預設的值
--LEAD(zje,1) --找到後面記錄的值，1：表示找前一個的值，0：表示找不到 預設的值
--first_value() --取出該視窗的第一個值。
--last_value() --取該視窗最後一個值，注意是按漸進順序來的，都是這樣。
--null值排最後,作為最大值列 SELECT BILLID,VPD_COMPID,TTIME,ZJE,
/*RANK() OVER(PARTITION BY VPD_COMPID ORDER BY ZJE) AS RANK,
DENSE_RANK() OVER(PARTITION BY VPD_COMPID ORDER BY ZJE) AS DENSE_RANK,
MIN(ZJE) KEEP(DENSE_RANK FIRST ORDER BY TTIME) OVER(PARTITION BY VPD_COMPID) WORST,
MAX(ZJE) KEEP(DENSE_RANK LAST ORDER BY TTIME) OVER(PARTITION BY VPD_COMPID) BEST,
LAG(ZJE,1,0) OVER(ORDER BY TTIME) AS PREV_ZJE,
LEAD(ZJE,1,0) OVER(ORDER BY TTIME) AS NEXT_SAL,*/
FIRST_VALUE(BILLID) OVER(PARTITION BY VPD_COMPID ORDER BY ZJE) AS FIRST_VALUE_ASC,
FIRST_VALUE(BILLID) OVER(PARTITION BY VPD_COMPID ORDER BY NVL(ZJE,0) DESC) AS FIRST_VALUE_DESC,
LAST_VALUE(BILLID) OVER(PARTITION BY VPD_COMPID ORDER BY NVL(ZJE,0)) AS LAST_VALUE_ASC,
LAST_VALUE(BILLID) OVER(PARTITION BY VPD_COMPID ORDER BY ZJE DESC) AS LAST_VALUE_DESC,
ROW_NUMBER() OVER(PARTITION BY VPD_COMPID ORDER BY BILLID) AS ROW_NUMBER
FROM XTBILL2011 ORDER BY VPD_COMPID,ZJE DESC;
--5、ratio_to_report() 函數
--記錄，所佔的百分比
SELECT BILLID,VPD_COMPID,TTIME,ZJE,
RATIO_TO_REPORT(ZJE) OVER()
FROM XTBILL2011;
三、樹查詢相關的函數
--1、ORDER SIBLINGS BY 先按上級的compid排序，再按下級的compid排序。
--2、level與rpad\lpad組合應用，構造可通的層級
--3、CONNECT_BY_ISLEAF 判斷當前結點是否為葉子結點，0表示為非葉子結點，1則表示為葉子結點
--（如果不存在下級節點就是路子節點）
--4、CONNECT_BY_ROOT 列出樹的根結點對應的欄位值，需與欄位值組合使用
--5、SYS_CONNECT_BY_PATH 函數就是從start with 開始的地方開始遍曆，並記下其遍曆到的節點，
-- start with 開始的地方被視為根節點，將遍曆到的路徑根據函數中的分隔字元，組成一個新的
-- 字串。
SELECT LEVEL,LPAD(' ',(LEVEL-1)*3)||A.COMPID,CONNECT_BY_ISLEAF,
CONNECT_BY_ROOT COMPNAME ROOT_COMPNAME,CONNECT_BY_ISCYCLE "CYCLE",
SYS_CONNECT_BY_PATH(COMPID,'/') FORMAT_COMPID,
SYS_CONNECT_BY_PATH(COMPNAME,'->') FORMAT_COMPNAME,
A.*
FROM COMPANYINFO A
START WITH A.COMPID = '0000'
CONNECT BY NOCYCLE PRIOR A.COMPID = A.PCOMPID
ORDER SIBLINGS BY COMPID;
SELECT * FROM COMPANYINFO WHERE COMPID='0000' FOR UPDATE;
--with關鍵字與樹查詢組合應用例子：
--查詢與compid=’0001‘的同級節點
WITH COMPANYINFO_TMP AS
(SELECT LEVEL LEV,LPAD(' ',(LEVEL-1)*3)||A.COMPID,CONNECT_BY_ISLEAF,
A.*
FROM COMPANYINFO A
START WITH A.COMPID = '0000'
CONNECT BY PRIOR A.COMPID = A.PCOMPID
)
SELECT * FROM COMPANYINFO_TMP
WHERE LEV=(SELECT LEV FROM COMPANYINFO_TMP
WHERE COMPID='0001') ORDER BY COMPID;
四、其它：
NVL(a,b); --若a為null，則返回b,否則返回a(即不轉換)
NVL2(a,b,c); --不管a是不是Null都要轉換，如果為空白，返回c,否則返回b.
NULLIF(a,b); --判斷a與b是否相等，若相等則返回null,不支援類型自動轉換。
COALESC(a,b,...,n); --從左至右返回第1個非null值，若所有的列表元素都為null,則返回null.它有短路計算功能，
--比如，a為null,b非null,則返回b的值，不再計算下一個值。
DECODE\SIGN;
--應用例子：
WITH T AS
(SELECT LEVEL ID
FROM DUAL
CONNECT BY LEVEL<10
)
SELECT ID,
DECODE(-1,SIGN(ID-5),'low',
SIGN(ID-8),'mid',
'high') RESULTS
FROM T;
--decode的常用應用之一是實現固定行轉列：
--下面的一decode的綜合應用：查詢借貨不平衡的資料
CALL VPD_PKG.SET_CONTEXT_COMPID('-1') ;
SELECT BILLID,SUM(DECODE(BZJD,1,TMONEYF,0)) AS DFJE,SUM(DECODE(BZJD,1,0,TMONEYF)) AS JFJE
FROM (SELECT A.BILLID,DECODE(A.CXBZ,1,1-A.BZJD,A.BZJD) AS BZJD,SUM(DECODE(A.CXBZ,1,-1*A.TMONEYF,A.TMONEYF)) AS TMONEYF
FROM XTBILLMX2010 A,XTBILL2010 B
WHERE B.BILLID=A.BILLID AND B.BZWC=1
GROUP BY A.BILLID,DECODE(A.CXBZ,1,1-A.BZJD,A.BZJD))
GROUP BY BILLID
HAVING SUM(DECODE(BZJD,1,TMONEYF,0))<>SUM(DECODE(BZJD,1,0,TMONEYF));
