Oracle 高级函数：分析函数与窗口函数

Oracle 分析函数具有强大的功能。分析函数经常与窗口函数同时使用。

窗口函数总是为查询过程中的当前记录提供一个相关记录集，而且随着当前记录的推移，相应的记录集也会随之改变。

这非常类似与“滑动窗”的概念。分析函数的操作对象即为‘滑动窗’所指的记录的集合。

 

分析函数

排名函数： RANK(), DENSE_RANK(), ROW_NUMBER()， 一般与窗口函数一起使用。

例如测试表students 中的数据如下：

>> select * from students;

id   name age

1   金瑞 18

2   钟岩 19

3   王山 19

4   刘迪 17

5  终会  18

6 柳青  19

 

1） RANK() 返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名都是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

>> select name,age, rank() over(order by age) position from students.

name age position

刘迪 17      1

终会  18      2

金瑞  18      2

钟岩 19      4

王山 19      4

柳青  19      4

分析结果可以看出： RANK() 函数返回相同的排序序号：当遇到相同的数据时，此时所有相同数据的排名都是一样的，

同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

 

2） DENSE_RANK(), 返回一个唯一的值，除非遇到相同数据时，此时所有的数据排名都是一样的。

>> select name,age, dense_rank() over(order by age) position from students

name age position

刘迪 17      1

终会  18      2

金瑞  18      2

钟岩 19      3

王山 19      3

柳青  19      3

 

3） ROW_NUMBER(), 回一个唯一的值，当遇到相同数据时，排名按照记录集中记录的顺序依次递增。

 

>>select name,age, row_number() over(order by age) position from students

刘迪 17      1

终会  18      2

金瑞  18      3

钟岩 19      4

王山 19      5

柳青  19      6

 

4) PERCENT_RANK()函数

PERCENT_RANK 函数用于计算分区当中每个数值相对于同分区下其他数据行的百分比排名， 它的工作方式与RANK()相同， 不过该函数的返回值不是

一个整数形式的排名信息，而是给定值在排名当中的百分比信息。

窗口函数： OVER(), 函数针对某一个字段开窗。对于窗口函数，利用PARTITION BY 关键字进行分区。

 >>  select  t.* dense_rank() over(partition by department order by salary) position

       from salary t

      order by employee_id ;

    分析： over(partition by department order by salary) 为当前记录获得分区窗口，分区列为department;

    当前记录所在的分区为当前窗口；order by salary 用于将窗口中的记录按照salary 进行排序；

    dense_rank() 则获得当前记录在分区窗口中经过排序之后的位置。