Oracle笔记-分析函数

　　第 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条件中无法使用分析函数，只能利用嵌套循环实现。