Oracle常用分析函数教程
一、概述
1、分析函数是什么？
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，是Oracle专门用于解决复杂报表统计需求的功能强大的函数，它可以在数据中进行分组然后计算基于组的某种统计值，并且每一组的每一行都可以返回一个统计值。
2、分析函数和聚合函数的不同之处是什么？
普通的聚合函数用group by分组，每个分组返回一个统计值，而分析函数采用partition by分组，并且每组每行都可以返回一个统计值。
3、分析函数的形式
分析函数带有一个开窗函数over()，包含三个分析子句:分组(partition by), 排序(order by), 窗口(rows) ，他们的使用形式如下：over(partition by xxx order by yyy rows between zzz)。
二、理解over()函数
1、两个order by的执行时机
分析函数（以及与其配合的开窗函数over（））是在整个sql查询结束后(sql语句中的order by的执行比较特殊)再进行的操作, 也就是说sql语句中的order by也会影响分析函数的执行结果：
（1）两者一致：如果sql语句中的order by与分析函数的开窗函数over（）分析时要求的排序一致，即sql语句中的order by子句里的内容和开窗函数over（）中的order by子句里的内容一样，那么sql语句中的排序将先执行,分析函数在分析时就不必再排序；
（2）两者不一致：如果sql语句中的order by与分析函数配合的开窗函数over（）分析时要求的排序不一致，即sql语句中的order by子句里的内容和开窗函数over（）中的order by子句里的内容不一样，那么sql语句中的排序将最后在分析函数分析结束后执行排序。
2、开窗函数over（）中的分组/排序/窗口
开窗函数over（）包含三个分析子句：分组子句(partition by)， 排序子句(order by)， 窗口子句(rows)
窗口就是分析函数分析时要处理的数据范围，就拿sum来说,它是sum窗口中的记录而不是整个分组中的记录，因此我们在想得到某个栏位的累计值时，我们需要把窗口指定到该分组中的第一行数据到当前行, 如果你指定该窗口从该分组中的第一行到最后一行,那么该组中的每一个sum值都会一样，即整个组的总和。
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化。
窗口子句在这里只说rows方式的窗口，range方式和滑动窗口也不提。
窗口子句中我们经常用到指定第一行，当前行，最后一行这样的三个属性：
第一行是 unbounded preceding
当前行是 current row
最后一行是 unbounded following
注意：
1、当开窗函数over（）出现分组(partition by)子句时，unbounded preceding即第一行是指表中一个分组里的第一行， unbounded following即最后一行是指表中一个分组里的最后一行；
2、当开窗函数over（）省略了分组(partition by)子句时， unbounded preceding即第一行是指表中的第一行， unbounded following即最后一行是指表中的最后一行。
3、窗口子句不能单独出现，必须有order by子句时才能出现。
例如:
last\_value(sal) over( partition by deptno
order by sal
rows between unbounded preceding and unbounded following)
以上示例指定窗口为整个分组。
当出现order by子句的时候，不一定要有窗口子句，但效果会很不一样，此时的窗口默认是当前组的第一行到当前行！
4、如果省略分组,则把全部记录当成一个组。
（1）如果存在order by则默认窗口是unbounded preceding and current row --当前组的第一行到当前行
（2）如果省略order by则窗口默认为unbounded preceding and unbounded following --整个组
而无论是否省略分组子句，如下结论都是成立的：
（1）窗口子句不能单独出现，必须有order by子句时才能出现。
（2）当省略窗口子句时:
a) 如果存在order by则默认的窗口是unbounded preceding and current row --当前组的第一行到当前行，即在当前组中，第一行到当前行
b) 如果同时省略order by则默认的窗口是unbounded preceding and unbounded following --整个组
所以，lag(sal) over(order by sal) 解释over（order by salary）表示意义如下：
首先，我们要知道由于省略分组子句，所以当前组的范围为整个表的数据行，然后，在当前组（此时为整个表的数据行）这个范围里执行排序（即order by salary），最后，我们知道分析函数lag(sal)在当前组（此时为整个表的数据行）这个范围里的窗口范围为当前组的第一行到当前行，即分析函数lag(sal)在这个窗口范围执行。
三、常见分析函数详解
1、first\_value()与last\_value()：求最值对应的其他属性
2、rank()，dense\_rank()与row\_number()：求排序
rank，dense\_rank，row\_number函数为每条记录产生一个从1开始至n的自然数，n的值可能小于等于记录的总数。这3个函数的唯一区别在于当碰到相同数据时的排名策略。
①row\_number：
row\_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。
②dense\_rank：
dense\_rank函数返回一个唯一的值，当碰到相同数据时，此时所有相同数据的排名都是一样的。
③rank：
rank函数返回一个唯一的值，当碰到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。
**3、lag()与lead()：求之前或之后的第N行
lag和lead函数可以在一次查询中取出同一字段的前n行的数据和后n行的值。这种操作可以使用对相同表的表连接来实现，不过使用lag和lead有更高的效率。
lag(arg1,arg2,arg3)
第一个参数是列名，
第二个参数是偏移的offset，
第三个参数是超出记录窗口时的默认值。
lag的语法如下：
lead的语法如下：
lead 和lag 的语法类似。
lag(exp\_str,offset,defval) over()
exp\_str 指的是要做对比的字段。
offset 是exp\_str字段的偏移量，即 offset 为N ，指的是在表中从当前行位置向前数N行就是我们所要找的那一行了。
比如说，在表中，假设当前我们说的当前行在表中排在第四行，则offset 为3时表示的是我们所要找的数据行就是表中的第一行（即4-3=1）。
offset的默认值为1！
lag（）函数的返回值为在表中从当前行位置向前数N行的那一行上exp\_str字段的值。
当在表中从当前行位置向前数N行已经超出了表的范围时，lag（）函数将defval这个参数值作为函数的返回值。
比如说，在表中，假设当前我们说的当前行在表中排在第四行，则offset 为6时表示的是我们所要找的数据行就是表中的第-2行（即4-6=-2），这就表示我们所要找的数据行不在表中已经超出表的范围了，所以lag（）函数将defval这个参数值作为函数的返回值。
default 参数的默认值为空值null，即如果在lag（）函数中没有显式设置default 参数值时lag（）函数的返回值为空值null。
**4、rollup()与cube()：排列组合分组
（1）group by rollup(a, b, c)：
首先会对(a、b、c)进行group by，
然后再对(a、b)进行group by，
其后再对(a)进行group by，
最后对全表进行汇总操作。
（2）group by cube(a, b, c)：
则首先会对(a、b、c)进行group by，
然后依次是(a、b)，(a、c)，(a)，(b、c)，(b)，(c)，
最后对全表进行汇总操作。
5、max(),min(),sun()与avg()：求最值、总和与平均值