SQL窗口函数(OLAP函数)
由于关系数据库提供支持OLAP用途功能时间不长,并且还有一部分DBMS不支持这个新功能(比如MYSQL)所以是个较为难的知识点。
窗口函数用法:
<窗口函数> OVER ( [PARTITION BY <列清单> ]
ORDER BY <排序用列清单> )
*其中[ ]中的内容可以忽略
下面介绍三种用于进行排序的专用窗口函数:
例子:如果数据库中存在数据:1,2,1,3,1,4,5
1、RANK()
在计算排序时,若存在相同位次,会跳过之后的位次。
例如,有3条排在第1位时,排序为:1,1,1,4······
2、DENSE_RANK()
在计算排序时,若存在相同位次,不会跳过之后的位次。
例如,有3条排在第1位时,排序为:1,1,1,2······
3、ROW_NUMBER()
这个函数赋予唯一的连续位次。
例如,有3条排在第1位时,排序为:1,2,3,4······
例子:
CREATE TABLE `salaries` (
`emp_no` int(11) NOT NULL,
`salary` int(11) NOT NULL,
`from_date` date NOT NULL,
`to_date` date NOT NULL,
PRIMARY KEY (`emp_no`,`from_date`));
对所有员工的当前(to_date=‘9999-01-01’)薪水按照salary进行按照1至N的排名,相同salary并列且按照emp_no升序排列
使用:
select emp_no, salary,
dense_rank() over (order by salary desc) as rank
from salaries
where to_date='9999-01-01'
order by rank asc,emp_no asc;