SQL 視窗函式

黃子毅發表於2022-03-28
SQL函式

視窗函式形如：

表示式 OVER (PARTITION BY 分組欄位 ORDER BY 排序欄位)

有兩個能力：

  1. 當表示式為 rank() dense_rank() row_number() 時，擁有分組排序能力。
  2. 當表示式為 sum() 等聚合函式時，擁有累計聚合能力。

無論何種能力，視窗函式都不會影響資料行數，而是將計算平攤在每一行。

這兩種能力需要區分理解。

底表

以上是示例底表，共有 8 條資料，城市1、城市2 兩個城市，下面各有地區1～4，每條資料都有該資料的人口數。

分組排序

如果按照人口排序，ORDER BY people 就行了，但如果我們想在城市內排序怎麼辦？

此時就要用到視窗函式的分組排序能力：

SELECT *, rank() over (PARTITION BY city ORDER BY people) FROM test

該 SQL 表示在 city 組內按照 people 進行排序。

其實 PARTITION BY 也是可選的，如果我們忽略它：

SELECT *, rank() over (ORDER BY people) FROM test

也是生效的，但該語句與普通 ORDER BY 等價，因此利用視窗函式進行分組排序時，一般都會使用 PARTITION BY。

各分組排序函式的差異

我們將 rank() dense_rank() row_number() 的結果都列印出來：

SELECT *, 
rank() over (PARTITION BY city ORDER BY people),
dense_rank() over (PARTITION BY city ORDER BY people),
row_number() over (PARTITION BY city ORDER BY people)
FROM test

其實從結果就可以猜到，這三個函式在處理排序遇到相同值時，對排名統計邏輯有如下差異：

  1. rank(): 值相同時排名相同，但佔用排名數字。
  2. dense_rank(): 值相同時排名相同，但不佔用排名數字，整體排名更加緊湊。
  3. row_number(): 無論值是否相同，都強制按照行號展示排名。

上面的例子可以優化一下，因為所有視窗邏輯都是相同的，我們可以利用 WINDOW AS 提取為一個變數：

SELECT *, 
rank() over wd, dense_rank() over wd, row_number() over wd
FROM test
WINDOW wd as (PARTITION BY city ORDER BY people)

累計聚合

我們之前說過，凡事使用了聚合函式，都會讓查詢變成聚合模式。如果不用 GROUP BY，聚合後返回行數會壓縮為一行，即使用了 GROUP BY，返回的行數一般也會大大減少，因為分組聚合了。

然而使用視窗函式的聚合卻不會導致返回行數減少，那麼這種聚合是怎麼計算的呢？我們不如直接看下面的例子：

SELECT *, 
sum(people) over (PARTITION BY city ORDER BY people)
FROM test

可以看到，在每個 city 分組內，按照 people 排序後進行了 累加（相同的值會合並在一起），這就是 BI 工具一般說的 RUNNGIN_SUM 的實現思路，當然一般我們排序規則使用絕對不會重複的日期，所以不會遇到第一個紅框中合併計算的問題。

累計函式還有 avg() min() 等等，這些都一樣可以作用於視窗函式，其邏輯可以按照下圖理解：

你可能有疑問，直接 sum(上一行結果，下一行) 不是更方便嗎？為了驗證猜想，我們試試 avg() 的結果：

可見，如果直接利用上一行結果的快取，那麼 avg 結果必然是不準確的，所以視窗累計聚合是每行重新計算的。當然也不排除對於 sum、max、min 做額外效能優化的可能性，但 avg 只能每行重頭計算。

與 GROUP BY 組合使用

視窗函式是可以與 GROUP BY 組合使用的，遵循的規則是，視窗範圍對後面的查詢結果生效，所以其實並不關心是否進行了 GROUP BY。我們看下面的例子：

按照地區分組後進行累加聚合，是對 GROUP BY 後的資料行粒度進行的，而不是之前的明細行。

總結

視窗函式在計算組內排序或累計 GVM 等場景非常有用，我們只要牢記兩個知識點就行了：

  1. 分組排序要結合 PARTITION BY 才有意義。
  2. 累計聚合作用於查詢結果行粒度，支援所有聚合函式。
討論地址是：精讀《SQL 視窗函式》· Issue #405 · ascoders/weekly

如果你想參與討論，請 點選這裡，每週都有新的主題，週末或週一釋出。前端精讀 - 幫你篩選靠譜的內容。

關注 前端精讀微信公眾號

<img width=200 src="https://img.alicdn.com/tfs/TB165W0MCzqK1RjSZFLXXcn2XXa-258-258.jpg">

版權宣告：自由轉載-非商用-非衍生-保持署名（創意共享 3.0 許可證）

相關文章