[Oracle] 分析函数（1）- 语法

浏览数：19 / 时间：2015年06月12日

语法概述

Analytic-Function

Analytic_Clause之PARTITION子句

Analytic_Clause之ORDER BY子句

Analytic_Clause之WINDOWING子句

举例：

下面看一个例子简单过下语法：

例:
sum(sal) over (partition by deptno order by ename) new_alias

sum就是函数名

(sal)是分析函数的参数,每个函数有0~3个参数,参数可以是表达式,例如:sum(sal+comm)

over() 是开窗函数，这是开启分析函数的起点，对于既可作为聚集函数又可作为分析函数的函数，Oracle无法识别，必须用over来标识此函数为分析函数，此处不可省！

partition by deptno 是可选的分区子句,如果不存在任何分区子句,则全部的结果集可看作一个单一的大区
ORDER by则是窗口规则;
ROW |range BETWEEN...AND 是窗口范围(row就像通过自身位置的前后物理座位来找对应的位置，相对固定；而range是通过某种条件来找位置，需要计算方能确定相应位置，如长我3岁和小我两岁的人。)

关于窗口范围的取数方式有多种，看似非常复杂，其实不难，我们开始慢慢剖析：

关键位置就3个：1. 分组第一行Unbounded preceding 2. 当前行Current ROW 3.分组最后一行 Unbounded following

接下来，产生了最简单的三个取数范围：

分组第一行->当前行 rows [between] unbounded preceding [and current row]，
分组第一行->分组最后一行 rows between unbounded preceding and unbounded following，
当前行->分组最后一行 rows between current row and unbounded following

为什么课程分析函数会让人觉的非常复杂呢，我们来看看，这个窗口范围有两种取数方式，一个是根据物理位置，就是row关键字，一个是根据逻辑位置取数，就是range,这时刚才的取数范围从3个变成6个了，如下：

ROWS的分组第一行->当前行，分组第一行->分组最后一行，当前行->分组最后一行
RANGE的分组第一行->当前行，分组第一行->分组最后一行，当前行->分组最后一行

不过这样的场景都考虑周全了吗，还有没有漏考虑的,有啊，我们继续看看有哪些可能：

从分组第一行->当前行前n行 rows between unbounded preceding and n行 preceding
从分组第一行->当前行后n行 rows between unbounded preceding and n行 following

从当前行->当前行后n行 rows between current row and n行 following
从当前行前n行->当前行 rows between n行 preceding and CURRENT ROW

从当前行前n行->分组最后一行 rows between n行 preceding and unbounded following
从当前行后n行->分组最后一行 rows between n行 following and unbounded following
从当前行前n1行->到当前行前n2行 rows between n1行 preceding and n2行 preceding
从当前行后n1行->到当前行后n2行 rows between n1行 following and n2行 following

还有没有，有！

从当前行前n1行到当前行后n2行 rows BETWEEN n1行 preceding and n2行 following

还有没有比如从分组第一行后n行到当前行，从当前行到分组最后一行前n行啥的啊？
哦，那倒是没有了，对了，你脑子还不够迷糊啊，记这么多？？？

总算结束了，不过还有一个窗口开始于当前行，结束于当前行的，不过这无意义，要了干啥呢？所以，算算，至少有9个。
考虑到rows 和range两套人马，这下又多了18个了。加上前面的6个，就是24个了。不过后面多出来的这18个，用到的几率比前面6个要少不少。

对了，还有一件很重要的事要交代

不写between AND ,在有order BY 的情况下，就是分组第一行到当前行 BETWEEN unbounded preceding and current row
不写between AND ,在没有order BY 的情况下，就是分组第一行到分组最后一行； BETWEEN unbounded preceding and unbounded following