oracle over rank dense_rank row_number 等分析函数总结

用各种搜索，总算把分析函数总结完了，嗨～累死了。
可以先看第二部分的实例，我相信有SQL基础的应该没问题。具体的知识点在看看第一、三部分

一、概念
Oracle从8.1.6开始提供分析函数，分析函数用于计算基于组的某种聚合值，它和聚合函数的不同之处是对于每个组返回多行，
而聚合函数对于每个组只返回一行。

常用的分析函数如下所列:
row_number() over(partition by ... order by ...)
rank() over(partition by ... order by ...)
dense_rank() over(partition by ... order by ...)
count() over(partition by ... order by ...)
max() over(partition by ... order by ...)
min() over(partition by ... order by ...)
sum() over(partition by ... order by ...)
avg() over(partition by ... order by ...)
first_value() over(partition by ... order by ...)
last_value() over(partition by ... order by ...)
lag() over(partition by ... order by ...)
lead() over(partition by ... order by ...)

开窗函数的理解：
开窗函数指定了分析函数工作的数据窗口大小，这个数据窗口大小可能会随着行的变化而变化，举例如下：
over（order by salary） 按照salary排序进行累计，order by是个默认的开窗函数
over（partition by deptno）按照部门分区
over（order by salary range between 50 preceding and 150 following）
每行对应的数据窗口是之前行幅度值不超过50，之后行幅度值不超过150
over（order by salary rows between 50 preceding and 150 following）
每行对应的数据窗口是之前50行，之后150行
over（order by salary rows between unbounded preceding and unbounded following）
每行对应的数据窗口是从第一行到最后一行，等效：
over（order by salary range between unbounded preceding and unbounded following） 

二、具体的例子（不要畏惧的看，其实一点不难）： 
1）查询员工薪水并连续求和
select deptno,ename,sal,
sum(sal)over(order by ename) sum1, /*表示连续求和*/
sum(sal)over() sum2, /*相当于求和sum(sal)*/
100* round(sal/sum(sal)over(),4) "bal%"
from emp;
结果如下：
DEPTNO ENAME SAL SUM1 SUM2 bal%
---------- ---------- ---------- ---------- ---------- ----------
    30 ALLEN 1600 1600 25800 6.2
    30 BLAKE 2850 4450 25800 11.05
    10 CLARK 2695 7145 25800 10.45
    20 FORD 3000 10145 25800 11.63
    30 JAMES 950 11095 25800 3.68
    20 JONES 2975 14070 25800 11.53
    10 KING 5500 19570 25800 21.32
    30 MARTIN 1250 20820 25800 4.84
    10 MILLER 1430 22250 25800 5.54
    20 SMITH 800 23050 25800 3.1
    30 TURNER 1500 24550 25800 5.81
    30 WARD 1250 25800 25800 4.84

2）如下：
select deptno,ename,sal,
sum(sal)over(partition by deptno order by ename) sum1,/*表示按部门号分氏，按姓名排序并连续求和*/
sum(sal)over(partition by deptno) sum2,/*表示部门分区，求和*/
sum(sal)over(partition by deptno order by sal) sum3,/*按部门分区，按薪水排序并连续求和*/
100* round(sal/sum(sal)over(),4) "bal%"
from emp;
结果如下：
DEPTNO ENAME SAL SUM1 SUM2 SUM3 bal%
---------- ---------- ---------- ---------- ---------- ---------- ----------
    10 CLARK 2695 2695 9625 4125 10.45
    10 KING 5500 8195 9625 9625 21.32
    10 MILLER 1430 9625 9625 1430 5.54
    20 FORD 3000 3000 6775 6775 11.63
    20 JONES 2975 5975 6775 3775 11.53
    20 SMITH 800 6775 6775 800 3.1
    30 ALLEN 1600 1600 9400 6550 6.2
    30 BLAKE 2850 4450 9400 9400 11.05
    30 JAMES 950 5400 9400 950 3.68
    30 MARTIN 1250 6650 9400 3450 4.84
    30 TURNER 1500 8150 9400 4950 5.81
    30 WARD 1250 9400 9400 3450 4.84

3）如下：
select deptno,empno,sal,
sum(sal)over(partition by deptno) "deptSum",/*按部门分区，并求和*/
rank()over(partition by deptno order by sal desc nulls last) rank, /*按部门分区，按薪水排序并计算序号*/
dense_rank()over(partition by deptno order by sal desc nulls last) d_rank,
row_number()over(partition by deptno order by sal desc nulls last) row_rank
from emp;
结果如下：
DEPTNO EMPNO SAL deptSum RANK D_RANK ROW_RANK
---------- ---------- ---------- ---------- ---------- ---------- ----------
    10 7839 5500 9625 1 1 1
    10 7782 2695 9625 2 2 2
    10 7934 1430 9625 3 3 3
    20 7902 3000 6775 1 1 1
    20 7566 2975 6775 2 2 2
    20 7369 800 6775 3 3 3
    30 7698 2850 9400 1 1 1
    30 7499 1600 9400 2 2 2
    30 7844 1500 9400 3 3 3
    30 7521 1250 9400 4 4 4
    30 7654 1250 9400 4 4 5
    30 7900 950 9400 6 5 6

三、补遗
rang()：涵数主要用于排序，并给出序号。
dense_rank()：功能同rank()一样，区别在于，rank()对于排序并的数据给予相同序号，接下来的数据序号直接跳中跃，dense_rank()则不是。
row_number()：涵数则是按照顺序依次使用，相当于我们普通查询里的rownum值。
比如数据（不懂可以看看http://hi.baidu.com/%B5%C8%B4%FD%D6%A4%CA%E9/blog/item/12039e304d5faa14ebc4afbe.html我就是看的这个）：
1，2，2，4，5，6.。。。。这是rank()的形式
1，2，2，3，4，5，。。。。这是dense_rank()的形式
1，2，3，4，5，6.。。。。。这是row_number()涵数形式

其实从上面三个例子当中，不难看出over(partition by ... order by ...)的整体概念
partition by :按照指字的字段分区，如果没有则针对全体数据
order by :按照指定字段进行连续操作（如求和(sum),排序(rank()等)，如果没有指定，就相当于对指定分区集合内的数据进行整体sum操作