分析函数

概述

Analytic functions compute an aggregate value based on a group of rows。分析函数主要是用于解决诸如“计算动态累计”、“找出一个组内的%”、“前N个查询”、“计算正在流动的平均数”此类的问题和其他很多方面的问题，即它解决很多统计方面的信息。

分析函数和聚集函数类似，但是不同于聚集函数的是，它们对它们基于的组数据返回多行聚集结果，而聚集函数就返回一个单一结果。如情况一，返回工号在100到1000之间的员工的工资之和，这种是用聚集函数处理；而如情况二，以工号升序排列，返回工号在100到1000之间的员工的工资和在这个员工之前的100个员工工资之和，这种用分析函数处理。很显然，聚集函数处理情况一，它返回得是一个结果，即一个总和；而分析函数处理的情况二，它返回很多行，返回工号在100和1000之间的员工数目如9900个行，每行都做了个计算总和的操作。

很显然，分析函数所能做的这些事情，在功能上，都可以利用plsql来完成，但是分析函数的出现使一个简单纯sql语句就能解决此类问题，而且一般在性能上都有较大提高。

工作机制

前面已经说到，分析函数是基于一组记录来计算聚集值的。这里，它所基于的这一组记录，我们把它称之为“窗口”(window)。对于每一行记录，都有一个窗口，用它来指定分析函数执行聚集运算的记录集。

这里，需要重点理解一下“窗口”这个概念。对于每一行记录（"当前行，current row”），都有一个“滑动的窗口”（"sliding window”）来构成执行聚集运算的行集。这个滑动窗口的大小，我们是可以定义的，我们有两种方法来定义窗口大小，这两种方法分别是：

1、 基于范围的：RANGES of data values，specifies the window as a logical offset。就是根据当前行记录的逻辑行偏移来计算窗口。如在2007年8月1号入职的员工，求比他早入职100天的员工的月工资平均值。这里早入职100天就是一个逻辑偏移，因为比他早入职100天的员工可能只有1个，也有可能有100个，员工的数目（行总数，一个员工一行）。比2007年8月1号 （确定值）入职“逻辑”上早100天的入职员工。

2、 基于行数的：ROWS offset from the current row，specifies the window in physical units (rows)。就是根据当前行记录的物理行偏移来计算窗口。如在2007年8月1号入职的员工，求在他之前入职的100个员工的月平均工资。这里之前100个入职的员工就是一个物理行偏移，因为这里的员工数目很确定（行总数确定，即100）。

可见，逻辑行偏移还是物理行偏移，是就行记录总数而言的，行记录总数确定的为物理行偏移，不确定为逻辑行偏移；进一步发现，根据窗口定义，其实，行记录总数，就是这个窗口的大小。

除了ORDER BY 以外，分析函数是最后执行的，即它在WHERE/GROUP BY/HAVING以及所有的join之后执行，但是在ORDER BY之前，因此，分析函数只能出现在select或者order by子句中。

语法
Analytic-Function(<Argument>,<Argument>,...)
OVER (
<Query-Partition-Clause> -- 分区子句
<Order-By-Clause> -- 分区排序子句
<Windowing-Clause> -- 窗口子句
)
这里最重要的就是窗口子句，还有就是不是每个分析函数都支持分区排序子句和窗口子句的。下面来分别解释它里面的各个部分的内容。
Analytic-Function：分析函数的名称。
Argument：分析函数所带的参数。不同的分析函数所带的参数的数目是不一样，含义也是不一样的，因此这项要具体函数具体分析。但是，有一点是确定的，参数的数目最少为0即无，最多只能有3个。
另外，这里的参数必须是数字数据类型的或者是可以隐式转换成数字数据类型的。参数的数字数据类型可能不一样，但是oracle会按照数字数据类型的转换规则来最终将这三个数字参数转换成同一种数字数据类型，即将它们转换成最高数据优先级的。数据类型本身的数据优先级顺序为：BINARY_DOUBLE>BINARY_FLOAT>NUMBER。另外，oracle的数据类型的优先级为：时间类型>数字类型>字符类型。
OVER关键字甚至可以看做是分析函数的标志，分析函数本身不能相互嵌套。
Query-Partition-Clause：分区子句的语法是：PARTITION BY (exp1[,exp2,...expN])
分区子句是在逻辑上对前面返回的记录集，即经过FROM/WHERE/GROUP BY/HAVING/JOINS“筛选”后的结果集，按照exp进行分组。其实，这里就和SELECT中的GROUP BY子句的功能有些类似，就是按照某些列对结果集分组。因此在这里，单词“Partition”和“group”是同义词。The analytic functions are applied to each group independently, they are reset for each group。如果你省略分区子句，那么oracle就把前面返回的所有的记录当作一个group/Partition。
Order-By-Clause
    （分区）排序子句的语法是：

