分析函数入门--摘自《Oracle专家高级编程》

序言

    有一些非常普通的操作,但是很不容易使用SQL语言编写出来,大致包括:

① 计算运行的总数

例如：逐行的显示一个部门的累计工资每行包括前面各行的工资总和。

② 查找一组内的百分数

例如：显示在某些部门中付给个人的总工资的百分数，将他们的工资与该部门的工资总数相除。

③ 前N个查询

例如：按照地区查找前N个工资最高的人或者前N份最高的销售款。

④ 计算正在流动的平均值

例如：将当前行的值与当前N行的值加在一起计算平均数。

⑤ 执行带等级的查询

例如：显示一个部门内一个人工资的相关等级。

    其实，在Oracle8.1.6中就已经考虑到了这些需求，提供了分析函数，目前SQL委员会正在讨论是否将这些扩展包含到SQL规范当中。

1、范例

select ename,deptno,sal,

sum(sal) over

  (order by deptno,ename) running_total,  --工资总计

sum(sal) over

  (partition by deptno order by ename) department_total,--部门工资总计

row_number() over

  (partition by deptno order by ename)seq --部门内序列

from emp

order by deptno,ename

    上述代码，对于完整的查询，能够计算一个RUNNING_TOTAL。这是使用完整的排序后的结果，通过SUM(SAL) OVER(PARTITION BY DEPTNO,ENAME)来完成。也能在每个部门内部计算一个连续的总和，在下一个部门开始的时候，进行复位，即重新赋值。SUM(SAL)中的PARTITION BY DEPTNO使之发生。PARTITION BY DEPTNO是一个分区子句，它在查询中指定，使数据分成组。ROW_NUMBER()函数随后根据排序标准（为了显示此位置添加一个SEQ列），用于对返回的每个组计算其序列号。因此，可以看到，按照ENAME排序时，SCOTT在部门20中为第四行，ROW_NUMBER（）还有其他用处，比如对结果集进行颠倒顺序等。

    这个新的函数集保持了一些让人兴奋的可能性，它打开了观看数据的全新的方法，减少了要花费大量的时间开发的大量程序代码以及复杂的（或者效率低的）查询，却达到了同样的效果。分析函数可能超过过去“纯粹的关系方法”。下面使用1000行而不是14行来比较上述查询的性能。新的分析函数和“旧的”关系方法用于测试查询性能，下述两条语句将用一个索引建立带有ENAME,DEPTNO,和SAL列的SCOOTT.EMP的副本：

SQL>  create table t

    as

    select object_name ename,

    mod(object_id,50) deptno,

    object_id sal

    from all_objects

    where rownum<=1000

    /

表已创建。

SQL>  create index t_index on t(deptno,ename);

可以在新的表上执行查询，使用AUTOTRACE方式跟踪，查看需要进行多达的工作量（需要让PLUSTRACE角色有效）：

SQL>  set autotrace traceonly

SQL>  set autotrace off

    经过了两个同样的查询，并且得到了相同的结果，但是两个SQL的效率是完全不一样的，标准SQL运行时间是分析函数的很多倍，并且运行的逻辑IO数量也增加了很多，分析函数使用特别少的资源处理结果集，并且减少了运行的时间。不仅如此，一旦理解了分析函数的语法，将会发现他们比等价的标准SQL更加容易编码。

2、分析函数是如何工作的

    本节包括语法细节和术语定义等，并且提供一些例子。并将演示26个新函数中的大部分（不是全部，因为很多例子重复）。分析函数使用同样的一般性的语法，并且很多函数提供了特殊的功能，用于一些不是由日常开发人员使用的技术规范。

语法：

    表面看来，分析函数的语法相当之简单，但是光看外表是很容易受骗的，它以如下形式开头：

    FUNCTION_NAME(<argument>,<argument>....)

    OVER

    (<Partition-Clause><Order-by-Clause><Windowing-Clause>)

    对于一个分析函数来说，至少有4个部分。它可以用参数、分区子句、排序子句和一个开窗子句调用，在前面介绍的例子为：

    sum(sal) over

      (partition by deptno

        order by ename)department_total,

    在上面的例子中：

    ①sum就是函数名

    ②（sal）是分析函数的参数，每个函数都有0到3个参数，参数可以是表达式，例如SUM(SAL+COMM);

    ③OVER是一个关键字，用于标识分析函数。否则查询分析器不能够区别SUM()聚集函数和SUM()分析函数。跟在OVER关键字后的字母描述了该分析函数将其执行的数据片段。

    ④PARTITION BY DEPTNO 是可选的分区子句，如果不存在任何子句，则全部的结果集可以看做一个单一的大区。可以使用它来将结果集分组，并且将分析函数应用于各组中，而不是全部的结果集中。在介绍的例子中，当省去分居子句时，产生用于全部结果集的SAL的SUM。通过DEPTNO进行划分之后，就可以按照DEPTNO计算出来SAL的总和（每组都要对连续性总和进行复位）。

    ⑤ORDER BY ENAME是可选的Order by子句，有些函数需要它，有些则不需要。依靠已排序数据的那些函数，例如，用于访问结果集中前一行和后一行的LAG和LEAD，必须使用ORDER BY子句，其他函数，例如AVG，则不需要。在使用任何排序的开窗函数时，该子句是强制性的，它指定了在计算分析函数的时候，数据是如何排序的。在本例中，不必按照DEPTNO和ENAME一起来排序，因为它是按照DEPTNO进行划分的，这意味着所划分的列是排序键码的一部分，Order by 轮流应用于每个分区中。

