帶你快速瞭解SQL視窗函數

2022-03-29 13:05:54

底表

以上是範例底表，共有 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

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

rank(): 值相同時排名相同，但佔用排名數位。
dense_rank(): 值相同時排名相同，但不佔用排名數位，整體排名更加緊湊。
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 等場景非常有用，我們只要牢記兩個知識點就行了：

分組排序要結合 PARTITION BY 才有意義。
累計聚合作用於查詢結果行粒度，支援所有聚合函數。

討論地址是：精讀《SQL 視窗函數》· Issue #405 · ascoders/weekly

到此這篇關於SQL視窗函數的文章就介紹到這了,更多相關SQL視窗函數內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

帶你快速瞭解SQL視窗函數

目錄

底表

分組排序

各分組排序函數的差異

累計聚合

與 GROUP BY 組合使用

總結

熱門文章