mysql 窗口函数_mysql 窗口函数及练习

--窗口函数（用于每组内部排名）

--基本句式
<窗口函数> over (
partition by <用于分组的列名>
order by <用于排序的列名>
)
--其中窗口函数可放1.专用窗口函数：rank,dense_rank,row_number，函数后的()内可不填值；2.聚合函数：sum,avg,count,max,min，函数后的()内要填指定聚合的列名
--窗口函数是对wheregroup by子句处理的结果进行操作，原则上只能写在select子句中

--eg1.rank在class中按班级分组排名成绩
rank () over (
partition by 班级    --对表分组；分组后的结果即称窗口（范围[分组的范围？]）
order by 成绩 desc)  --对分组后结果进行排序，order by 默认按升序排列
--注意该rank函数处理结果应作为单独一列；rank函数后不需接任何参数
select *,rank () over (
partition by 班级
order by 成绩 desc) as 成绩排布
from class
--而select * from class group by 班级 order by 成绩 desc   --group by分组会改变表的行数，对表格从上至下检查不同组，一个类别只选出现的第一行值只占一行；所以如果只需统计各班总人数用group by即可

--eg2.rank,dense_rank,row_number的区别
select *,rank() over (order by 成绩 desc) as ranks, --rank函数：如果有并列名次的行，会占用下一名次的位置。
dense_rank() over (order by 成绩 desc) as dense_ranks, --如果有并列名次的行，不占用下一名次的位置
row_number() over (order by 成绩 desc) as row_numbers --不考虑并列名次的情况
from class
--注意新列名不要与sql中函数名重复

--eg3.经典排名Q：要求并列名次排名相同，且不占用后面的名次--用dense_rank

--eg4.读取top N所在行的全部记录
--最高分
select 课程号,max(成绩) group by 课程号 from score
select * from score as a where 成绩 in(select max(成绩) group by 课程号 from score as b where a.课程号 = b.课程号) group by 课程号 
select * from score as a where 成绩 = (select max(成绩) from score as b where a.课程号 = b.课程号) order by 课程号
--每个学生成绩最高的N个科目
select * from score as a where 成绩 =
(select 成绩 from score as b where a.学号 = b.学号 group by 学号 order by 成绩 asc limit 2)--从score表中得结果的这个方法不知道哪错了
  --应用窗口函数，且应不考虑并列情况（why？）
select *,dense_rank() over(partiotion by 学号 order by 成绩 desc) as 排布 from various 
--更正：
select *,row_number() over(partiotion by 学号 order by 成绩 desc) as 排布 from various 
where 排布 <=2 --但由于select最后一步才执行，所以报错
--更正：将第一步得到的结果作为新表（子查询）进行查询
select * from(select *,row_number() over(partiotion by 学号 order by 成绩 desc) as 排布 from various ) where 排布 <= N

--eg4.利用聚合函数作为窗口函数
select *,
sum(成绩) over(order by 学号) as 累计总成绩, 
avg(成绩) over(order by 学号) as 累计平均成绩,
count(成绩) over(order by 学号) as 累计数量,
max(成绩) over(order by 学号) as 累计最高成绩,
min(成绩) over(order by 学号) as 累计最低成绩
from various
--sum求和，平均、计数、最大最小值，也是同理，都是针对自身记录、以及自身记录之上的所有数据进行计算；以此看出每一行数据对整体数据的影响

--如何在每个组内比较
--eg1.查找单科成绩高于该科目平均成绩的学生名单
select * from score where 成绩 > (select avg(成绩) group by 课程号 from score)
select *,avg(成绩) from score group by 课程号 having 成绩 > avg(成绩)
--更正为不改变总行数，应用partition BY
select * from(select *,avg(成绩) over (partition by 科目) as 平均成绩 from various) where 成绩 > avg(成绩 ) order by 成绩 desc limit 2
select * from (select *,avg(成绩) over (partition by 科目) as avg_score from 成绩表) as b where 成绩 › avg_score;

--窗口的移动平均
select *,avg(成绩) over (order by 学号 rows 2 preceding) as current_avg from 班级表;
--row n preceding表示该行与之前的n行组成的n+1行的平均，相当于一个倒T型框子
--在公司业绩名单排名中，可以通过移动平均，直观地查看到与相邻名次业绩的平均、求和等统计数据

--一些注意事项
--1.partition子句可以省略，省略就是不指定分组，如只是按成绩由高到低进行了排序
--2.窗口函数的功能
  --1）同时具有分组（partition by）和排序（order by）的功能
  --2）不减少原表的行数，所以经常用来在每组内排名
--3窗口函数只能用在select子句中
--4.窗口函数使用场景
  --1）经典top N
  --2）每组内排名
  --3）每组内比较：方法1，使用前面窗口函数案例来实现 方法2，使用关联子查询