用 PARTITION BY 來把資料分組

想像一下，你現在在一家很大的餐廳當服務生（或是 barista，如果你比較愛咖啡）。每天你都會算一下今天收到多少小費。但有個重點：餐廳有分區，你想知道每一區分別收到多少小費。PARTITION BY 就是 SQL 用來「把餐廳分區」的工具啦。

正式一點說，PARTITION BY 是在 window function 裡面用來把整個 table 的 row 分成不同 group（或叫「分區」）。每個 group 裡面，window function 都會重新跑一次。就像你在每個「分區」裡面各自用 function 一樣。

範例：怎麼運作的

假設我們有一個 sales table，裡面有銷售資料：

如果我們想算每個銷售員賺了多少錢，但要分區域來看，PARTITION BY 就是你要用的東西。

PARTITION BY 語法

語法超簡單：

window_function() OVER (PARTITION BY 欄位_或_欄位們)

• window_function() — 比如 SUM()、AVG()、ROW_NUMBER() 這些。
• PARTITION BY 欄位 — 指定要用哪個欄位來分組。
• OVER() — 這個 operator 跟 SQL 說：「在這個 window 裡面做某件事」。

範例：分組加總

來算一下每個區域的銷售總額：

SELECT
    region,
    salesperson,
    amount,
    SUM(amount) OVER (PARTITION BY region) AS total_sales_by_region
FROM sales;

結果會長這樣：

發生什麼事？SQL 會根據 region 欄位（North 跟 South）把 row 分組，然後對每個 group 分別用 SUM() 算總額。結果就是，group「North」裡的 row 都拿到一樣的總額，group「South」也是。

PARTITION BY 的實用例子

來看看 PARTITION BY 在現實世界可以怎麼用。

範例 1：在 group 裡面做排名

假設我們想要在每個區域裡，根據銷售額給銷售員排名。可以用 PARTITION BY 搭配 RANK()：

SELECT
    region,
    salesperson,
    amount,
    RANK() OVER (PARTITION BY region ORDER BY amount DESC) AS rank_in_region
FROM sales;

結果：

RANK() 會在每個 region group 裡面給排名，從 1 開始。注意，每個 group 的排名都是從 1 開始算。

範例 2：跟 group 平均值比較

假設我們想看每個銷售員賺的錢，跟他那個區域的平均比起來怎樣。用 AVG()：

SELECT
    region,
    salesperson,
    amount,
    AVG(amount) OVER (PARTITION BY region) AS avg_sales_by_region,
    amount - AVG(amount) OVER (PARTITION BY region) AS diff_from_avg
FROM sales;

結果：

SQL 會先根據 region 分組，再算每個 group 的 AVG(amount)。最後，每一 row 都會算出它跟平均的差距。

範例 3：在 group 裡面給 row 編號

比如你想要在每個區域 group 裡，給所有交易編號。用 ROW_NUMBER()：

SELECT
    region,
    salesperson,
    amount,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS row_number
FROM sales;

結果：

跟 GROUP BY 的比較

很多人會搞混 PARTITION BY 跟 GROUP BY。來比較一下：

GROUP BY

GROUP BY 會改變輸出的結構——它會把 table 的 row 聚合起來。例如：

SELECT
    region,
    SUM(amount) AS total_sales
FROM sales
GROUP BY region;

結果：

這裡我們就看不到銷售員的資訊了，因為資料被聚合起來了。

PARTITION BY

PARTITION BY 則不會改變結構。我們還是可以看到每一 row，但多了 group 算出來的值。也就是說，PARTITION BY 可以聚合但不會丟細節。

用 PARTITION BY 常見錯誤

錯誤 1：忘記 PARTITION BY

有時候你想分組，但忘了加 PARTITION BY。例如：

SELECT
    region,
    salesperson,
    amount,
    SUM(amount) OVER () AS total_sales
FROM sales;

結果：

這裡 SUM(amount) 是算整個 table 的總額，不是每個區域。如果你想分區域，記得要加 PARTITION BY region。

錯誤 2：ORDER BY 順序搞錯

window 裡面的 row 順序對 RANK() 或 ROW_NUMBER() 這種 function 很重要。用 OVER() 裡的 ORDER BY 時要小心順序。