窗口函數(shù)

• 簡(jiǎn)單理解，就是對(duì)查詢的結(jié)果多出一列，這一列可以是聚合值，也可以是排序值。
• 開窗函數(shù)一般就是說(shuō)的是over（）函數(shù)，其窗口是由一個(gè) OVER 子句 定義的多行記錄
• 開窗函數(shù)一般分為兩類,聚合開窗函數(shù)和排序開窗函數(shù)。

簡(jiǎn)單來(lái)說(shuō)，窗口函數(shù)有以下功能：

1）同時(shí)具有分組和排序的功能

2）不減少原表的行數(shù)

3）語(yǔ)法如下：

<窗口函數(shù)> over (partition by <用于分組的列名>
? ? ? ? ? ? ? ? order by <用于排序的列名> [rows between ?? and ???])

<窗口函數(shù)>的位置，可以放以下兩種函數(shù)：

1） 專用窗口函數(shù)，包括后面要講到的rank, dense_rank, row_number等專用窗口函數(shù)。

2） 聚合函數(shù)，如sum(). avg(), count(), max(), min()等,rows between…and…

因?yàn)榇翱诤瘮?shù)是對(duì)where或者group by子句處理后的結(jié)果進(jìn)行操作，所以窗口函數(shù)原則上只能寫在select子句中。

3）業(yè)務(wù)需求“在每組內(nèi)排名”，比如：

• 排名問題：每個(gè)部門按業(yè)績(jī)來(lái)排名
• topN問題：找出每個(gè)部門排名前N的員工進(jìn)行獎(jiǎng)勵(lì)

1.1 排序窗口函數(shù)rank

-- 如果我們想在每個(gè)班級(jí)內(nèi)按成績(jī)排名，得到下面的結(jié)果。
select *,
? ?rank() over (partition by 班級(jí)
? ? ? ? ? ? ? ? ?order by 成績(jī) desc) as ranking
from 班級(jí)表;

我們來(lái)解釋下這個(gè)sql語(yǔ)句里的select子句。rank是排序的函數(shù)。要求是“每個(gè)班級(jí)內(nèi)按成績(jī)排名”，這句話可以分為兩部分：

1）每個(gè)班級(jí)內(nèi)：按班級(jí)分組

• partition by用來(lái)對(duì)表分組。在這個(gè)例子中，所以我們指定了按“班級(jí)”分組（partition by 班級(jí)）

2）按成績(jī)排名

• order by子句的功能是對(duì)分組后的結(jié)果進(jìn)行排序，默認(rèn)是按照升序（asc）排列。在本例中（order by 成績(jī) desc）是按成績(jī)這一列排序，加了desc關(guān)鍵詞表示降序排列。

通過下圖，我們就可以理解partiition by（分組）和order by（在組內(nèi)排序)的作用了。 

group by分組匯總后改變了表的行數(shù)，一行只有一個(gè)類別。而partiition by和rank函數(shù)不會(huì)減少原表中的行數(shù)。

注意事項(xiàng)：

• partition子句可是省略，省略就是不指定分組，只是按成績(jī)由高到低進(jìn)行了排序。但是，這就失去了窗口函數(shù)的功能，所以一般不要這么使用。
• 窗口函數(shù)原則上只能寫在select子句中

1.2 rank(), dense_rank(), row_number()區(qū)別

select *,
   rank() over (order by 成績(jī) desc) as ranking,
   dense_rank() over (order by 成績(jī) desc) as dese_rank,
   row_number() over (order by 成績(jī) desc) as row_num
from 班級(jí)表

得到結(jié)果：

從上面的結(jié)果可以看出：

• rank函數(shù)：這個(gè)例子中是5位，5位，5位，8位，也就是如果有并列名次的行，會(huì)占用下一名次的位置。比如正常排名是1，2，3，4，但是現(xiàn)在前3名是并列的名次，結(jié)果是：1，1，1，4。
• dense_rank函數(shù)：這個(gè)例子中是5位，5位，5位，6位，也就是如果有并列名次的行，不占用下一名次的位置。比如正常排名是1，2，3，4，但是現(xiàn)在前3名是并列的名次，結(jié)果是：1，1，1，2。
• row_number函數(shù)：這個(gè)例子中是5位，6位，7位，8位，也就是不考慮并列名次的情況。比如前3名是并列的名次，排名是正常的1，2，3，4。