1.Function 子句

    Oracle提供了26个分析函数提供刚给大家使用，它们按照功能可以分为5类。

    有多种等级（ranking）函数，他们对于寻找前N种查询的回答是非常有用的。前面已经使用过这样的函数了，例如在前面例子中生成SEQ列的ROW_NUMBER函数。该函数是在部门中按照他们的ENAME对他们进行排序。可以很容易的按照SALARY或者其他属性对他们进行排序。

    在计算不同的累计的时候，开窗（windowing）函数是很有用的。在前面的例子中已看到了有关这个函数的两个例子，即在不同的组上计算SUM(SAL)。可使用很多其他的函数来代替SUM,例如有COUNT、AVG、MIN还有MAX等。

    有不同的制表（reporting）函数，他们与前面的开窗函数非常相似。事实上，他们的名字也一样，例如SUM、MIN、MAX等。开窗函数用于数据的一个窗口上，正如前面的例子中求连续的总和那样，而制表函数则用于一个分区或者一组上的所有列，例如，在开始的查询中，如果被问及：

     sum(sal) over() total_salary,

    sum(sal) over(partition by deptno) total_salary_for_department

    然后，将得到的该组的总和，而不是像前面那样的连续总和。开窗函数与制表函数的关键不同之处在于OVER语句上缺少一个ORDER BY子句，缺少ORDER BY 的时候，函数应用于该组中的每一行，有ORDER BY 子句的时候，函数用于窗口。

     可以使用的函数还有LAG和LEAD。这些函数允许在结果集中向前或者向后检索值，为了避免数据的自连接，他们是非常有用的。例如，如果有一张表，按照日期记录了来检查的病人，并想要计算每个病人来检查的时间间隔，LAG函数可以解决这个问题。只要按照病人划分数据，并将他们按日期排序就可以了。LAG函数很容易从前面的记录返回病人的数据，然后减去两个日期。在引入分析函数之间，为了检索数据，要求将病人的数据与其自身做复杂的自连接。

     最后，有很多统计函数，例如VAR_POP、VAR_SAMP、STDEV_POP,以及线性的衰减函数等，这些函数计算任何未排序分区的统计值。

2.Partition子句

    按照划分表达式的标准，PARTITION BY子句将一个简单的结果分成N组。单词“分区”和“组”在此以及在Oracle的官方文档中，是被用作同义词，分析函数分别用于每一组中，例如，在前面师范累积函数SAL的时候，按照DEPTNO进行划分，当DEPTNO在结果集发生了变化的时候，则将累计的SAL复位为0，并且综合重新开始计算。

    如果省略了分区子句，则全部的结果集被看做一个单一的组。在介绍的例子里面，为了获得全部结果集的一个连续的综合，使用了sum(sal),而不用分区子句。

    注意，查询中分析函数的每个实例有一个完全不同的分区子句，开始的那个简单的例子其实已经做到了这点，这是非常令人感兴趣的。RUNNING_TOTAL列并没有提供一个分区子句，因此，全部的结果集都是它目标组。另外一个方面，DEPARTMENTAL_TOTAL列按照部门划分结果集，允许在一个部门内计算连续的总和。

    分区子句语法简单，并且在语法上与SQL查询的GROUP BY 子句非常相似。

    PARTITION BY EXPRESSION<EXPRESSION><EXPRESSION>

3.Order by 子句

    ORDER BY 子句指定数据是如何存储在每个组(分区)内的，这明确地影响了任何分析函数的结果。在有ORDER BY子句存在时，对分析函数进行了不同的计算。作为一个很简单的例子，在有，和没有ORDER BY 子句的时候使用AVG()，会发生什么。

    select ename,sal,avg(sal) over() from emp；

    这个使用了分析函数的SQL查询的是表中所有的员工的平均工资。

      select ename,sal,avg(sal) over(order by ename)

from emp

order by ename

/

    在没有ORDER BY子句的时候，在全部组上计算平均值，每一行给一个同样的（在此被用作reporting函数）。在用带有ORDER BY的AVG()时，每一行的平均值是那一行与前面所有行的平均值（此处用作开窗函数），例如，带ORDER BY子句的查询中ALLEN的平均工资为1350（1100与1600的平均值）。

    提示：

    可以说， 分析函数ORDER BY 的存在将添加一个默认的开窗子句，这意味着计算中所使用的行的集合是当前分区中当前行和前面所有的行，没有ORDER BY,默认的窗口是全部的分区。

    为了获得整个是如何工作的真实感受，两次使用相同的分析函数，每次用一个不同的ORDER BY是有用的。在第一个例子中，使用“ORDER BY DEPTNO,ENAME”计算EMP表的连续总和，这导致从第一行到最后一行计算连续总和，行序由ORDER BY 函数指定，如果列的序号颠倒，或者排好了序的列一起发生了改变，连续总和的结果将会有很大的不同，最后一行的结果相同，但是所有的中间值是不同的。

select ename,deptno,

sum(sal) over(order by ename,deptno) sum_ename_deptno,

sum(sal) over(order by deptno,ename) sum_deptno_ename

from emp

order by ename,deptno

/