分区排序子句的作用是指定一个分区（Partition或者group）内，数据记录是如何排序的。你可以指定多个列来指定排序。该子句功能有点类似SELECT中的ORDER BY子句。
    这里有几个要注意的地方：
1、  从分区排序的语法可知，它必须接expr。
2、  The SIBLINGS keyword is not valid (it is relevant only in hierarchical queries). Position (position) and column aliases (c_alias) are also invalid. Otherwise this order_by_clause is the same as that used to order the overall query or subquery.
3、  NULLS FIRST/NULLS LAST指定是把NULL的行放在最前面还是最后。默认情况下，ASC的NULL是在最后，而DESC的NULL行是在最前面。
Windowing-Clause
The windowing clause gives us a way to define a sliding or anchored window of data, on which the analytic function will operate, within a group.
请看它的语法形式：

窗口的形式有两种，所谓滑动窗口（sliding window）：就是窗口的边界是随着分析函数操作的记录行的变化而动态变化。所谓固定窗口（抛锚窗口，anchored window）：就是窗口的边界始终是固定的。默认的窗口形式固定窗口，它开始于当前组（当前分区，Partition，group）的第一行，结束于当前行（current row）。

下面来解释一下窗口子句语法中的关键字。
BETWEEN...END：指定窗口的起始点和终止点。
UNBOUNDED PRECEDING：指定窗口的起始点是分区的第一行。
UNBOUNDED FOLLOWING：指定窗口的终止点是分区的最后一行。
value_expr PRECEDING/FOLLOWING：表示在当前行/当前值的“前面/后面”value_expr处。当你用物理行偏移ROWS方式开窗的时候，就是当前行；当用逻辑行偏移RANGE方式开窗的时候，就是当前值。

根据PRECEDING和FOLLOWING的含义，我们可以推导出以下结论：
1、  如果CURRENT ROW是窗口的起点，那么窗口的终止点就不能是value_expr PRECEDING；如果CURRENT ROW是窗口的终止点，那么窗口的起点就不能是value_expr FOLLOWING。
2、  如果窗口的起始点是value_expr FOLLOWING，那么窗口的终止点也必须是value_expr FOLLOWING；如果窗口的终止点是value_expr PRECEDING，那么窗口起始点也必须是value_expr PRECEDING。

当是使用物理行偏移ROWS方式开窗的时候：
1、  value_expr必须是一个常量或者表达式，且其值必须是正值。
2、  If value_expr is part of the start point, then it must evaluate to a row before the end point.

当是使用逻辑行偏移RANGE方式开窗的时候：
1、  value_expr必须是一个常量或者表达式，且其值必须是正值；或者是表示间隔的表达式。
2、  如果value_expr是应用于一个数字的计算，那么分区排序子句中的expr必须是数字或者日期类型。
3、  如果value_expr是应用于一个间隔值的计算，那么分区排序子句中的exp必须是个日期类型。
4、  对于RANGE BETWEEN UNBUNDED PRECEDING AND CURRENT ROW（即RANGE UNBUNDED PRECEDING)，或者对于RANGE BETWEEN CURRENT ROW AND UNBUNDED FOLLOWING（即RANGE UNBUNDED FOLLOWING），分区排序子句（ORDER BY）可以接根据多个exp来排序，否则只能有一个排序键。

常用分析函数

avg([distinct]...) over(...)：用于计算平均值。

count([distinct]...) over(...)：用于计算个数。
如果参数是“*”，那么它返回所有行的总数；如果是某个列，那么它返回这个列的值是NOT NULL的行的总数。有关distinct关键字，它的含义和avg中的一样。 

first_value(exp IGNORE NULLS) over(...)：返回窗口中的第一个值。
IGNORE NULLS表示如果第一行的exp是NULL，那么就忽略。

last_value(exp IGNORE NULLS) over(...)：返回窗口中的最后一个值。