資料排序除了order by的方式外，還可以透過鎖定特定欄位給定順序性，再更進階將資料進行排序。今天要介紹好用的排序函數。

排序函數

此系列函數由Partition by與order by組成，Partition by用來將資料分組的，Order by決定分組資料的順序性，根據用途以下分三類說明。

1. 用於給定資料順序性
   [註]Row_Number照組內排序給出一個順序
   [註]DENSE_RANK同名給同順序&繼續數, RANK同名給同順序跳過一個名次數
   ROW_NUMBER() over (Partition by 欄位 Order by 欄位)
DENSE_RANK() over (Partition by 欄位 Order by 欄位)
Rank() over (Partition by 欄位 Order by 欄位)

2. 用於分頁, 總資料count數/n = page
   NTILE(n) OVER() -- 用於分頁, 總資料count數/n = page

-- 1 / 2 example
select ROW_NUMBER() over (Partition by dept Order by score desc) ID
,Rank() over (Partition by dept Order by score desc) ID2
,DENSE_RANK() over (Partition by dept Order by score desc) ID3
,NTILE(5) OVER(Order by dept,score desc) PAGE  --總資料數/n=page
,A.*
from (
    select 'Amy' name, 'CSIE' dept, 50 score from dual
    union all
    select 'Oleve' name, 'Chemical' dept, 59 score from dual
    union all
    select 'Jake' name, 'CSIE' dept, 60 score from dual
    union all
    select 'Freddy' name, 'Chemical' dept, 80 score from dual
    union all
    select 'Kamy' name, 'CSIE' dept, 80 score from dual
    union all
    select 'Jerry' name, 'CSIE' dept, 60 score  from dual
    union all
    select 'Sherry' name, 'Chemical' dept, 60 score from dual
    union all
    select 'Ban' name, 'Chemical' dept, 85 score from dual
    union all
    select 'Seafood' name, 'Medicine' dept, 90 score from dual
    union all
    select 'Celine' name, 'Medicine' dept, 95 score  from dual
)A
;
--output
ROW_NUMBER  Rank  DENSE_RANK  NTILE    name       dept       score
1           1       1           1      Kamy       CSIE       80
2           2       2           1      Jerry      CSIE       60
3           2       2           2      Jake       CSIE       60
4           4       3           2      Amy        CSIE       50
1           1       1           3      Ban        Chemical   85
2           2       2           3      Freddy     Chemical   80
3           3       3           4      Sherry     Chemical   60
4           4       4           4      Oleve      Chemical   59
1           1       1           5      Celine     Medicine   95
2           2       2           5      Seafood    Medicine   90

3. 搭配聚合函數取得組內統計值
   FIRST_VALUE(欄位) over (Partition by 欄位 Order by 欄位)
LAST_VALUE(欄位) over (Partition by 欄位 Order by 欄位)
MAX(欄位) over (Partition by 欄位 Order by 欄位)
MIN(欄位) over (Partition by 欄位 Order by 欄位)
AVG(欄位) over (Partition by 欄位 Order by 欄位)

select FIRST_VALUE(score) over (Partition by dept Order by dept) FIRST_VALUE
,LAST_VALUE(score) over (Partition by dept Order by dept) LAST_VALUE
,MAX(score) over (Partition by dept Order by dept) MAX_V
,MIN(score) over (Partition by dept Order by dept) MIN_V
,AVG(score) over (Partition by dept Order by dept) AVG_V
,A.*
from (
    select 'Amy' name, 'CSIE' dept, 50 score from dual
    union all
    select 'Oleve' name, 'Chemical' dept, 59 score from dual
    union all
    select 'Jake' name, 'CSIE' dept, 60 score from dual
    union all
    select 'Freddy' name, 'Chemical' dept, 80 score from dual
    union all
    select 'Kamy' name, 'CSIE' dept, 80 score from dual
    union all
    select 'Jerry' name, 'CSIE' dept, 60 score  from dual
    union all
    select 'Sherry' name, 'Chemical' dept, 60 score from dual
    union all
    select 'Ban' name, 'Chemical' dept, 85 score from dual
    union all
    select 'Seafood' name, 'Medicine' dept, 90 score from dual
    union all
    select 'Celine' name, 'Medicine' dept, 95 score  from dual
)A
;
-- output
FIRST_VALUE	LAST_VALUE	MAX	MIN	AVG | NAME	DEPT	SCORE
50 80 80 50 62.5 Amy CSIE 50
50 80 80 50 62.5 Jerry CSIE 60
50 80 80 50 62.5 Jake CSIE 60
50 80 80 50 62.5 Kamy CSIE 80
59 85 85 59 71 Oleve Chemical 59
59 85 85 59 71 Sherry Chemical 60
59 85 85 59 71 Freddy Chemical 80
59 85 85 59 71 Ban Chemical 85
90 95 95 90 92.5 Seafood Medicine 90
90 95 95 90 92.5 Celine Medicine 95

4. 其餘函數也可套入公式使用: LAG顯示前n行，LEAD顯示後n行
   LAG(欄位) over (Partition by 欄位 Order by 欄位)
LEAD(欄位) over (Partition by 欄位 Order by 欄位)

select LAG(score) over (Partition by dept Order by dept,score) LAG
,LEAD(score) over (Partition by dept Order by dept,score) LEAD
,A.*
from (
    select 'Amy' name, 'CSIE' dept, 50 score from dual
    union all
    select 'Oleve' name, 'Chemical' dept, 59 score from dual
    union all
    select 'Jake' name, 'CSIE' dept, 60 score from dual
    union all
    select 'Freddy' name, 'Chemical' dept, 80 score from dual
    union all
    select 'Kamy' name, 'CSIE' dept, 80 score from dual
    union all
    select 'Jerry' name, 'CSIE' dept, 60 score  from dual
    union all
    select 'Sherry' name, 'Chemical' dept, 60 score from dual
    union all
    select 'Ban' name, 'Chemical' dept, 85 score from dual
    union all
    select 'Seafood' name, 'Medicine' dept, 90 score from dual
    union all
    select 'Celine' name, 'Medicine' dept, 95 score  from dual
)A
;
-- output
NULL 60 Amy CSIE 50
50 60 Jerry CSIE 60
60 80 Jake CSIE 60
60 NULL Kamy CSIE 80
NULL 60 Oleve Chemical 59
59 80 Sherry Chemical 60
60 85 Freddy Chemical 80
80 NULL Ban Chemical 85
NULL 95 Seafood Medicine 90
90 NULL Celine Medicine 95

Reference

• 十分鐘內快速上手與使用 Window function｜SQL 教學
• BEGTUT.COM
• Sql 四大排名函數（ROW_NUMBER、RANK、DENSE_RANK、NTILE）簡介
• [SQL] Rank、Dense_Rank、Row_Number用法與範例
• SQL排序之 row_number, rank(), dense_rank()区别