- 含義
視窗函數，也叫OLAP函數（Online Anallytical Processing，聯機分析處理），可以對資料庫資料進行實時分析處理。視窗函數包含專用視窗函數和聚合函數，兩者差別在于，專用視窗函數傳回的是每組多行資料，不影響行數。聚合函數隻傳回的是每組的單行資料。
2. 基本語句
<視窗函數> over ( partition by <用于分組的列名>
order by <用于排序的列名> ) 2. 視窗函數類型
專用視窗函數：rank，denserank, row_number等
聚合函數：max，min，sum，avg，count等
二、如何使用視窗函數建立測試表，如下
INSERT INTO 班級表(學号,班級,成績)
VALUES('0001','1','86');
INSERT INTO 班級表(學号,班級,成績)
VALUES('0002','1','95');
INSERT INTO 班級表(學号,班級,成績)
VALUES('0003','2','89');
INSERT INTO 班級表(學号,班級,成績)
VALUES('0004','1','83');
INSERT INTO 班級表(學号,班級,成績)
VALUES('0005','2','86');
INSERT INTO 班級表(學号,班級,成績)
VALUES('0006','3','92');
INSERT INTO 班級表(學号,班級,成績)
VALUES('0007','3','86');
INSERT INTO 班級表(學号,班級,成績)
VALUES('0008','1','88'); 每個班級内按成績排名，得出結果。
select *,
rank() over (partition by 班級
order by 成績 desc) as ranking
from 班級表; 我們了解以下視窗函數的sql語句
- partition by 用來對表的分組
每個班級内就是按照班級分組，是以partition by 班級
2. order by 對分組後進行排序，預設升序。
按成績排名，,是以order by 成績，加了desc是按照成績降序排列。
分組彙總與視窗函數的差別：group by分組彙總後會改變表的行數，一行隻有一個類别。而partition by和rank 函數不會影響原表的行數。
/*group by 分組彙總改變行數*/
select 班級,count(學号)
from 班級表
group by 班級
order by 班級; /*partition by 分組彙總總行數不變*/
select 班級,
count(學号) over (partition by 班級
order by 班級) as current_count
from 班級表; rank ：跳躍排序,相同資料排名相同，排名存在重複值。
dense_rank ：連續排序，相同資料排排名相同。
row_number ：相同資料，在前面的排名優先，沒有重複值。
select *,
rank() over (order by 成績 desc ) as ranking,
dense_rank() over (order by 成績 desc) as dense_ranking,
row_number() over (order by 成績 desc) as row_num
from 班級表; 注意事項：
- 視窗函數as後面的别名不能跟函數重名，不然會報錯。
- 記得視窗函數over後面加括号。
應用：面試經典排名問題
三、聚合視窗函數- 含義
聚合視窗函數和專用視窗函數用法一樣，隻需把聚合函數放在視窗函數的位置，但是函數後面要指定聚合的列名。
2. sql語句應用
select *,
sum(成績) over (order by 學号)as current_sum,
avg(成績) over (order by 學号)as current_avg,
count(成績) over (order by 學号)as current_count,
max(成績) over (order by 學号)as current_max,
min(成績) over (order by 學号)as current_min
from 班級表; 針對自身記錄，以及自身記錄之上的資料進行計算。比如：先按學号排序，current_sum列的每行資料是前面所有行資料的總和。current_max列的每行資料是對比前面所有行資料後取最大值。
3. 作用
可以在每一行的資料裡直覺的看到，截至到本行資料，統計資料是多少。同時可以看出每行資料，對整體統計資料的影響。
四、經典面試題 1. 排名問題考察的是rank、dense_rank、row_number的差別。具體如何使用請回顧第二部分的專用視窗函數。
2. topN問題經常遇到的業務問題：
如何找到每個類别下使用者最喜歡的産品是哪個？
如果找到每個類别下的使用者點選最多的5個商品是什麼？
其實就是：分組取每組最大值、最小值，每組最大的N條記錄。
案例：按課程号分組提取成績最大值所在行的資料。
建立測試表score1：
- 方法1：若使用group by分組和彙總函數，可得每個組的一個值，但是無法取得成績最大值所在行的資料。需要結合關聯子查詢來實作。
select *
from score1 as a
where 成績=(select max(成績) from score1 as b
where a.課程号=b.課程号); - 方法 2：運用視窗函數
select *
from (select *,
dense_rank() over(partition by 課程号 order by 成績 desc)as dense_ranking from score1) as a
where dense_ranking=1; topN問題的萬能模闆：
select * from
(select *,
row_number() over (partition by 要分組的列名 order by 要排序的列名 desc) as ranking
from 表名) as a
where ranking <= N; 案例1：按照雇員編号升序排列，查找薪水的累計薪水。其中累計薪水是前N個目前員工（結束日期=‘9999-01-01‘）薪水的累計和。
select 雇員編号,薪水,
sum（薪水) over (order by 雇員編号) as 累計薪水
from 薪水表
where 結束時間='1/1/9999'; 案例2：下表為确診人數表，包含日期和該日期對應的新增确診人數。
按照日期進行升序排列，查找日期、确認人數以及對應的累計确診人數。
select 日期, 确診人數,
sum(确診人數) over (order by 日期) as 累計确診人數
from 确診人數表; 累計求和問題的萬能模闆：
select 列1,列2,
sum（列名) over (order by 用于排序的列名) as 累計值别名
from 表名; 各科成績表為如下：
查找單科成績高于該科目平均成績的學生名單。
select 姓名,科目,
avg(成績) over (partition by 科目) as 平均成績
from 各科成績表
where 成績>平均成績; ！！！sql報錯！！！
原因是在運作順序中，select字句是最後被運作，該sql是先運作where後select。
正确sql語句寫法：
select *
from (select *, avg(成績) over (partition by 科目) as 平均成績
from 各科成績表) as a
where 成績>平均成績; 方法二：運用關聯子查詢。
select 姓名,科目,成績
from 各科成績表 a
where 成績>(select avg(成績) from 各科成績表 as b where a.科目=b.科目); 以班級表為測試表。
select *,
avg(成績) over (order by 學号 rows 2 preceding) as current_avg
from 班級表; rows和precedin是"之前~行"的意思，得出的current_avg數值是自己記錄以及前2行的平均值。譬如：學号0004學号的current_avg是自己和前2位同學的平均，即學号0002、0003、0004三位同學的平均。
業務場景：
在公司業績名單排名中，可以通過移動平均，直覺地檢視到與相鄰名稱業績的平均，求和等統計資料。