Window Function:不收合的聚合

上一篇的 GROUP BY 把每組收合成一列。但你一定遇過這種需求:「我想要整組的計算,又想保留每一列。」 例如——在每一筆訂單旁邊,標上它佔該客戶總額的比例;或在每個月的營收旁,標上跟上個月的差。收合就做不到了,因為收合完你連「每一列」都沒了。這就是 window function:不收合的聚合。它是 SQL 從「會用」到「好用」的分水嶺,也是這個系列第二幕一堆招式的基礎。

Window function = 不收合的聚合

同一份資料、同一個 SUM,GROUP BY 和 OVER 的差別只有一件事:要不要把列收掉:

左邊 GROUP BY 把列收掉;右邊 SUM() OVER (PARTITION BY customer) 保留每一列,只是在旁邊多算一個「該組總計」。有了每列 + 組計併排,你就能算「這筆佔全組多少 %」——這是收合永遠做不到的

一句話:GROUP BY 是「多列變一列」,window function 是「每列旁邊,多一個看整組算出來的值」。 列還在,你才能做「單列 vs 整組」的比較。

OVER 的三個旋鈕

window function 的威力全在 OVER (...) 那個括號裡,拆開來就三個旋鈕:

SUM(amount) OVER (
  PARTITION BY customer    -- ① 分組:但不收合(沒寫就是整張表一組)
  ORDER BY order_date      -- ② 組內排序:定義「到目前為止」的順序
  ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  -- ③ frame:每一列要看哪個範圍
)

前兩個好懂,第三個 frame 是最多人沒搞清楚、也最容易中招的。frame 決定「算這一列時,要涵蓋組內的哪些列」。以累計(running total)為例,每一列的 frame 是「從開頭到目前這列」,所以總和會一列一列長大:

frame 是一扇隨「目前列」滑動的窗。累計是「開頭→目前列」,移動平均是「前 N 列→目前列」——換 frame,就換了整個計算的意義

這裡有個一定要記的坑:frame 的預設值。 當你寫了 ORDER BY 卻沒寫 frame,預設是「開頭→目前列」(累計);沒寫 ORDER BY 時,預設是「整個 partition」(組總計)。所以 SUM() OVER (PARTITION BY c) 是組總計,但 SUM() OVER (PARTITION BY c ORDER BY d) 會變成累計——一個 ORDER BY 就換了意思,很多人在這裡默默算錯。

三類最常用的 window function

排名類:ROW_NUMBER()(1,2,3… 一定唯一)、RANK()(同分同名次、會跳號:1,1,3)、DENSE_RANK()(同分同名次、不跳:1,1,2)。
位移類:LAG() / LEAD() 拿前一列 / 後一列的值。算環比差一行搞定:sales - LAG(sales) OVER (ORDER BY month)。
聚合當 window:SUM / AVG / COUNT 加上 OVER,配 frame 做累計、移動平均、佔比。

經典招式:每組前 N 名

「每個分類的銷量前 3 名」是 window function 最招牌的用途,而它剛好踩中第一篇的執行順序——window function 在 SELECT(⑤)階段才算,不能直接放進 WHERE(②),得先在子查詢裡算出排名,外層再篩:

SELECT * FROM (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS rn
  FROM products
) t
WHERE rn <= 3;   -- 每類前 3 名

那個「為什麼一定要包一層子查詢」的老問題,答案還是執行順序:ROW_NUMBER 算完的時候,WHERE 早跑過了。

反思

「不收合」這一個字,打開一整片新天地

我還記得第一次用 window function 的感覺——SQL 突然「升級」了。很多本來要自己 join 回去、或用一堆相關子查詢硬拼的東西(每列佔比、跟上一列比、每組排名),一個 OVER 就解決,而且又快又好讀。關鍵的頓悟就是上一篇的延伸:GROUP BY 把列收掉,你就失去了「單列」;window function 保留每一列,才讓「這一列 vs 整組」的比較變得可能。很多分析需求的本質,就是『單列跟它所屬群體的關係』,而那正是 window function 生來要做的事。

frame 的預設值,是我看過最多人踩的坑

SUM() OVER (PARTITION BY c ORDER BY d) 到底是組總計還是累計?差別只在你有沒有寫 ORDER BY,而結果天差地遠。這跟 NULL 那篇的精神一模一樣:預設行為你不懂,就會寫出「跑得出來、看起來對、其實錯」的 query。 我現在寫累計或組總計,一定把 frame 明確寫出來(ROWS BETWEEN ...),不靠預設——多打一行字,換掉一整類難抓的 bug,划算得很。

window function 是分析型 SQL 的分水嶺

老實說,會不會 window function,幾乎是我判斷一個人 SQL 深不深的一條線。它不只是個方便的函式,而是一種看資料的角度——把每一列放回它所屬的群體、序列裡去看。這個系列接下來第二幕的幾招——去重取最新、連續區間(gaps and islands)、慢變維(SCD)——骨子裡全是 window function 的應用。所以這篇是壓箱寶,也是後面的地基:把 OVER 的三個旋鈕轉熟,你的 SQL 才算真正進了分析的門。