萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 2008 排序視窗函數完整指南:ROW_NUMBER, RANK, DENSE_RANK, NTILE

概覽

SQL Server 2005 引入 ANSI SQL:2003 標準的排序視窗函數,允許在不分組的情況下為結果集分配排名。四大函數差異在於「相同值如何編號」與「編號是否連續」。

測試資料準備


CREATE TABLE test (iname NVARCHAR(10), iage INT);
INSERT INTO test VALUES ('A', 20), ('B', 20), ('C', 18), ('D', 22), ('E', 18);

1. ROW_NUMBER() - 唯一連續編號

依 ORDER BY 排序,每列分配 唯一、連續 的整數 (1,2,3...),相同值也給不同號。


SELECT 
    ROW_NUMBER() OVER (ORDER BY iname DESC) AS rowid,
    iname
FROM test;
ROW_NUMBER 結果

結果:A=1, B=2, C=3, D=4, E=5 (依 iname DESC 排序)

2. RANK() - 相同值同排名,編號不連續 (跳號)

相同排序值給相同排名,下一名次 跳過被佔用的號碼 (1,2,2,4...)。


SELECT DISTINCT
    RANK() OVER (ORDER BY iname DESC) AS rowid,
    iname, iage
FROM test;
RANK 結果

結果:若 iname 相同則同 rank,下一名次跳號。

3. DENSE_RANK() - 相同值同排名,編號連續 (不跳號)

相同值同排名,但下一名次 緊接著下一個整數 (1,2,2,3...)。


SELECT DISTINCT
    DENSE_RANK() OVER (ORDER BY iname DESC) AS rowid,
    iname
FROM test;
DENSE_RANK 結果

4. NTILE(n) - 分桶 (分組編號)

依排序將結果集平均分成 n 個桶,每列標示所屬桶號 (1~n)。


SELECT DISTINCT
    NTILE(5) OVER (ORDER BY iname DESC) AS bucket,
    iname, iage
FROM test
ORDER BY bucket;
NTILE 結果

結果:5 筆資料分 5 桶,每桶 1 筆,bucket 為 1~5。

差異對照表

函數相同值編號編號連續性典型用途
ROW_NUMBER不同連續分頁、去重保留第一筆、唯一序號
RANK相同不連續 (跳號)競賽排名 (並列第一,第三名無第二)
DENSE_RANK相同連續分級統計、薪資等級
NTILE(n)視分布而定連續桶號四分位數、十分位數、資料分桶分析

進階用法:PARTITION BY 分組排名

加入 PARTITION BY 可在各分區內獨立排名:


-- 各部門內依薪資排名
SELECT 
    dept, name, salary,
    ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) AS rn_dept,
    RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rk_dept
FROM employee;

效能提示

  • 排序視窗函數需要 排序運算子,大量資料請確保 ORDER BY 欄位有索引支援。
  • ROW_NUMBER 常用於 高效分頁 (配合 CTE/派生表):
    
       WITH cte AS (
           SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM big_table
       )
       SELECT * FROM cte WHERE rn BETWEEN 21 AND 30;  -- 第 3 頁,每頁 10 筆
       

總結

四大排序函數各有適用場景:ROW_NUMBER 為最通用的唯一序號;RANK/DENSE_RANK 用於語意排名;NTILE 用於統計分桶。善用 PARTITION BY 實現分組內排名,是報表與分析查詢的利器。

copyright © 萬盛學電腦網 all rights reserved