两个SUM(SAL)列都是正确的，其中一个是通过DEPTNO计算SUM(SAL),而另外一个首先通过ENAME然后通过DEPTNO来实现。由于结果集是按照（ENAME,DEPTNO）的顺序，因而按那个顺序计算的SUM(SAL）看起来更正确，但它们都得到了同样的结果，最后的总和为29025。带分析函数的ORDER BY 子句的语法如下：

     ORDER BY EXPRESSION <ASC|DESC><NULLS FIRST|NULLS LAST>

    它与用于查询的ORDER BY子句一样，但它仅仅在分区内对行进行排序，且不必与同样查询的ORDER BY一样。NULLS FIRST和NULLS LAST子句是Oracle8.1.6引入的新语法，它们允许大家指定NULLS是出现在开始还是最后，当使用DESC顺序，特别是带有分析函数的时候，这个新的功能是至关重要的。

4. Windowing 子句

    此处语法表面看起来有一点点的复杂，虽然实际上它并不是那么难，刚开始术语有点让人困惑。像RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW这样的术语，它是带ORDER BY 子句的默认窗口，不是大家日常所使用的术语。用于开窗子句的语法列出来相当的复杂，请参考Oracle SQL Reference Manual，坐着列出有关windowing 子句所有的变量，并且解释各自使用的数据集合，下面首先介绍windowing 子句的功能。

    windowing子句给出了一个定义变化或者固定的数据窗口方法，分析函数将对这些数据进行操作。在一组内基于任意变化或固定的窗口中，可用该子句来让分析函数计算它的值。例如，范围子句RANGE UNBOUNDED PRECEDING意味着将分析函数用于当前组中从第一行到当前一行的每一行。默认的窗口是一个固定的窗口，仅仅在一组的第一行开始，一直继续到当前行。如果一个窗口的使用方式如下所示：

SUM(sal)OVER

  （PARTITION BY deptno

    ORDER BY ename

    ROWS 2 PRECEDING) department_total2，

  这将在一组内创建一个变化的窗口，并且计算那一组中当前行的SAL列加上前两行SAL列的总和。若需要一个报表显示一个部门中当前雇员的工资与前面两行工资的总和，则如下所示：

select deptno,ename,sal,

sum(sal) over

  (partition by deptno

   order by ename

   rows 2 preceding) sliding_total

from emp

order by deptno,ename

/

 在此，与查询有关的部分是：

sum（sal） over

  （partition by deptno

     order by ename

     rows 2 preceding） sliding_total

  Partition子句使得SUM(SAL)在每个部门内都计算了一次,而与其他组无关(当部门发生改变的时候,sum(sal)重新复位)。Order by ename子句对每个部门内的数据按照ename进行排序，为了计算工资，这就允许开窗子句“rows 2 preceding”访问组中当前行的前两行。例如SMITH的sliding_total值为6775,这是800、3000与2975的总和，那仅仅是SMITH那行工资加上窗口中的前两行的工资。

  根据两个标准，我们可以建立窗口：数据值的范围（RANGES）或者与当前行的行偏移量。大家已经了解了RANGE子句，例如RANGE UNBOUNDED PRECEDING。请注意，要使用窗口，必须使用Order by子句，下面介绍ROW与RANGE窗口，并且完成描述这些窗口指定的不同方法。   

5. Range 窗口

  Range窗口根据WHERE子句将行集中在一起。例如，如果有“range 5 preceding”，将产生一个滑动的窗口，它在组拥有所有当前行以前5行的集合。这些单元行既可以是号码比较，也可以是日期比较，且如果使用非号码和日期的数据类型，则RANGE 是无效的。

  如果拥有带HIREDATE日期类的EMP表，则指定

   count（*） over（order by hiredate asc range 100 preceding）

  就可以找到分区中当前行以前的所有行，以便HIREDATE在当前行的HIREDATE的100天以内。这种情况下，由于日期按ASC（升序，由小到大）排序，窗口中的值包含当前组中所有行，以便HIREDATE大于当前行的HIREDATE，且在100天以内。如果使用

  count（*） over（order by hiredate desc range 100 preceding）

  对分区按DESC（降序，从大到小）排序，则将得到相同的逻辑结果，但是组中的数据排列则完全不同。它为窗口找到一个不同的行的集合，这时会发现所有行在当前行的前面，因此HIREDATE比当前行的HIREDATE大，并且在它的100天之内。有一个例子帮助大家更清楚的理解它，在此使用一个使用了FIRST_VALUE分析函数的查询，该函数返回在窗口中使用了FIRST行的表达式的值。可以很容易的看到窗口在何处开始。

select ename,sal,hiredate,hiredate-100 windowtop,

first_value(ename)

over(order by hiredate asc

    range 100 preceding)ename_proc,

first_value(hiredate)

over(order by hiredate asc

    range 100 preceding)hiredate_prec

from emp

order by hiredate asc

/

  对单一的分区按照HIREDATE ASC进行排序，使用分析函数FIRST_VALUE，查找窗口中的第一个ENAME和HIREDATE的值，如果考察CLARK行，则可以看到，它的HIREDATE为09-6月 -81，在这个日期前的100天为01-3月 -81，为方便起见，将这个日期放在了WINDOWTOP列中。然后分析函数在已经排序的分区中把每一行定义为窗口，该分区在CLARK记录的前面，且HIREDATE在09-6月 -81与01-3月 -81之间。那个窗口中ENAME的第一个值为JONES，它是分析函数返回到ENAME_PREC列中的名字。

  从HIREDATE DESC（降序）的角度去考虑：

select ename,sal,hiredate,hiredate-100 windowtop,

first_value(ename)

over(order by hiredate desc

    range 100 preceding)ename_proc,

first_value(hiredate)

over(order by hiredate desc

    range 100 preceding)hiredate_prec

from emp

order by hiredate desc

/

  如果再次考察CLARK,所选择的窗口是不同的,因为分区中的数据排序是不相同的.CLARK窗口的RANGE 100 PRECEDING 现在回到了TURNER,因为TURNER的HIREDATE是CLARK记录前的最后一个HIREDATE,该记录在CLARK的100天以内.

  有时要指出一个范围的实际大小有点令人困惑.我发现,使用FIRST_VALUE是帮助使窗口可视化,并且证明已经正确设立了参数的一个便捷方法.现在您能够清楚的“看到”这个例子的窗口，使用它们计算一些有意义的东西。我们需要报告每个雇员的工资、前100天以内的平均工资和随后100天以内的平均工资。其查询如下：

