MySQL之窗口函数

1 窗口函数定义

窗口函数是OLAP(online analytical processing)，可以对数据库内的数据实时分析处理。换句话来说，就是group by的完整显示版。

1.1 语法结构

<窗口函数>  over
（partition by <用于分组的列名>
order by <用于排序的列名>）

<窗口函数>一般有以下两种：

1. 排序函数，包括rank，dense_rank，row_number等。
2. 聚合函数，如sum，avg，count，max，min等。

其中，

• rank为1,2,2,4,5
• dense_rank为1,2,2,3,4
• row_number为1,2,3,4,5

1.2 特点

窗口函数只能用在select语句中
因此，它的别名不能用在where语句中，若要使用过滤，必须用子查询的方式

2 窗口函数使用

select 列名1，‹窗口函数› over 
                      (partition by ‹用于分组的列名› 
                       order by ‹用于排序的列名›) as 列别名
from 表名；

2.1 topN相关

SC表

以SC表为例，求在每个课程内按成绩排名

SELECT *,row_number() over(partition by CNO order by SCGRADE desc) as d
from SC;

现在，改求每个课程内的前两名成绩等信息

select * 
from (SELECT *,row_number() over(partition by CNO order by SCGRADE desc) as d
      from SC) t
where d <= 2;

这里必须使用from子查询，然后再用where进行过滤
套用公式：

select *
from (
  select *,row_number() over (partition by 要分组的列名 order by 要排序的列名 desc)
  as 列别名 from 原表名
） as 表别名 
where ranking <= N；

2.2 组内比较相关

窗口函数的另一个使用场景，求每个课程的平均成绩
1.使用group by

select cno,avg(SCGRADE) as a
from sc
group by cno;

2.使用窗口函数

select *,avg(SCGRADE) over(partition by cno) as a 
from sc;

区别于order by：

select *,avg(SCGRADE) over(order by cno) as a 
from sc;

求每个课程内成绩高于该科目平均成绩的学生名单
1.使用group by

select *
from sc
left join (select cno,avg(SCGRADE) as a
           from sc
           group by cno) t
on sc.cno = t.cno
where sc.SCGRADE > t.a;

需要使用表联结才能完成

2.使用窗口函数

select *
from (select *,avg(SCGRADE) over(partition by cno) as a from sc) t
where scgrade > a;

2.3 窗口函数的移动平均

<窗口函数>  over
（partition by <用于分组的列名>
order by <用于排序的列名>
rows <N preceding/N following/current row>）

例如：

select *, avg(成绩) over (
order by 学号 rows 2 preceding
) as current_avg 
from 班级表;