常用的序列窗口函數如下： ```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 | +----------+-----------+---------+-------+--+ ```