select ename,hiredate,sal,

avg(sal) 

  over (order by hiredate asc range 100 preceding)

    avg_sal_100_days_before,

avg(sal) 

  over (order by hiredate desc range 100 preceding)

    avg_sal_100_days_after

from emp

order by hiredate

/

  此处，如果再次考察CLARK，由于您了解该组内的窗口，可以看到平均工资为

2875.33与1975，可以看到2875.33等于（2975+2850+2450）/3。这些是CLARK与BLAKE以及与JONES的工资之和的平均工资。他后一百天的工资是，这是数据按降序排列的时候CLARK的工资以及与CLARK前一行工资的平均值。这个查询就可以很容易的查询出来CLARK前100天的平均工资与后100天的平均工资。

  一般来说，RANGE窗口仅仅对NUMBERS和DATES起作用，因为您不能从VARCHAR2中增加或者减去N个单元。另外的限制是ORDER BY中只能有一列，因而范围实际上是一维的，不能在N维空间中。

6. Row 窗口

  ROW窗口是物理单元，是包括在窗口中的行的物理数。使用前面的例子作为ROW分区：

     count（*） over（order by ROWS 5 preceding）

  那个窗口包括6行，当前行以及改行前面（此处的“前面”由ORDER BY子句定义的）的5行。利用ROW分区，就没有RANGE分区那样的限制了，数据可以是任意类型的，并且ORDER BY 可以包含很多列。下面的例子与前面的例子类似：

select ename,sal,hiredate,

first_value(ename)

  over(order by hiredate asc

       rows 5 preceding) ename_prec,

first_value(hiredate)

  over(order by hiredate asc

       rows 5 preceding) ename_proc

from emp

order by hiredate

/

运行结果如下所示：

  再次考察CLARK可以看到，在窗口“ROWS 5 PRECEDING”中的第一个值是SMITH，即在窗口中从CLARK往回数5行的哪一行，实际上，对BLAKE和JONES来说，SMITH是所有前面行的第一个值，这是因为SMITH是该行中的第一条记录（即使是对SMITH来说也是如此）。对该组按照降序排列：

select ename,sal,hiredate,

first_value(ename)

  over(order by hiredate desc

       rows 5 preceding) ename_prec,

first_value(hiredate)

  over(order by hiredate desc

       rows 5 preceding) ename_proc

from emp

order by hiredate desc

/

SQL>col sal format 99999

运行结果如下图所示：

    现在改组中JAMES是CLARK前面五行中的一个值，可对在他们之前或者之后的（最多）五个雇员的记录计算平均工资如下：

select ename,hiredate,sal,

avg(sal)

  over(order by hiredate asc rows 5 preceding) avg_5_before,

count(*)

  over(order by hiredate asc rows 5 preceding) obs_before,

avg(sal)

  over(order by hiredate desc rows 5 preceding) avg_5_after,

count(*)

  over(order by hiredate desc rows 5 preceding) obs_after

from emp

order by hiredate

/

运行结果如下图所示：

  请注意，在此也使用了COUNT(*)函数。这对演示有多少行参与计算平均值是非常有用的。可以很清楚的看到，对于ALLEN那条数据来说，在计算平均工资的时候，在雇佣他之前的人只使用了2条记录，而在雇佣他之后则使用了6条记录，在ALLEN的记录所在位置只有1条记录，因而在计算的时候，分析函数使用尽可能多的数据。

7. Specifying 窗口

  现在，理解了“RANGE”与“ROWS”窗口的差别，就可以研究用什么方式指定这些范围了。按照最简单的形式，可以用3种彼此排斥的语句中的一种来指定窗口。

  ①UNBOUNDED PRECEDING 这个窗口从当前分区的第一行开始，并且结束于正在处理的当前行。

  ②CURRENT ROW 该窗口从当前行开始（并且结束）就是当前行而已了！

  ③Numeric Expression PRECEDING 对于ROWS来说，该窗口从当前行之前的数字表达式（Numeric Expression）的行开始。对于RANGE来说，则从行序值小于数字表达式的当前行值开始。

  在单个表单中CURRENT ROW的范围可能从来不会使用，因为它限制分析函数为单行。在一个更加复杂的表单中，用BETWEEN子句来指定窗口，在此可能使用CURRENT ROW作为该窗口的起点，也作为其终点，BETWEEN子句的起点和终点可使用列表中所有的项来指定。

  Numeric Expression FOLLOWING 该窗口在当前行Numeric Expressing行之后的行终止（或者开始），并且从行序值大于当前航Numeric Expression行的范围开始（或终止）。

  这些窗口的一些例子为：

select deptno,ename,hiredate,

count(*) over(partition by deptno

              order by hiredate nulls first

              range 100 preceding)cnt_range,

count(*) over(partition by deptno

              order by hiredate nulls first

              rows 2 preceding)cnt_rows

from emp

where deptno in (10,20)

order by deptno,hiredate

/

执行的结果如下：

  正如所看到的，RANGE 100 PRECEDING 仅仅计算在当前分区中的行数，以便HIREDATE在HIREDATE-100和HIREDATE+100之间。在这种情况下，计数总是为1或者为2，表示在这些部门，让一个雇员在另外一个雇员的100天之内被雇佣是很稀少的，它仅仅发生了两次。然而ROWS 2 PRECEDING的窗口从1到3发生变化，这取决于离该组有多远。对该组第一行来说，计数为1（前面没有行了），对该组的下一行来说，计数为2，最后对第三行和更高的行来说，COUNT(*)保持为常数，因为只有对当前行和它前面的两行进行计数。

  现在，看看如何使用BETWEEN。迄今为止，定义的所有窗口在当前行结束，并可在结果集中往回找到更多的信息。可以定义一个窗口，以便能够处理的当前航不是该窗口中的最后一行，而是在该窗口中的中间某个地方。例如：

