萬盛學電腦網

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

SQL Server PARTITION BY 與 ROW_NUMBER() 進階應用全解析

SQL Server PARTITION BY 與 ROW_NUMBER() 進階應用全解析

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');

1. PARTITION BY vs 無 PARTITION BY

-- 有分區:每組 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;

2. 實戰案例八則

案例 1:簡單編號

SELECT Email, CustomerID, ROW_NUMBER() OVER(ORDER BY Psd) AS Rn FROM QT_Customer;

案例 2:訂單按金額升序編號

SELECT DID, CustomerID, TotalPrice, ROW_NUMBER() OVER(ORDER BY TotalPrice) AS Rn FROM OP_Order;

案例 3:每位客戶訂單按金額升序編號(知曉每客下單次數)

SELECT ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY TotalPrice) AS Rn,
       CustomerID, TotalPrice, DID FROM OP_Order;

案例 4:統計每客總下單次數

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;

案例 5:找出每客最低消費訂單,並顯示該筆是第幾單

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);

案例 6:篩選每客第一筆訂單

WITH t AS (
  SELECT ROW_NUMBER() OVER(PARTITION BY CustomerID ORDER BY InsDT) AS Rn, *
  FROM OP_Order
)
SELECT * FROM t WHERE Rn = 1;

案例 7:分頁查詢

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

案例 8:執行順序陷阱

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、去重、分頁、歷史快照等高頻需求。

copyright © 萬盛學電腦網 all rights reserved