SQL Server NTILE() 視窗函數實作：取得各群組前 10% 資料

SQL Server 2005 引入的 NTILE() 視窗函數可將排序後的資料集平均分成 N 個桶數，搭配 PARTITION BY 即可輕鬆實現「各群組前 N%」的查詢需求。本文以取得各資料表前 10% 欄位為例示範。

語法結構

NTILE(integer_expression) OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY order_expression [ASC | DESC], ...
)

• integer_expression：桶數 (如 10 代表十分位數、100 代表百分位數)
• 回傳值：1 到 N 的整數，代表該列落在哪個桶

範例：取得各資料表 (id) 前 10% 的欄位 (colid)

SELECT id, name, colid, rn
FROM (
    SELECT *,
           rn = NTILE(10) OVER (PARTITION BY id ORDER BY colorder)
    FROM syscolumns
) t
WHERE rn = 1

執行邏輯解析

1. PARTITION BY id：依資料表 ID 分組
2. ORDER BY colorder：組內依欄位順序排序
3. NTILE(10)：將各組資料平均分成 10 桶，前 10% 落在桶號 1
4. 外層 WHERE rn = 1 過濾出第一桶資料

進階應用變化

取得前 20% (前兩桶)

WHERE rn IN (1, 2)

取得中位數區間 (第 5-6 桶，約 40%-60%)

WHERE rn BETWEEN 5 AND 6

搭配 ROW_NUMBER() 精確控制筆數

NTILE 為平均分配，資料筆數無法整除桶數時，前幾桶會多分 1 筆。若需精確「前 N 筆」或「前 N% 筆數」，改用 ROW_NUMBER() 搭配計算：

SELECT id, name, colid
FROM (
    SELECT *,
           rn = ROW_NUMBER() OVER (PARTITION BY id ORDER BY colorder),
           cnt = COUNT(*) OVER (PARTITION BY id)
    FROM syscolumns
) t
WHERE rn <= CAST(cnt * 0.1 AS INT)

效能考量

• NTILE() 需要排序，確保 PARTITION BY + ORDER BY 欄位有適當索引
• 大量資料分區查詢可考慮建立 分區索引 或 篩選索引
• 避免在未索引的大表上直接套用，先以 CTE/暫存表縮小範圍

適用版本

• SQL Server 2005+ (所有版本)
• Azure SQL Database / Managed Instance
• SQL Server on Linux