select ename,hiredate,

first_value(ename) over

(order by hiredate asc

range between 100 preceding and 100 following) first_value,

last_value(ename) over

(order by hiredate asc

range between 100 preceding and 100 following) last_value

from emp

order by hiredate asc

/

执行的结果如下图所示：

  再次观察CLARK，我们现在可以看到该窗口往回扩展到了JONES，往下到了TURNER。没让窗口包括100天之前或者之后所雇用的人，现在该窗口包含了在当前记录100天之前和之后所雇用的人。

  好了，现在对分析函数子句的四个组成部分的语法有了一个很好的理解了：

1）function本身

2）partition clause用于将大的结果集分为独立的组

3）order by clause 用于对数据进行排序

4）windowing clause用于定义分析函数将在起上操作的行的集合

     FUNCTION_NAME(<argument>,<argument>,...)

    OVER

    (<Partition-Clause><Order-by-Clause><Windowing Clause>)

  现在简介所有的可用函数。

2.1 分析函数的语法

  有26个可以使用的分析函数。其中，有一些同名，比如AVG和SUM。其他的有新的名字，并且提供了新的功能。本节仅仅列出可用的函数，并且对它们的用途进行简短的描述。

1） AVG(<distinct|all>expression)

用于计算一个组和窗口内表达式的平均值。Distinct用于去掉重复的数据后得到该组的平均值。

2） CORR（expression，expression）

返回一对表达式的相关系数，他们是如下的缩写：

COVAR_POP(expr1，expr2）/STDDEV_POP(expr1)*STDDEV_POP(expr2))

从统计上讲，相关性是变量之间的关联的强度，变量之间的关联意味着在某种程度上一个变量的值可以由其他变量的值进行预测。通过范围一个-1到1之间的一个数，相关系数给出了关联的强度，其中0，表示不关联。

3） COUNT(<distinct><*><expression>)

它将对一组内发生的事情进行计数。如果指定*或者一些非空常数，count将对所有的行计数。如果指定一个表达式，count返回表达式非空赋值计数。可以使用distinct将记录去掉一组中完全相同的数据后出现的行数。

4） COVAR_POP(expression，expression）

返回一对表达式的总体协方差

5） COVAR_SAMP（expression，expression）

返回一对表达式的样本协方差。

6） CUME_DIST

计算一行在组中的相对位置。CUME_DIST总是返回大于0、小于或者等于1的数，该数标识该行在N行中的位置。例如，在一个3行的组中，返回的累积分值为1/3、2/3、和3/3。

7） DENSE_RANK

根据ORDER BY 子句中表达式的值，从查询返回的每一行，计算它们与其他行的相对位置。组内的数据按照ORDER BY子句进行排序，然后给每一行赋一个号，从而形成一个序列，该序列从1开始，往后累加。每次ORDER BY表达式的值发生变化时，该序列也随之增加。有同样值的行得到同样的数字序号（认为null是相等的）。密集的序列返回的是没有间隔的数。

8） FIRST_VALUE

返回组中的第一个值

9） LAG(expression,<offset>,<defalut>)

LAG可以访问结果集中的其他行而不用进行自连接.它允许去处理游标,就好像游标是一个数组一样.在给定组中可参考当前行之前的行,这样就可以从组中与当前行一起选择以前的行.关于如何获取"下一行"可以参看LEAD

Offset是一个正整数,其默认值为1。若索引超出窗口的范围，就返回默认值（默认返回是组中的第一行）。

10） LAST_VALUE

返回组中的最后一个值。

11） LEAD(expression,<offset>,<default>)

Lead与LAG相反，LAG让您可访问组中当前行之前的行，而LEAD让您可以访问组中当前行之后的行。

Offset是一个正整数，其默认值是1.若索引超出窗口的范围，就返回默认值（默认只返回的是组中最后一行）。

12） MAX（expression）

在一个组的窗口中查找表达式的最大值。

13） MIN（expression）

在一个组的窗口中查找表达式的最小值。

14） NTITLE(expression）

将一个组分为“表达式值”的散列表示

例如,如果表达式=4，则给组中的每一行分配一个数（从1到4）。如果组中有20行，则给前五个分配1，给下5行分配2等等。如果组中的基数不是由表达式平均分开的，则对这些行进行分配的时候，组中就没有任何percentile的行数比其他prcentile的行数超过一行，最低的prcentile是那些拥有额外行的prcentile。例如，若表达式=4，行数=21，则percentile=1的有6行，percentile=2的有5行等等。

15） PERCENT_RANK

它与CUME_DIST（累积分配）函数类似。对于一个组中给定的行数来说，在计算那行的序号的时候，先减去1，然后除以1（1小于组中所求的行数）。该函数总是返回0到1（包括1）之间的数。

16） RANK

根据ORDER BY子句中表达式的值，从查询返回的每一行，计算它们与其他行的相对位置。组中的数据按照ORDER BY 子句排序，然后给每一行赋一个数字序号，从而形成一个序列，该序列从1开始，往后累加，每次ORDER BY 表达式的值发生变化的时候，该序列也随之增加。有同样值的行得到同样的数字序号。然后，如果两行的确得到同样的排序，则序数随后跳跃。若两行序数为1，则没有序数2，序列给组中的下一行分配3，DENSE_RANK则没有任何跳跃值。

17） RATIO_TO_REPORT(expression）

该函数计算expression/sum（expression))的值

它给出相对于总数的百分比,即当前行对sum的贡献。

18） REGR_xxxxxxxx（expression，expression）

这些线性回归函数适合最小二乘法回归线，有9个不同的回归函数可以使用。

19) ROW_NUMBER

