AL9-5. 窗口函数使用场景：聚合窗口函数

聚合函数，一般指sum、max、min、avg、count

聚合窗口函数（聚合开窗函数）的应用，能简化聚合函数+group by的使用，以下案例来体现

1、查询每一个学科的平均分数

# 使用聚合函数select cname,avg(num) 平均分数 from student group by cname;

• 若写成：select * ,avg(num) 平均分数 from student group by cname;会报错，这种简单的问题，用聚合函数解决即可

2、查询每个学生及格科目的数量

# 使用聚合函数select sname, count(cname) 学科个数 from(select * from student where num >= 60) as e group by sname;

# 使用窗口函数1select sname, count(cname) over(partition by sname) 学科个数 from student where num >= 60;

• where num >= 60：先筛选出成绩大于或等于60分、即成绩及格的分数

• 再从及格的分数中，以sname字段为维度、统计每个学生及格的科目(count(cname))数量

# 使用窗口函数2select *, count(cname) over(partition by sname) 学科个数 from student where num >= 60;

• 把select sname改为select *，在窗口函数中，仍可运行、因为partition by不折叠，只是输出的结果不一样

3、计算每个学生的成绩、与自己的平均分的差距

这种问题使用聚合函数，写起来将非常复杂，但是使用窗口函数便十分简化

# 使用窗口函数1select *, num - e.平均分 as 差 from(select *, avg(num) over(partition by sname) 平均分 from student) as eorder by num;

• select *, avg(num) over(partition by sname) 平均分：新建一列以sname为维度、计算每个学生平均分的字段，字段名为“平均分”，每个学生的平均分是固定不变的

• (select *, avg(num) over(partition by sname) 平均分 from student) as e：将平均分作为子查询表、子表表名为e

• num - e.平均分 as 差：新建一列字段名为“差”，字段值为“num”字段值-“平均分”字段值，求取到每个学生每科分数、减每个学生平均分的差值

# 使用聚合函数2select sname,cname,e.平均分, num - e.平均分 as 差 from(select *, avg(num) over(partition by sname) 平均分 from student)as eorder by num;

• 把select *, num - e.平均分，改成：select sname,cname,e.平均分, num - e.平均分，仅输出结果不一样