常用的序列窗口函数如下： ```sql row_number() -- 对所有数值输出不同的序号，序号唯一且连续，如：1、2、3、4、5 rank() -- 对相同数值输出相同序号，但下一个序号间断，如：1、1、3、3、5 dense_rank() -- 对相同的数据输出相同的序号，但下一个序号不间断，如：1、1、2、2、3 nlite(n) -- 将有序的数据集合平均分配到n个桶中，将桶号分配给每一行，根据桶号，选取前或后n分之几的数据 percent_rank -- (目前排名-1)/(总行数-1)，相对于一组值的百分比排名 ``` ```sql select ename, deptno, sal, row_number() over() as row_num, rank() over(partition by deptno order by sal) as rank, dense_rank() over(partition by deptno order by sal) as dense_rank, percent_rank() over(partition by deptno order by sal) as percent_rank, ntile(2) over(partition by deptno order by sal) as ntile from emp order by deptno, sal; +---------+---------+---------+----------+-------+-------------+---------------+--------+--+ | ename | deptno | sal | row_num | rank | dense_rank | percent_rank | ntile | +---------+---------+---------+----------+-------+-------------+---------------+--------+--+ | MILLER | 10 | 1300.0 | 1 | 1 | 1 | 0.0 | 1 | | CLARK | 10 | 2450.0 | 8 | 2 | 2 | 0.5 | 1 | | KING | 10 | 5000.0 | 6 | 3 | 3 | 1.0 | 2 | | SMITH | 20 | 800.0 | 14 | 1 | 1 | 0.0 | 1 | | ADAMS | 20 | 1100.0 | 4 | 2 | 2 | 0.25 | 1 | | JONES | 20 | 2975.0 | 11 | 3 | 3 | 0.5 | 1 | | SCOTT | 20 | 3000.0 | 7 | 4 | 4 | 0.75 | 2 | | FORD | 20 | 3000.0 | 2 | 4 | 4 | 0.75 | 2 | | JAMES | 30 | 950.0 | 3 | 1 | 1 | 0.0 | 1 | | MARTIN | 30 | 1250.0 | 10 | 2 | 2 | 0.2 | 1 | | WARD | 30 | 1250.0 | 12 | 2 | 2 | 0.2 | 1 | | TURNER | 30 | 1500.0 | 5 | 4 | 3 | 0.6 | 2 | | ALLEN | 30 | 1600.0 | 13 | 5 | 4 | 0.8 | 2 | | BLAKE | 30 | 2850.0 | 9 | 6 | 5 | 1.0 | 2 | +---------+---------+---------+----------+-------+-------------+---------------+--------+--+ -- 最常用的场景是分组排序求 TopN 问题 -- 演示示例：求每个部门工资前两高的员工 select * from (select ename, deptno, sal, dense_rank() over(partition by deptno order by sal desc) as rn from emp) t where t.rn<=2 order by t.deptno, t.rn; +----------+-----------+---------+-------+--+ | t.ename | t.deptno | t.sal | t.rn | +----------+-----------+---------+-------+--+ | KING | 10 | 5000.0 | 1 | | CLARK | 10 | 2450.0 | 2 | | FORD | 20 | 3000.0 | 1 | | SCOTT | 20 | 3000.0 | 1 | | JONES | 20 | 2975.0 | 2 | | BLAKE | 30 | 2850.0 | 1 | | ALLEN | 30 | 1600.0 | 2 | +----------+-----------+---------+-------+--+ ```