返回序组中一行的偏移量，从而可用于按特定标准排序的行号。

20) STDDEV（expression）

计算当前航关于组的标准偏离

21) STDDEV_POP（expression）

该函数计算总体标准偏离，并返回总体变量的平方根，其返回值与VAR_POP函数的平方根相同。

22) STDDEV_SAMP（expression）

该函数计算累计样本标准偏离，并返回样本变量的平方根，其返回值与VAR_SAMP函数的平方根相同。

23) SUM(expression）

该函数计算组中表达式的累积值

24) VAR_POP(expression)

该函数返回非空集合的总体变量（忽略NULL），VAR_POP进行如下计算：

（SUM(expr*expr)-SUM(expr)*SUM(expr)/COUNT(expr)/COUNT(expr)）

25) VAR_SAMP(expression)

该函数返回非空集合的样本变量（忽略NULL），VAR_SAMP进行如下计算：

（SUM(expr*expr）-SUM（expr）*SUM(expr)/COUNT(expr)/COUNT(expr)-1)

26) VARIANCE（expression）

该函数返回表达式的变量，Oracle计算该变量如下：

如果表达式中的行数为1，则返回0

如果表达式中的行数大于1，则返回VAR_SAMP

2.3 范例

    现在开始介绍有趣的部分，看看如何对这些功能进行处理。我们将两个例子演示成如何利用这些新的函数进行处理，并给出一个好的工作集合范例。

    

前N个记录的查询

    经常听到的一个问题是：“如何才能通过某些字段集合得到前N个记录”。在访问这些分析函数之前，这个问题是很难回答的。现在，他们非常容易。

    然而，有些与前N个查询有关的问题，主要是在人们表达它们的方式上。在设计报表的时候要非常小心，考虑下面这些问题：

    提示：

    给出各部门销售代表工资最高的前三名。

    该问题具有二义性。这是因为有重复的值，即可能有4个人都赚同样的工资，那该怎么办？

    可以给出至少3个合理的解释，但是没有一个解释能够返回3条记录。可以解释为：

    ①给出销售人员工资最高的前三名：也就是说，查找唯一的工资总数的集合，对他们进行排序，得到最大的前三名。

    ②最多给出三个工资最高的人，如果碰巧有四个人的工资最高，答案是没有任何行记录；如果有2个人赚最高的工资，有两个人赚次高的工资，答案是仅有2行记录（2个最高的）。

    ③对销售人员按照工资从高到低排序，给出前三行记录，如果一个部门少于三个人，则返回少于三个记录。

    在进一步的提问与澄清之后，大部分人认为是第一种情况，其余的人认为是第二种或者第三种情况，下面分析，如何使用这个分析函数，来回答三种情况中的任意一种，并且将他们与没有分析函数的时候进行对比。

    对于这些例子，在在此使用SCOTT.EMP表，将来回答的第一个问题是：“给出各部门销售代表工资最高的前三名的销售人员的集合”。

SELECT *

  FROM (SELECT DEPTNO,

               ENAME,

               SAL,

               DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) DR

          FROM EMP)

 WHERE DR <= 3

 ORDER BY DEPTNO, SAL DESC 

查询出来的结果如下：

   在此处，DENSE_RANK()函数用于获得工资最高的前3名。将密集序列分配给工资列，并对它按照降序排序。回想一下前面的情况，密集序列没有跳跃号，并给有同样值的那些行分配同样的号。因而，在内部视图建立结果集之后，可以仅仅选择带有密集序列的3行或者更少的行，这就按照部门给出了最高工资的前3名的每一个人。若想要使用RANK，并遇到有相同值的情况，看一下会发生什么。

SELECT DEPTNO,

       ENAME,

       SAL,

       DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) DR,

       RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) R

  FROM EMP

查询结果如下：

  如果使用了RANK，则将会在结果集中丢掉ADMAMS（因为他排在第四名），但是ADAMS是部门20中转区工资最高的前三名之一，因而他也是属于结果集的。在这种情况下，使用RANK而不是DENSE_RANK将不符合特殊的查询。

  最后，使用内部视图，并对 密集序列的哪一列命名为DR，这是因为在WHERE或者HAVING子句中不能够直接使用分析函数，因而不得不计算该视图，然后筛选需要保留的行。在很多例子中，使用带谓语的内部视图是一种非常常见的操作。

  现在对于问题“给出各部门工资最高的（最多为）3个人”：

select *

from ( select deptno,ename,sal,

         count(*) over( partition by deptno

                        order by sal desc

                        range unbounded preceding)

         cnt from emp)

where cnt<=3

order by deptno,sal desc

/

查询的结果如下：

  这里有一点点小技巧。此处正在做的是在窗口中计算当前记录前的所有记录，并对他们按照工资进行排序。RANGE UNBOUNDED PRECEDING是默认的范围，它产生一个窗口，该窗口包括所有工资大于等于我们工资的那些记录（由于是按降序排列的）。通过计算赚与我们同样或者更多工资的人，可以仅检索这些行，以便赚同样多或者更多的工资的人的数目少于或者等于3）。注意，对于部门20来说，SCOTT和FORD的计数都为2,在那个部门中他们是工资最高的前两个,因而他们在同一个窗口,注意该查询中微妙的差异是让人感兴趣的:

select *

from (select deptno,ename,sal,

      count(*) over(partition by deptno

                    order by sal desc,ename

                    range unbounded preceding)

      cnt from emp)

where cnt<=3

order by deptno,sal desc

/

查询的结果如下:

