Oracle笔记-分析函数

iveerson_0420

iveerson_0420

2016-02-19 19:45

最近很多朋友喜欢上设计,但是大家却不知道如何去做,别担心有图老师给你解答,史上最全最棒的详细解说让你一看就懂。

  第 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)

(本文来源于图老师网站,更多请访问http://m.tulaoshi.com/bianchengyuyan/)

  计数

  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

(本文来源于图老师网站,更多请访问http://m.tulaoshi.com/bianchengyuyan/)

        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不支持分析函数的语法,可以通过以下两种方法解决:

  1。使用动态游标;

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

  WHERE子句中的分析函数

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

展开更多 50%)
分享

猜你喜欢

Oracle笔记-分析函数

编程语言 网络编程
Oracle笔记-分析函数

Oracle分析函数学习笔记2

编程语言 网络编程
Oracle分析函数学习笔记2

s8lol主宰符文怎么配

英雄联盟 网络游戏
s8lol主宰符文怎么配

Oracle分析函数学习笔记1

编程语言 网络编程
Oracle分析函数学习笔记1

Oracle 分析函数的使用

编程语言 网络编程
Oracle 分析函数的使用

lol偷钱流符文搭配推荐

英雄联盟 网络游戏
lol偷钱流符文搭配推荐

Oracle和SQL Server分析挖掘函数

编程语言 网络编程
Oracle和SQL Server分析挖掘函数

oracle 函数

编程语言 网络编程
oracle 函数

lolAD刺客新符文搭配推荐

英雄联盟
lolAD刺客新符文搭配推荐

SQL Server 7.0与以前的版本相比安全模式中最重要的

SQL Server 7.0与以前的版本相比安全模式中最重要的

Linux系统中如何修改网卡DNS?

Linux系统中如何修改网卡DNS?
下拉加载更多内容 ↓