在使用數(shù)據(jù)庫(kù)/數(shù)據(jù)倉(cāng)庫(kù)的過(guò)程中，我們可能會(huì)遇到以下幾個(gè)情況：

1. 需要得到的數(shù)據(jù)既要包括聚合后的數(shù)據(jù)， 也需要包括沒(méi)被聚合的字段
2. 需要獲取多個(gè)字段前n條的數(shù)據(jù)

這個(gè)時(shí)候如果我們使用傳統(tǒng)的group by聚合函數(shù)的話，可能會(huì)出現(xiàn)以下的問(wèn)題，

對(duì)于第一種情況，如果sql語(yǔ)句使用group by的話，select只能包括group by的字段以及根據(jù)它們聚合得到的字段，如果select包括其它沒(méi)有參與聚合的字段的話，sql會(huì)報(bào)錯(cuò)，此時(shí)如果不把它們也放入group by語(yǔ)句里的話，可能語(yǔ)句就無(wú)法執(zhí)行
對(duì)于第二種情況，如果用group by的話語(yǔ)句就會(huì)比較復(fù)雜

為了解決這些實(shí)際操作中可能會(huì)遇到的問(wèn)題，我們可以使用窗口函數(shù)來(lái)進(jìn)行g(shù)roup by的替代。窗口函數(shù)可以讓聚合列和基礎(chǔ)列同時(shí)顯示在查詢結(jié)果中。
窗口函數(shù)可以分為兩大類，聚合窗口函數(shù)和排序窗口函數(shù)。它們均有以下類似的調(diào)用格式：

{windows_func}() over(partition by xx order by xx)

很多數(shù)據(jù)庫(kù)均支持窗口函數(shù)，MySQL從8.0版本開(kāi)始也支持窗口函數(shù)。

1. 聚合窗口函數(shù)

顧名思義，這一類窗口函數(shù)是實(shí)現(xiàn)類似于group by的聚合的功能。它和group by的區(qū)別是， group by的話對(duì)于聚合的字段的話只會(huì)顯示一行結(jié)果，而聚合窗口函數(shù)的話可以在符合條件的每一行中均顯示相關(guān)的結(jié)果。

聚合窗口函數(shù)包括以下幾個(gè)類別：

1. sum()
2. count()
3. max()
4. min()
5. avg()

舉個(gè)栗子，假設(shè)以下這張score表是某小學(xué)的學(xué)生成績(jī)單。

這時(shí)如果我們想知道每個(gè)班數(shù)學(xué)成績(jī)良好（>=75分）的人數(shù)以及班級(jí)每個(gè)同學(xué)的數(shù)學(xué)成績(jī)和同班級(jí)最高分、最低分、平均分的差距的話，就可以使用窗口函數(shù)來(lái)進(jìn)行查詢。

select *, avg(math_score) over (partition by class_name) as avg_math_score,
       max(math_score) over (partition by class_name) as max_math_score,
       min(math_score) over (partition by class_name) as min_math_score,
       count(case when math_score>=75 then user_id end) over (partition by class_name) as math_good_cnt
from score
order by user_id

1.jpg

可以看到結(jié)果如上。聚合開(kāi)窗函數(shù)的partition by類似于數(shù)據(jù)庫(kù)的group by聚合操作，在這個(gè)SQL例子中，開(kāi)窗函數(shù)根據(jù)不同班級(jí)將數(shù)據(jù)分組，然后求出對(duì)應(yīng)的平均值，最大值以及最小值。這里的 count() 開(kāi)窗函數(shù)使用了case when語(yǔ)句篩選了數(shù)學(xué)成績(jī)大等于75分的同學(xué)，然后進(jìn)行聚合計(jì)算。

2. 排序窗口函數(shù)

排序窗口函數(shù)是另外一種窗口函數(shù)，它主要獲取每一行的數(shù)據(jù)按照某些字段聚合之后，關(guān)于某一字段的排名。主要有以下幾種：

1. row_number() over(order by)
2. rank() over(order by)
3. dense_rank() over(order by)
4. ntile(分組數(shù)) () over(order by)

排序窗口的函數(shù)排序默認(rèn)是升序排序，如果需要降序排序的話需要在后面加上DESC。同時(shí)排序窗口支持Partition分組，但是partition需要放在order by前面。（這個(gè)和group by類似）

關(guān)于row_number(), rank()和dense_rank()，它們的區(qū)別主要在于遇到有數(shù)據(jù)并列情況的時(shí)候的區(qū)別：

1. row_number()排序的時(shí)候每一行都有唯一的排序編號(hào)，即使有并列的數(shù)據(jù)它們的rank也是不同的
   （可以理解為row_number的排序一定要決出一個(gè)名次）
2. rank()和dense_rank()會(huì)給并列的數(shù)據(jù)相同的排序編號(hào)，區(qū)別是在給相同數(shù)據(jù)排序之后，rank()會(huì)留下若干個(gè)排名空位（取決于有幾個(gè)相同數(shù)據(jù)），而dense_rank()不會(huì)。
   （舉個(gè)栗子，一個(gè)比賽要決出前三名，但是有兩個(gè)并列第二的選手。這種情況下rank（）排序的話就沒(méi)有第三名，站在領(lǐng)獎(jiǎng)臺(tái)的依舊只有三位選手（第三名是空的）。但是用dense_rank()排序的話就會(huì)有四位選手站在領(lǐng)獎(jiǎng)臺(tái)）