注意,如何添加ORDER BY 函数将会影响到窗口,以前FORD和SCOTT的计数都是2,那是因为建立该窗口的时候仅仅使用工资那一列,更特殊的窗口是改变COUNT的结果.此处仅指出,根据ORDER BY 与RANGE计算窗口函数.岁划分按照工资排序,当SCOTT为当前行的时候,FORD在SCOTT之前;当FORD为当前行的时候,SCOTT在FORD之前。只有按照SAL与ENAME列进行排序的时候，SCOTT和FORD记录才有任何一种排序。

  为了了解使用计数的这个方法允许返回3个或者更少的记录，可修改数据来生成它，工资在前面的人超过3个。

SQL> update emp set sal=99 where deptno =30;

查询的语句如下：

select *

  from (select deptno,ename,sal,

        count(*) over(partition by deptno

                      order by sal desc

                      range unbounded preceding)

        cnt from emp)

where cnt<=3

order by deptno,sal desc

/

查询的结果如下：

这个时候，我们发现在结果集中，部门30中的数据都不再出现在报表中，因为那个部门中的全部的6个人赚取同样的工资，对他们所有的人来说，CNT的字段都为6,该数字不小于也不等于3，因此没有在报表中出现。

现在，对于最后一个问题：“对销售人员按照工资从大到小进行排序，给出前三行的记录”。使用ROW_NUMBER()是非常容易完成的：

select *

from (select deptno,ename,sal,

        row_number() over(partition by deptno

                          order by sal desc)

      rn from emp)

where rn<=3

/

查询出来的结果如下：

  这个查询在工资列上对每个分区按照降序进行排序，然后给每一行分配一个连续的行号。在做完这个之后，使用WHERE子句或得每个分区中的前三行。在下面的例子中，使用这个同样的概念将行转变为列。然后，在此需要注意的是，对于DEPTNO=30得到的行是随机的，请回忆一下，对部门30进行修改，一遍所有的6个雇员的工资都变成99，可以通过ORDER BY控制返回哪三个记录。

  例如，全部3个人赚同样数目的工资的时候，使用ORDER SAL DESC,ENAME语句，按雇员名排序的时候，可以得到三个工资最高的。

  利用上述方法和ROW_NUMBER，能够从一组数据中获得任意的数据片，这是非常令人感兴趣的。在没有国籍（stateless）的环境中，这是非常有用的，再次可以通过一些数据指定页数，例如，如果一次要显示EMP表按照ENAME排序的五行记录，可以使用类似于下面的查询：

select ename,hiredate,sal

from (select ename,hiredate,sal,

        row_number() over(order by ename)

      rn from emp)

where rn between 5 and 10

order by rn

/

查询的结果如下：

  此处对于性能的比较部分省略。

  分析函数执行的查询比没有使用分析函数的查询超过好多倍。

  在各种情况中，这些分析函数不仅使写复杂查询的工作变得比较容易，而且在查询的运行的时候他们的性能有了实质性的提高，这是非常清楚的。它们让大家做一些在SQL中由于相关的成本而没有考虑做的事情。

Pivot（行列转换）查询

  Pivot查询是这个样子的，当您想要取出一些如下的数据的时候：

   C1    C2    C3

  a1    b1    x1

  a1    b1    x2

  a1    b1    x3

并且想按照如下的格式显示：

C1  C2    C3(1)   C3(2)    C3(3)

a1  b1    x1      x2       x3

这样子，就将行变成了列。例如在一个部门中取出工资项，并且使他们变成列，则输出结果如下：

而不是这个样子：

对于行列转换要介绍两个例子。头一个例子是前面问题的另外一种实现方式，第二个例子来说明如何将任何结果集以一般的形式进行行列转换，并且给出一个模板完成这个工作。

  在第一个例子中，想要显示的每个部门中最高的前3名作为列，该查询只需要每个部门返回一行，改行有4列：DEPTNO、该部门工资最高的雇员名、工资次高的雇员名等等，使用这个新的功能来完成上述的任务几乎是非常容易的（而在这之前，要完成这个功能实际上是几乎不可能的）。

select deptno,

    max(decode(rn,1,ename,null)) hightest_paid,

    max(decode(rn,2,ename,null)) second_paid,

    max(decode(rn,3,ename,null)) third_paid

from 

(select deptno,ename,

 row_number() over(partition by deptno order by sal desc nulls last) rn 

from emp)

where rn<=3

group by deptno

