第 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_N
FROM
(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_pivot
AS
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_pivot
AS
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條件中無法使用分析函數，只能利用嵌套循環實現。
查看本文來源