1.3 排序截取數(shù)據(jù)lag(),lead(),ntile(),cume_dist()

• LAG(col,n,default_val)：獲取往前第n行數(shù)據(jù)，col是列名，n是往上的行數(shù)，當(dāng)?shù)趎行為null的時(shí)候取default_val
• LEAD(col,n, default_val)：往后第n行數(shù)據(jù)，col是列名，n是往下的行數(shù)，當(dāng)?shù)趎行為null的時(shí)候取default_val
• NTILE(n)：把有序分區(qū)中的行分發(fā)到指定數(shù)據(jù)的組中，各個(gè)組有編號(hào)，編號(hào)從1開始，對(duì)于每一行，NTILE返回此行所屬的組的編號(hào)。
• cume_dist（），計(jì)算某個(gè)窗口或分區(qū)中某個(gè)值的累積分布。假定升序排序，則使用以下公式確定累積分布：

小于等于當(dāng)前值x的行數(shù) / 窗口或partition分區(qū)內(nèi)的總行數(shù)。其中，x 等于 order by 子句中指定的列的當(dāng)前行中的值。

1.4 聚合函數(shù)作為窗口函數(shù)

聚和窗口函數(shù)和上面提到的專用窗口函數(shù)用法完全相同，只需要把聚合函數(shù)寫在窗口函數(shù)的位置即可，但是函數(shù)后面括號(hào)里面不能為空，需要指定聚合的列名。

我們來(lái)看一下窗口函數(shù)是聚合函數(shù)時(shí)，會(huì)出來(lái)什么結(jié)果：

select *,
   sum(成績(jī)) over (order by 學(xué)號(hào)) as current_sum,
   avg(成績(jī)) over (order by 學(xué)號(hào)) as current_avg,
   count(成績(jī)) over (order by 學(xué)號(hào)) as current_count,
   max(成績(jī)) over (order by 學(xué)號(hào)) as current_max,
   min(成績(jī)) over (order by 學(xué)號(hào)) as current_min
from 班級(jí)表

如上圖，聚合函數(shù)sum在窗口函數(shù)中，是對(duì)自身記錄、及位于自身記錄以上的數(shù)據(jù)進(jìn)行求和的結(jié)果。比如0004號(hào)，在使用sum窗口函數(shù)后的結(jié)果，是對(duì)0001，0002，0003，0004號(hào)的成績(jī)求和，若是0005號(hào)，則結(jié)果是0001號(hào)~0005號(hào)成績(jī)的求和，以此類推。

不僅是sum求和，平均、計(jì)數(shù)、最大最小值，也是同理，都是針對(duì)自身記錄、以及自身記錄之上的所有數(shù)據(jù)進(jìn)行計(jì)算，

這樣使用窗口函數(shù)有什么用呢？

聚合函數(shù)作為窗口函數(shù)，可以在每一行的數(shù)據(jù)里直觀的看到，截止到本行數(shù)據(jù)，統(tǒng)計(jì)數(shù)據(jù)是多少（最大值、最小值等）。同時(shí)可以看出每一行數(shù)據(jù)，對(duì)整體統(tǒng)計(jì)數(shù)據(jù)的影響。

1.5 over(- - rows between and )

sum()/... over ([partition by 列名] [order by 列名] [rows between ... and ...] )
-- 從起點(diǎn)到當(dāng)前行數(shù)據(jù)聚合
between unbounded preceding and current row?
-- 往前2行到往后1行的數(shù)據(jù)聚合
between 2 preceding and 1 following?

rows必須跟在Order by 子句之后，對(duì)排序的結(jié)果進(jìn)行限制，使用固定的行數(shù)來(lái)限制分區(qū)中的數(shù)據(jù)行數(shù)量。

