窗口函數(shù)形如：

表達式 OVER (PARTITION BY 分組字段 ORDER BY 排序字段)

有兩個能力：

• 當(dāng)表達式為 rank() dense_rank() row_number() 時，擁有分組排序能力。
• 當(dāng)表達式為 sum() 等聚合函數(shù)時，擁有累計聚合能力。

無論何種能力，窗口函數(shù)都不會影響數(shù)據(jù)行數(shù)，而是將計算平攤在每一行。

這兩種能力需要區(qū)分理解。

底表

以上是示例底表，共有 8 條數(shù)據(jù)，城市1、城市2 兩個城市，下面各有地區(qū)1～4，每條數(shù)據(jù)都有該數(shù)據(jù)的人口數(shù)。

分組排序

如果按照人口排序，ORDER BY people 就行了，但如果我們想在城市內(nèi)排序怎么辦？

此時就要用到窗口函數(shù)的分組排序能力：

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

該 SQL 表示在 city 組內(nèi)按照 people 進行排序。

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

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

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

各分組排序函數(shù)的差異

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

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

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

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

上面的例子可以優(yōu)化一下，因為所有窗口邏輯都是相同的，我們可以利用 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)

累計聚合

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

然而使用窗口函數(shù)的聚合卻不會導(dǎo)致返回行數(shù)減少，那么這種聚合是怎么計算的呢？我們不如直接看下面的例子：

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

可以看到，在每個 city 分組內(nèi)，按照 people 排序后進行了 累加（相同的值會合并在一起），這就是 BI 工具一般說的 RUNNGIN_SUM 的實現(xiàn)思路，當(dāng)然一般我們排序規(guī)則使用絕對不會重復(fù)的日期，所以不會遇到第一個紅框中合并計算的問題。

累計函數(shù)還有 avg() min() 等等，這些都一樣可以作用于窗口函數(shù)，其邏輯可以按照下圖理解：

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

可見，如果直接利用上一行結(jié)果的緩存，那么 avg 結(jié)果必然是不準(zhǔn)確的，所以窗口累計聚合是每行重新計算的。當(dāng)然也不排除對于 sum、max、min 做額外性能優(yōu)化的可能性，但 avg 只能每行重頭計算。

與 GROUP BY 組合使用

窗口函數(shù)是可以與 GROUP BY 組合使用的，遵循的規(guī)則是，窗口范圍對后面的查詢結(jié)果生效，所以其實并不關(guān)心是否進行了 GROUP BY。我們看下面的例子：

按照地區(qū)分組后進行累加聚合，是對 GROUP BY 后的數(shù)據(jù)行粒度進行的，而不是之前的明細行。

總結(jié)

窗口函數(shù)在計算組內(nèi)排序或累計 GVM 等場景非常有用，我們只要牢記兩個知識點就行了：

• 分組排序要結(jié)合 PARTITION BY 才有意義。
• 累計聚合作用于查詢結(jié)果行粒度，支持所有聚合函數(shù)。

討論地址是：精讀《SQL 窗口函數(shù)》· Issue #405 · ascoders/weekly

到此這篇關(guān)于SQL窗口函數(shù)的文章就介紹到這了,更多相關(guān)SQL窗口函數(shù)內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論