一、窗口函數有什么用？

在日常生活中，經常會遇到需要在每組內排名，比如下面的業務需求：

排名問題：每個部門按業績來排名

topN問題：找出每個部門排名前N的員工進行獎勵

面對這類需求，就需要使用sql的高級功能窗口函數了。

二、什么是窗口函數，也叫OLAP函數（Online Anallytical Processing,聯機分析處理）,可以對數據庫數據進行實時分析處理。

窗口函數基本語法如下：

<窗口函數> over (partition by <用于分組的列名> order by<用于排序的列名>）

窗口函數的位置，可以放以下兩種函數：

1)專用窗口函數：rank,dense_ rank, row_number

2) 聚合函數：sum,avg,count,max,min

因為窗口函數是對where 或者group by 子句處理后的結果進行操作，所以窗口函數原則上只能寫在select 子句中。

1.專用窗口函數rank

想要每個班級內按成績排名的結果

select *,
rank() over(partition by 班級 order by 成績 desc) as ranking
from 班級表

為什么叫“窗口”函數？

因為partition by分組后的結果稱為“窗口”，表示“范圍”的意思。

簡單來說，窗口函數有以下功能：

1)同時具有分組和排序的功能

2)不減少原表的行數

3)語法見前

2.專用窗口函數rank,dense_rank, row_number有什么區別?

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

從上面結果可以看出：

rank 函數:如果有并列名次的行，會占用下一名次的位置。

dense_rank 函數:如果有并列名次的行，不占用下一名次的位置。

row_number函數:不考慮并列名次的情況。

3. top N問題

每組最大的N條記錄

select * 
from (select *,row_number() over(partition by 要分組的列名order by 要排序的列名 desc) as ranking from 表名)
where ranking<=N;

4.聚合窗口函數

select *,
sum(成績) over(order by 學號) as current_sum
from 班級表

如上圖，聚合函數sum在窗口函數中，是對自身記錄、及位于自身記錄以上的數據進行求和的結果。平均、計數、最大最小值也是同理。

這樣使用窗口函數有什么用？

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

5.查找單科成績高于該科目平均成績的學生名單

1）窗口函數法

select * 
from(select *,avg(成績) over(partition by 科目) as avg_score from 成績表) as b
where 成績>avg_score

2）關聯子查詢法

見之前文章

6.窗口函數的移動平均

select *,
avg(成績) over(order by 學號 rows 2 preceding) as current_avg
from 班級表

每一行得到的結果，都是當前行和前面2行的平均(共3行)。

這樣使用窗口函數有什么用呢？

在公司業績名單排名中，可以通過移動平均，直觀地查看到與相鄰名次業績的平均、求和等統計數據。