PARTITION BY 是視窗函數的核心子句,將結果集切分為多個分區,視窗函數在每個分區內獨立計算。搭配 ROW_NUMBER() 可實現分組排序編號、Top-N 查詢、去重、分頁等高階需求。本文從原理到 8 大實戰案例完整講解。
OVER (PARTITION BY col1, col2 ORDER BY col3):先按 PARTITION BY 分組,組內按 ORDER BY 排序,再套用視窗函數。GROUP BY 差異:GROUP BY 收合為單行彙總;PARTITION BY 保留明細行,並可輸出分組內的排序號、累計值等。CREATE TABLE TESTDB (A VARCHAR(8), B VARCHAR(8));
INSERT INTO TESTDB VALUES
('A1','B1'),('A1','B2'),('A1','B3'),
('A2','B4'),('A2','B5'),('A2','B6'),
('A3','B7'),('A3','B3'),('A3','B4');
-- 有分區:每組 A 內部編號 1,2,3 SELECT *, ROW_NUMBER() OVER(PARTITION BY A ORDER BY A DESC) AS Num FROM TESTDB; -- 無分區:全表連續編號 1~9 SELECT *, ROW_NUMBER() OVER(ORDER BY A DESC) AS Num FROM TESTDB;
SELECT Email, CustomerID, ROW_NUMBER() OVER(ORDER BY Psd) AS Rn FROM QT_Customer;
SELECT DID, CustomerID, TotalPrice, ROW_NUMBER() OVER(ORDER BY TotalPrice) AS Rn FROM OP_Order;
SELECT ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY TotalPrice) AS Rn,
CustomerID, TotalPrice, DID FROM OP_Order;
WITH t AS ( SELECT ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY TotalPrice) AS Rn, CustomerID FROM OP_Order ) SELECT MAX(Rn) AS OrderCount, CustomerID FROM t GROUP BY CustomerID;
WITH t AS ( SELECT ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY InsDT) AS Rn, * FROM OP_Order ) SELECT * FROM t WHERE TotalPrice IN (SELECT MIN(TotalPrice) FROM t GROUP BY CustomerID);
WITH t AS ( SELECT ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY InsDT) AS Rn, * FROM OP_Order ) SELECT * FROM t WHERE Rn = 1;
WITH t AS ( SELECT ROW_NUMBER() OVER(ORDER BY CreateDate DESC) AS Rn, * FROM Products ) SELECT * FROM t WHERE Rn BETWEEN 21 AND 30; -- 第 3 頁,頁大小 10
SELECT ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY InsDT) AS Rn, * FROM OP_Order WHERE InsDT > '2011-07-22';
注意:WHERE → GROUP BY → ORDER BY → OVER()。視窗函數在 WHERE 之後執行,故編號是針對已過濾的資料集。
RANK()、DENSE_RANK():處理並列名次。NTILE(n):將分區平均切成 n 桶。FIRST_VALUE/LAST_VALUE:取得分區首/尾值。SUM() OVER(PARTITION BY ... ORDER BY ... ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW):累計總額。PARTITION BY + ROW_NUMBER() 是解決「分組內排序編號」問題的瑞士刀。掌握其執行順序、搭配 CTE 進行二次篩選,即可優雅處理 Top-N、去重、分頁、歷史快照等高頻需求。