Oracle笔记-分析函数

　　第 12 章 分析函数

　　12.1 分析函数如何工作

　　语法

中国网管联盟bitsCN.com

　　FUNCTION_NAME（<参数>，…） 网管联盟bitsCN_com

　　OVER

网管网www_bitscn_com

　　（<PARTITION BY 表达式，…> <ORDER BY 表达式 <ASC | DESC> <NULLS FIRST | NULLS LAST>> <WINDOWING子句>） 网管论坛bbs_bitsCN_com

　　PARTITION子句 网管u家u.bitsCN.com

　　ORDER BY子句

　　WINDOWING子句

网管论坛bbs_bitsCN_com

　　缺省时相当于RANGE UNBOUNDED PRECEDING

　　1. 值域窗（RANGE WINDOW）

　　RANGE N PRECEDING 网管联盟bitsCN@com

　　仅对数值或日期类型有效，选定窗为排序后当前行之前，某列（即排序列）值大于/小于（当前行该列值 –/+ N）的所有行，因此与ORDER BY子句有关系。

网管联盟bitsCN_com

　　2. 行窗（ROW WINDOW） 网管联盟bitsCN_com

　　ROWS N PRECEDING

网管联盟bitsCN@com

　　选定窗为当前行及之前N行。

　　还可以加上BETWEEN AND 形式，例如RANGE BETWEEN m PRECEDING AND n FOLLOWING

网管联盟bitsCN_com

　　函数 网管有家bitscn.net

　　AVG（<distinct | all> expr） 网管bitscn_com

　　一组或选定窗中表达式的平均值

　　CORR（expr， expr） 网管u家bitscn.net

　　即COVAR_POP（exp1，exp2） / （STDDEV_POP（expr1） * STDDEV_POP（expr2）），两个表达式的互相关，-1（反相关） ~ 1（正相关），0表示不相关 网管u家u.bitsCN.com

　　COUNT（<distinct> <*> <expr>）

网管下载dl.bitscn.com

　　计数 网管下载dl.bitscn.com

　　COVAR_POP（expr， expr） 中国网管联盟bitsCN.com

　　总体协方差

　　COVAR_SAMP（expr， expr）

　　样本协方差

网管u家u.bitscn@com

　　CUME_DIST

网管u家u.bitsCN.com

　　累积分布，即行在组中的相对位置，返回0 ~ 1 网管u家www.bitscn.net

　　DENSE_RANK

网管下载dl.bitscn.com

　　行的相对排序（与ORDER BY搭配），相同的值具有一样的序数（NULL计为相同），并不留空序数 网管联盟bitsCN_com

　　FIRST_VALUE 网管下载dl.bitscn.com

　　一个组的第一个值

网管论坛bbs_bitsCN_com

　　LAG（expr， <offset>， <default>） 网管u家www.bitscn.net

　　访问之前的行，OFFSET是缺省为1 的正数，表示相对行数，DEFAULT是当超出选定窗范围时的返回值（如第一行不存在之前行）

网管有家bitscn.net

　　LAST_VALUE 网管u家u.bitsCN.com

　　一个组的最后一个值

　　LEAD（expr， <offset>， <default>） 网管朋友网www_bitscn_net

　　访问之后的行，OFFSET是缺省为1 的正数，表示相对行数，DEFAULT是当超出选定窗范围时的返回值（如最后行不存在之前行） 网管朋友网www_bitscn_net

　　MAXexpr） 网管有家www.bitscn.net

　　最大值

网管网www_bitscn_com

　　MIN（expr）

网管bitscn_com

　　最小值 网管论坛bbs_bitsCN_com

　　NTILE（expr）

中国网管联盟bitsCN.com

　　按表达式的值和行在组中的位置编号，如表达式为4，则组分4份，分别为1 ~ 4的值，而不能等分则多出的部分在值最小的那组

　　PERCENT_RANK

　　类似CUME_DIST，1/（行的序数 - 1） 网管u家bitscn.net

　　RANK

　　相对序数，允许并列，并空出随后序号

网管朋友网www_bitscn_net

　　RATIO_TO_REPORT（expr） 网管u家bitscn.net

　　表达式值 / SUM（表达式值） 中国网管联盟bitsCN.com

　　REGR_ xxxx（expr， expr） 网管下载dl.bitscn.com

　　线性回归函数

网管网www_bitscn_com

　　ROW_NUMBER

网管有家bitscn.net

　　排序的组中行的偏移

网管论坛bbs_bitsCN_com

　　STDDEV（expr）

网管联盟bitsCN@com

　　标准差 网管u家bitscn.net

　　STDDEV_POP（expr） 网管联盟bitsCN_com

　　总体标准差 网管有家www.bitscn.net

　　STDDEV_SAMP（expr） 网管有家www.bitscn.net

　　样本标准差 网管u家u.bitscn@com

　　SUM（expr） 网管u家u.bitsCN.com

　　合计 网管有家www.bitscn.net

　　VAR_POP（expr） 网管朋友网www_bitscn_net

　　总体方差 网管下载dl.bitscn.com

　　VAR_SAMP（expr）

网管论坛bbs_bitsCN_com

　　样本方差

网管联盟bitsCN@com

　　VARIANCE（expr） 网管有家www.bitscn.net

　　方差

　　12.2 例子

　　竖表转横表