/

  这个仅仅是创建内部的结果集，此集合按照工资顺序分配给部门雇员一个部门号码，外部查询解码的时候仅仅保留序列中的1,2,3行，并且将这些列分配给恰当的列。GOURP BY 消除了多余的行，并且将失败的结果保留下来了，如果大家能够看到没有GROUP BY 和MAX的结果集，就非常容易作者的意思了。

  如果有一个带有C1，C2列的表T，想要得到如下的结果：

  C1  C2(1)  C2(2)  ... C2(N)

  此处C1列将成为行（沿着页码往下），而C2列将转换为列（沿着页码往右），C2的值是要变成列而不是行，从而可以生成一个查询表单：

  SELECT C1

    MAX(DECODE(RN,1，C2，NULL) C2_1，

    MAX(DECODE(RN,2，C2，NULL) C2_2,

    ...

    MAX(DECODE(RN,N,CN,NULL)C2_N

  FROM(SELECT C1，C2

       ROW_NUMBER() OVER(PARTITION BY C1 ORDER BY <SOMETHING>) RN

       FROM T

       <SOME PREDICATE>)

  GROUP BY C1

  在上面的例子中C1只是DEPTNO，C2是ENAME，由于按照SAL DESC进行排序，检索到的前面3列是那个部门最高的三名雇员（请记住，如果有4个人赚的工资排在前三位，那么就会丢掉一个人的信息了）。

  第二个例子是一个更一般的“想要对结果集进行行列转换”的问题，此处不是让一个单一的列C1固定，而是让单一的列C2进行行列转换。看更加一般的情况，C1为列的集合，C2也是，在进行行转换的时候，与上面要根据JOB和DEPTNO报告雇员以及其工资的目的非常相似，该报表需要让雇员横着穿过页面作为例子，而不是沿着页面数着下去，工资也一样，另外雇员需要按照工资顺序从左到右排列，步骤为：

SQL> r

  1* select max(count(1)) from emp group by deptno,job

查询的结果如下：

它表示可以列出的数目，现在可以产生查询：

select deptno,job,

max(decode(rn,1,ename,null)) ename_1,

max(decode(rn,1,sal,null)) sal_1,

max(decode(rn,2,ename,null)) ename_2,

max(decode(rn,2,sal,null)) sal_2,

max(decode(rn,3,ename,null)) ename_3,

max(decode(rn,3,sal,null)) sal_3,

max(decode(rn,4,ename,null)) ename_4,

max(decode(rn,5,sal,null)) sal_4

from

(

select deptno,job,ename,sal,

  row_number() over(partition by deptno,job order by sal,ename) rn

from emp

)

group by deptno,job

/

产生的查询结果如下：

行列转换的包的实现包的定义：

CREATE OR REPLACE PACKAGE my_pkg

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;

在此,必须要输入p_max_cols或者p_max_cols_query的值,SQL需要知道查询中列的数目,该参数将建立一个有适当列数的查询,此处需要递送的值是查询的输出结果:

SQL> select max(count(1)) from emp group by deptno,job;

里面的p_query函数仅仅是使数据结合在一起的查询,使用以前查询的例子,该查询为:

select deptno,job,ename,sal,

  row_number() over(partition by deptno,job order by sal,ename) rn

from emp

下面两个是输入列名数组，P_ANCHOR是告诉我什么列继续作为列，而P_PIVOT则说明了哪些将作为记录的列。在上面的例子中，P_ANCHOR=('deptno','job')和P_PIVOT=('ename','sal'),所有的调用如下所示:

variable x refcursor

set autoprint on

begin

my_pkg.pivot(

p_max_cols_query=>'select max(count(1)) from emp group by deptno,job',

p_query=>'select deptno,job,ename,sal,

  row_number() over(partition by deptno,job order by sal,ename) rn

from emp',

p_anchor=>my_pkg.array('deptno','job'),

p_pivot=>my_pkg.array('ename','sal'),

p_cursor=>:x

);

end;

/

该包体的实现如下：

CREATE OR REPLACE PACKAGE BODY my_pkg

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

          l_max_cols := p_max_cols;

       ELSIF (p_max_cols_query 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;

      --构造查询SQL

      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';

      DBMS_OUTPUT.PUT_LINE(''||'==>'||l_query);

      OPEN p_cursor FOR l_query;

      EXECUTE IMMEDIATE 'alter session set cursor_sharing=exact';

    END;

END;

  上面的转换函数仅仅做了一些字符串的处理，以便重新写该查询，并且动态的打开REFCURSOR。该查询有一个带常数的谓词，为了方便约束变量，可以设置游标，然后后退，进行查询分析。现在您有了一个充分分析的查询了。

访问当前行周围的行

  人们常常想要访问的数据不仅来自于当前行，还有当前行之前和之后的行。例如，假设您需要一个报表，按照部门显示所有的雇员、他们的雇用日期、最后一次雇佣之前多少天、下一次雇用之后多少天。直接使用SQL的话，这个查询就如同噩梦一般，很难写，虽然可以完成，但是非常困难，不仅仅如此，而且其参数很明显又是有问题的。使用分析函数，可以很好的解决这个问题：

select deptno,ename,hiredate,

  lag(hiredate,1,null) over(partition by deptno order by hiredate,ename)last_hire,

  hiredate-lag(hiredate,1,null) over(partition by deptno order by hiredate,ename)days_last,

  lead(hiredate,1,null) over(partition by deptno order by hiredate,ename) next_hire,

  lead(hiredate,1,null) over(partition by deptno order by hiredate,ename)-hiredate days_next

from emp

order by deptno,hiredate

/

查询结果如下：

  LEAD和LAG例程可以看做是一种对被分区的组上加上索引的方法。使用这些函数，可以访问任何个别的行。现在介绍关于LAG和LEAD函数的详细信息，这些函数一共有三个参数：

LAG(ARG1，ARG2，ARG3)

1）ARG1是从其他行返回的表达式

2）ARG2是希望检索的当前行分区的偏移量，是一个正整数偏移量。在LAG的例子中，它是一个往回检索以前行的索引；在LEAD的例子中，它是一个往后检索即将到来的行的索引，该变量的默认值为1。

3）在ARG2表示的索引超出了窗口的范围的时候，ARG3就是默认的返回值。例如，每个分区的第一行都没有前一行，因而哪一行的LAG(...)就没有定义。您可以允许它默认范围NULL或者提供一个值。应该注意的是，那些窗口并没有使用LAG以及LEAD，您可能仅仅使用PARTITION BY 和ORDER BY 而没有使用ROWS 和RANGE。

  因而在例子中：

hiredate-lag(hiredate,1,null) over(partition by deptno order by hiredate,ename)days_last,

  通过将1传递给第二个参数（若没有前一行记录，则默认返回NULL），使用LAG来查找当前记录前面的记录，利用DEPTNO来进行分区后，则每个部门实现的时候与其他部门没有关系，通过对HIREDATE的排序，LAG(HIREDATE,1，NULL)返回小于当前记录的最大的HIREADATE。