至于ntile()的話它是根據(jù)分組數(shù)來(lái)進(jìn)行均分分組的，比如分組數(shù)為2的話，那么就是排序之后前一半數(shù)據(jù)歸為第1組，后一半數(shù)據(jù)歸為第2組。如果數(shù)據(jù)沒(méi)法均分，那么ntile會(huì)優(yōu)先考慮多分?jǐn)?shù)據(jù)到rank較小的分組中（比如要把{1, 3, 5, 8, 10）分為2組，那么{1, 3, 5}會(huì)分為第1組， {8, 10}會(huì)分為第2組）

繼續(xù)舉之前成績(jī)單的例子，假設(shè)我們想知道每個(gè)班學(xué)生在班內(nèi)的排名情況，那么我們可以用排序窗口函數(shù)來(lái)進(jìn)行查看，

select *, rank() over(partition by class_name order by math_score desc) as rank_rank,
row_number() over(partition by class_name order by math_score desc) as row_number_rank,
dense_rank()over(partition by class_name order by math_score desc) as dense_rank_rank,
ntile(2) over (partition by class_name order by math_score desc) as ntile_rank
from score

結(jié)果如圖，從查詢情況來(lái)看也可以發(fā)現(xiàn)不同排序窗口函數(shù)的區(qū)別。

3. Value 窗口函數(shù)

英文名為Value Window Functions，沒(méi)找到一個(gè)合適的中文翻譯，因此就以value窗口函數(shù)來(lái)命名了。
這一類的窗口函數(shù)主要有以下四種

1. Lag()
2. Lead()
3. First_Value()
4. Last_Value()

3.1 Lag()和Lead()

Lag()函數(shù)的作用是返回當(dāng)前行的前n行的某一列的值，Lead()函數(shù)是返回當(dāng)前行后n行的某一列的值。
具體的參數(shù)如下：

LAG | LEAD （col, line_num, DEFAULT)
OVER (PARTITION BY ORDER BY)

col: 指定獲取哪一列的字段
line_num:決定返回前/后多少行的值
default: 默認(rèn)為None，如果找不到對(duì)應(yīng)的值則返回該值。（比如要返回第一行的前兩行數(shù)據(jù)，這種情況就是默認(rèn)返回None)
order by 根據(jù)哪些字段排序，這決定了返回的前n行/后n行是按照哪個(gè)字段的排列為標(biāo)準(zhǔn)的。
舉個(gè)栗子，假設(shè)有一張記錄每個(gè)學(xué)生多次考試成績(jī)的表。

4.jpg

如果我們想要在一行里面知道該學(xué)生上一次的考試成績(jī)和下一次的考試成績(jī)的話，可以寫如下的sql語(yǔ)句：

select *, lag(score, 1) over (partition by user_id, subject order by exam_index) as last_score,
lead(score, 1) over (partition by user_id, subject order by exam_index) as next_score
from exam_record

可以看出在同一行中，顯示出了學(xué)生當(dāng)次，上一次和下一次的考試成績(jī)。

3.jpg

3.2 First_value()和Last_value()

這兩個(gè)函數(shù)的作用是返回特定分組內(nèi)的某一字段第一次出現(xiàn)的值和最后一次出現(xiàn)的值。
具體的參數(shù)如下：

FIRST_VALUE | LAST_VALUE （col （ignore nulls))
OVER (PARTITION BY ORDER BY)

比如我們想要在一行里面知道每個(gè)學(xué)生當(dāng)次考試成績(jī)，第一次考試成績(jī)和最后一次考試成績(jī)的話，可以寫如下的sql語(yǔ)句：

select *, first_value(score) over (partition by user_id, subject order by exam_index) as first_score,
last_value(score) over (partition by user_id, subject order by exam_index rows between unbounded preceding and unbounded following) as last_score
from exam_record

可以得到結(jié)果如下:

5.jpg

需要注意的是，這里last value的over參數(shù)后面加了個(gè)rows between unbounded preceding and unbounded following語(yǔ)句，這是為什么呢？因?yàn)閘ast_value的默認(rèn)選項(xiàng)是 BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，即默認(rèn)是當(dāng)前行和之前行比較，如果沒(méi)加這個(gè)參數(shù)的話，每一行對(duì)應(yīng)的last_value都是自己的值，而不是整體的最后的值。

關(guān)于參數(shù)的ignore nulls選項(xiàng)，它是可選的，如果添加ignore nulls的話，那么如果說(shuō)對(duì)應(yīng)分組有NULL值的話，會(huì)單獨(dú)列出來(lái)，而不會(huì)參與分組之內(nèi)的first value計(jì)算。

以上便是關(guān)于SQL開(kāi)窗函數(shù)的所有內(nèi)容了。寫完之后發(fā)現(xiàn)自己對(duì)于這方面的知識(shí)又鞏固了:)。