　　一般形式为将一个列为C！， C2， … CN的表，以C1， C2， … CX为基准，将CX+1， … CN的不同值改为列。一般化的语法：

网管网www.bitscn.com

　　SELECT C1， C2， … CX， 网管u家bitscn.net

  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

网管论坛bbs_bitsCN_com

FROM

(SELECT C1, C2, … CN,

ROW_NUMBER() OVER (PARTITION BY C1, C2, … CX ORDER BY <something>) rn 中国网管论坛bbs.bitsCN.com

   FROM T 网管bitscn_com

   WHERE …)

网管网www.bitscn.com

GROUP BY C1, C2, … CX; 网管bitscn_com

通用包： 网管下载dl.bitscn.com

CREATE OR REPLACE PACKAGE pkg_pivot 中国网管联盟bitsCN.com

AS 网管有家www.bitscn.net

  TYPE refcursor IS REF CURSOR;

中国网管论坛bbs.bitsCN.com

  TYPE ARRAY IS TABLE OF VARCHAR2(30); 网管网www_bitscn_com

  PROCEDURE pivot(p_max_cols IN NUMBER DEFAULT NULL, 网管u家u.bitscn@com

                  p_max_cols_query IN VARCHAR2 DEFAULT NULL,

                  p_query IN VARCHAR2,

                  p_anchor IN ARRAY, 网管有家bitscn.net

                  p_pivot IN ARRAY, 中国网管论坛bbs.bitsCN.com

                  p_cursor IN OUT refcursor); 网管联盟bitsCN_com

END;

网管朋友网www_bitscn_net

CREATE OR REPLACE PACKAGE BODY pkg_pivot

网管有家bitscn.net

AS

中国网管论坛bbs.bitsCN.com

  PROCEDURE pivot(p_max_cols IN NUMBER DEFAULT NULL, 中国网管联盟bitsCN.com

                  p_max_cols_query IN VARCHAR2 DEFAULT NULL,

                  p_query IN VARCHAR2,

                  p_anchor IN ARRAY,

                  p_pivot IN ARRAY, 网管联盟bitsCN_com

                  p_cursor IN OUT refcursor) 网管有家bitscn.net

  AS 网管bitscn_com

    l_max_cols NUMBER; 网管有家www.bitscn.net

    l_query LONG; 网管u家bitscn.net

    l_cnames ARRAY;

  BEGIN 网管网www_bitscn_com

    IF (p_max_cols IS NOT NULL) 网管有家bitscn.net

    THEN 网管联盟bitsCN_com

      EXECUTE IMMEDIATE p_max_cols_query INTO l_max_cols; 网管u家u.bitscn@com

    ELSE

网管网www_bitscn_com

      RAISE_APPLICATION_ERROR(-20001, 'Cannot figure out max cols');

    END IF; 网管有家bitscn.net

    l_query := 'select '; 中国网管论坛bbs.bitsCN.com

    FOR i IN 1 .. p_anchor.count

网管下载dl.bitscn.com

    LOOP 网管联盟bitsCN@com

      l_query := l_query || p_anchor(i) || ',';

网管u家www.bitscn.net

    END LOOP;

网管bitscn_com

    FOR i IN 1 .. l_max_cols 网管网www.bitscn.com

    LOOP 网管网www_bitscn_com

      FOR j IN 1 .. p_pivot.count 网管朋友网www_bitscn_net

      LOOP

网管u家u.bitsCN.com

        l_query := l_query || 'max(decode(rn,'||i||','||p_pivot(j)||',null)) '||p_pivot(j) || '_' || i || ','; 网管u家bitscn.net

      END LOOP; 网管u家u.bitsCN.com

    END LOOP;

    l_query := RTRIM(l_query,',') || ' from (' || p_query || ') group by ';

    FOR i IN 1 .. p_anchor.count 网管有家bitscn.net

    LOOP

网管有家www.bitscn.net

      l_query := l_query || p_anchor(i) || ',';

    END LOOP;

    l_query := RTRIM(l_query,',');

中国网管联盟bitsCN.com

    EXECUTE IMMEDIATE 'alter session set cursor_sharing=force'; 网管论坛bbs_bitsCN_com

    OPEN p_cursor FOR l_query; 网管有家www.bitscn.net

    EXECUTE IMMEDIATE 'alter session set cursor_sharing=exact'; 网管网www_bitscn_com

  END;

END;

网管下载dl.bitscn.com

　　其中：

网管u家bitscn.net

　　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；

网管u家www.bitscn.net

　　p_anchor为pkg_pivot.array（C1， C2， … CX） 网管u家u.bitscn@com

　　p_pivot为pkg_pivot.array（CX+1， CX+2， … CN） 网管有家www.bitscn.net

　　p_cursor为返回的游标。

　　12.3 最后说明

网管bitscn_com

　　PL/SQL与分析函数 网管u家www.bitscn.net

　　PL/SQL不支持分析函数的语法，可以通过以下两种方法解决： 网管朋友网www_bitscn_net

　　１。使用动态游标； 中国网管论坛bbs.bitsCN.com

　　２。将含分析函数的语句创建为视图。

网管下载dl.bitscn.com

　　WHERE子句中的分析函数 网管论坛bbs_bitsCN_com

　　由于查询仅在最后的ORDER BY子句前执行分析函数，因此WHERE条件中无法使用分析函数，只能利用嵌套循环实现。 网管网www_bitscn_com