SQL窗口函數(shù)是什么

這篇文章主要介紹了SQL窗口函數(shù)是什么，具有一定借鑒價(jià)值，感興趣的朋友可以參考下，希望大家閱讀完這篇文章之后大有收獲，下面讓小編帶著大家一起了解一下。

窗口函數(shù)形如：

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

有兩個(gè)能力：

• 當(dāng)表達(dá)式為 rank() dense_rank() row_number() 時(shí)，擁有分組排序能力。

• 當(dāng)表達(dá)式為 sum() 等聚合函數(shù)時(shí)，擁有累計(jì)聚合能力。

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

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

底表

以上是示例底表，共有 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ì)算。

與 GROUP BY 組合使用

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

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

總結(jié)

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

• 分組排序要結(jié)合 PARTITION BY 才有意義。

• 累計(jì)聚合作用于查詢(xún)結(jié)果行粒度，支持所有聚合函數(shù)。

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

感謝你能夠認(rèn)真閱讀完這篇文章，希望小編分享的“SQL窗口函數(shù)是什么”這篇文章對(duì)大家有幫助，同時(shí)也希望大家多多支持億速云，關(guān)注億速云行業(yè)資訊頻道，更多相關(guān)知識(shí)等著你來(lái)學(xué)習(xí)!