- 含义
窗口函数，也叫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三位同学的平均。
业务场景：
在公司业绩名单排名中，可以通过移动平均，直观地查看到与相邻名称业绩的平均，求和等统计数据。