第 12 章 分析函數 12.1 分析函數如何工作 語法 FUNCTION_NAME（<參數>，…） OVER （<PARTITION BY 表達式，…> <ORDER BY 表達式 <ASC DESC> <NULLS FIRST NULLS LAST>> <WINDOWING子句>） PARTITION子句 ORDER BY子句 WINDOWING子句 缺省時相當于RANGE UNBOUNDED PRECEDING 1. 值域窗（RANGE WINDOW） RANGE N PRECEDING 僅對數值或日期類型有效，選定窗為排序后當前行之前，某列（即排序列）值大于/小于（當前行該列值 –/+ N）的所有行，因此與ORDER BY子句有關系。 2. 行窗（ROW WINDOW） ROWS N PRECEDING 選定窗為當前行及之前N行。 還可以加上BETWEEN AND 形式，例如RANGE BETWEEN m PRECEDING AND n FOLLOWING 函數 AVG（<distinct all> eXPr） 一組或選定窗中表達式的平均值 CORR（expr， expr） 即COVAR_POP（exp1，exp2） / （STDDEV_POP（expr1） * STDDEV_POP（expr2）），兩個表達式的互相關，-1（反相關） ~ 1（正相關），0表示不相關 COUNT（<distinct> <*> <expr>） 計數 COVAR_POP（expr， expr） 總體協方差 COVAR_SAMP（expr， expr） 樣本協方差 CUME_DIST 累積分布，即行在組中的相對位置，返回0 ~ 1 DENSE_RANK 行的相對排序（與ORDER BY搭配），相同的值具有一樣的序數（NULL計為相同），并不留空序數 FIRST_VALUE 一個組的第一個值 LAG（expr， <offset>， <default>） 訪問之前的行，OFFSET是缺省為1 的正數，表示相對行數，DEFAULT是當超出選定窗范圍時的返回值（如第一行不存在之前行） LAST_VALUE 一個組的最后一個值 LEAD（expr， <offset>， <default>） 訪問之后的行，OFFSET是缺省為1 的正數，表示相對行數，DEFAULT是當超出選定窗范圍時的返回值（如最后行不存在之前行） MAXexpr） 最大值 MIN（expr） 最小值 NTILE（expr） 按表達式的值和行在組中的位置編號，如表達式為4，則組分4份，分別為1 ~ 4的值，而不能等分則多出的部分在值最小的那組 PERCENT_RANK 類似CUME_DIST，1/（行的序數 - 1） RANK 相對序數，答應并列，并空出隨后序號 RATIO_TO_REPORT（expr） 表達式值 / SUM（表達式值） REGR_ xxxx（expr， expr） 線性回歸函數 ROW_NUMBER 排序的組中行的偏移 STDDEV（expr） 標準差 STDDEV_POP（expr） 總體標準差 STDDEV_SAMP（expr） 樣本標準差 SUM（expr） 合計 VAR_POP（expr） 總體方差 VAR_SAMP（expr） 樣本方差 VARIANCE（expr） 方差 12.2 例子 豎表轉橫表 一般形式為將一個列為C！， C2， … CN的表，以C1， C2， … CX為基準，將CX+1， … CN的不同值改為列。一般化的語法： SELECT C1， C2， … CX， MAX(DECODE(rn, 1, CX+1, NULL)) CX+1_1, … MAX(DECODE(rn, 1, CN, NULL)) CN_1 MAX(DECODE(rn, 2, CX+1, NULL)) CX+1_2, … MAX(DECODE(rn, 2, CN, NULL)) CN_2 … MAX(DECODE(rn,N,CX+1, NULL)) CX+1_N, … MAX(DECODE(rn,N, CN, NULL)) CN_NFROM(SELECT C1, C2, … CN,ROW_NUMBER() OVER (PARTITION BY C1, C2, … CX ORDER BY <something>) rn FROM T WHERE …)
GROUP BY C1, C2, … CX;通用包：CREATE OR REPLACE PACKAGE pkg_pivotAS TYPE refcursor IS REF CURSOR; TYPE ARRAY IS TABLE OF VARCHAR2(30); PROCEDURE pivot(p_max_cols IN NUMBER DEFAULT NULL, p_max_cols_query IN VARCHAR2 DEFAULT NULL, p_query IN VARCHAR2, p_anchor IN ARRAY, p_pivot IN ARRAY, p_cursor IN OUT refcursor);END;CREATE OR REPLACE PACKAGE BODY pkg_pivotAS PROCEDURE pivot(p_max_cols IN NUMBER DEFAULT NULL, p_max_cols_query IN VARCHAR2 DEFAULT NULL, p_query IN VARCHAR2, p_anchor IN ARRAY, p_pivot IN ARRAY, p_cursor IN OUT refcursor) AS l_max_cols NUMBER; l_query LONG; l_cnames ARRAY; BEGIN IF (p_max_cols IS NOT NULL) THEN EXECUTE IMMEDIATE p_max_cols_query INTO l_max_cols; ELSE RAISE_application_ERROR(-20001, 'Cannot figure out max cols'); END IF; l_query := 'select '; FOR i IN 1 .. p_anchor.count LOOP l_query := l_query p_anchor(i) ','; END LOOP; FOR i IN 1 .. l_max_cols LOOP FOR j IN 1 .. p_pivot.count LOOP l_query := l_query 'max(decode(rn,'i','p_pivot(j)',null)) 'p_pivot(j) '_' i ','; END LOOP; END LOOP; l_query := RTRIM(l_query,',') ' from (' p_query ') group by ';
FOR i IN 1 .. p_anchor.count LOOP l_query := l_query p_anchor(i) ','; END LOOP; l_query := RTRIM(l_query,','); EXECUTE IMMEDIATE 'alter session set cursor_sharing=force'; OPEN p_cursor FOR l_query; EXECUTE IMMEDIATE 'alter session set cursor_sharing=exact'; END;END; 其中： p_max_cols_query為SELECT MAX（COUNT（*）） FROM TABLE_NAME GROUP BY C1， C2， … CX； p_query為SELECT C1， C2， … CN ROW_NUMBER（） OVER （PARTITION BY C1， C2， … CX ORDER BY <something>） rn FROM TABLE_NAME； p_anchor為pkg_pivot.array（C1， C2， … CX） p_pivot為pkg_pivot.array（CX+1， CX+2， … CN） p_cursor為返回的游標。 12.3 最后說明 PL/SQL與分析函數 PL/SQL不支持分析函數的語法，可以通過以下兩種方法解決： １。使用動態游標； ２。將含分析函數的語句創建為視圖。 WHERE子句中的分析函數 由于查詢僅在最后的ORDER BY子句前執行分析函數，因此WHERE條件中無法使用分析函數，只能利用嵌套循環實現。
