帶你快速了解SQL窗口函數(shù)

更新時(shí)間：2022年03月29日 09:48:43 作者：黃子毅

窗口函數(shù),也叫OLAP函數(shù)（Online Anallytical Processing,聯(lián)機(jī)分析處理）,可以對(duì)數(shù)據(jù)庫(kù)數(shù)據(jù)進(jìn)行實(shí)時(shí)分析處理,下面這篇文章主要給大家介紹了關(guān)于SQL窗口函數(shù)的相關(guān)資料,需要的朋友可以參考下

底表

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

分組排序

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

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

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

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

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

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

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

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

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

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

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

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

上面的例子可以?xún)?yōu)化一下，因?yàn)樗写翱谶壿嫸际窍嗤?，我們可以利?WINDOW AS 提取為一個(gè)變量：

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

累計(jì)聚合

我們之前說(shuō)過(guò)，凡事使用了聚合函數(shù)，都會(huì)讓查詢(xún)變成聚合模式。如果不用 GROUP BY，聚合后返回行數(shù)會(huì)壓縮為一行，即使用了 GROUP BY，返回的行數(shù)一般也會(huì)大大減少，因?yàn)榉纸M聚合了。

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

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

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

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

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

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