• OVER()：指定分析函數(shù)工作的數(shù)據(jù)窗口大小，這個(gè)數(shù)據(jù)窗口大小可能會(huì)隨著行的變而變化。
• CURRENT ROW：當(dāng)前行
• n PRECEDING：往前n行數(shù)據(jù)
• n FOLLOWING：往后n行數(shù)據(jù)
• UNBOUNDED：起點(diǎn)，unbounded preceding 表示從表數(shù)據(jù)的起點(diǎn)， unbounded following表示到后面的終點(diǎn)

select name,subject,score,
sum(score) over() as sum1,
sum(score) over(partition by subject) as sum2,
sum(score) over(partition by subject order by score) as sum3,?
-- 由起點(diǎn)到當(dāng)前行的窗口聚合，和sum3一樣
sum(score) over(partition by subject order by score rows between unbounded preceding and current row) as sum4,?
-- 當(dāng)前行和前面一行的窗口聚合
sum(score) over(partition by subject order by score rows between 1 preceding and current row) as sum5,
-- 當(dāng)前行的前面一行和后面一行的窗口聚合
sum(score) over(partition by subject order by score rows between 1 preceding AND 1 following) as sum6,
-- 當(dāng)前和后面所有的行
sum(score) over(partition by subject order by score rows between current row and unbounded following) as sum7
from t_fraction;
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+
| name ?| subject ?| score ?| sum1 ?| sum2 ?| sum3 ?| sum4 ?| sum5 ?| sum6 ?| sum7 ?|
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+
| 孫悟空 ? | 數(shù)學(xué) ? ? ? | 12 ? ? | 359 ? | 185 ? | 12 ? ?| 12 ? ?| 12 ? ?| 31 ? ?| 185 ? |
| 沙悟凈 ? | 數(shù)學(xué) ? ? ? | 19 ? ? | 359 ? | 185 ? | 31 ? ?| 31 ? ?| 31 ? ?| 104 ? | 173 ? |
| 豬八戒 ? | 數(shù)學(xué) ? ? ? | 73 ? ? | 359 ? | 185 ? | 104 ? | 104 ? | 92 ? ?| 173 ? | 154 ? |
| 唐玄奘 ? | 數(shù)學(xué) ? ? ? | 81 ? ? | 359 ? | 185 ? | 185 ? | 185 ? | 154 ? | 154 ? | 81 ? ?|
| 豬八戒 ? | 英語(yǔ) ? ? ? | 11 ? ? | 359 ? | 80 ? ?| 11 ? ?| 11 ? ?| 11 ? ?| 26 ? ?| 80 ? ?|
| 孫悟空 ? | 英語(yǔ) ? ? ? | 15 ? ? | 359 ? | 80 ? ?| 26 ? ?| 26 ? ?| 26 ? ?| 49 ? ?| 69 ? ?|
| 唐玄奘 ? | 英語(yǔ) ? ? ? | 23 ? ? | 359 ? | 80 ? ?| 49 ? ?| 49 ? ?| 38 ? ?| 69 ? ?| 54 ? ?|
| 沙悟凈 ? | 英語(yǔ) ? ? ? | 31 ? ? | 359 ? | 80 ? ?| 80 ? ?| 80 ? ?| 54 ? ?| 54 ? ?| 31 ? ?|
| 孫悟空 ? | 語(yǔ)文 ? ? ? | 10 ? ? | 359 ? | 94 ? ?| 10 ? ?| 10 ? ?| 10 ? ?| 31 ? ?| 94 ? ?|
| 唐玄奘 ? | 語(yǔ)文 ? ? ? | 21 ? ? | 359 ? | 94 ? ?| 31 ? ?| 31 ? ?| 31 ? ?| 53 ? ?| 84 ? ?|
| 沙悟凈 ? | 語(yǔ)文 ? ? ? | 22 ? ? | 359 ? | 94 ? ?| 53 ? ?| 53 ? ?| 43 ? ?| 84 ? ?| 63 ? ?|
| 豬八戒 ? | 語(yǔ)文 ? ? ? | 41 ? ? | 359 ? | 94 ? ?| 94 ? ?| 94 ? ?| 63 ? ?| 63 ? ?| 41 ? ?|
+-------+----------+--------+-------+-------+-------+-------+-------+-------+-------+

以上為個(gè)人經(jīng)驗(yàn)，希望能給大家一個(gè)參考，也希望大家多多支持腳本之家。

最新評(píng)論