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()：求最值、总和与平均值