MySQL——窗口函数

1.窗口函数解释

（1）窗口函数的基本语法：<窗口函数> over(partition by <用于分组的列名> order by <用于排序的列名>)
（2）<窗口函数>的位置，可以放以下两种函数：
         ① 专用窗口函数，包括后面要讲到的rank, dense_rank, row_number等专用窗口函数
         ② 聚合函数，如sum. avg, count, max, min等
（3）窗口函数原则上只能写在select子句中。

2.窗口函数的使用

（1）专用窗口函数
例如下表：

create table t_grade(
id int(10) not null,
class int (10) not null,
score int (10) not null
);
insert into t_grade values(1,1,86);
insert into t_grade values(2,1,95);
insert into t_grade values(3,2,89);
insert into t_grade values(4,1,83);
insert into t_grade values(5,2,86);
insert into t_grade values(6,3,92);
insert into t_grade values(7,3,86);
insert into t_grade values(8,1,88);

题目：在每个班级内，按照分数排名，得到下面的结果：

使用的sql语句为：

select id,class,score,
rank() over(partition by class order by score) rk
from t_grade

或者dense_rank()：

select id,class,score,
dense_rank() over(partition by class order by score) rk
from t_grade

或者row_number()：

select id,class,score,
row_number() over(partition by class order by score) rk
from t_grade

rank(), dense_rank(), row_number()他们的不同之处在于：

select *,
   rank() over (order by score desc) as rk,
   dense_rank() over (order by score desc) as dense_rk,
   row_number() over (order by score desc) as row_num
from t_grade

不分组只排序时，
①rank()函数： 相等的值排名相同，图中倒数第二行至倒数第四行；若有相等的值，序号1~n 不连续，相等的排名序号会占用原本的序号。（多个最高分排名：只有一个第一；次高分排名：(最高分人数+1)名）
②dense_rank()： 相等的值排名相同，图中倒数第二行至倒数第四行；若有相等的值，序号1~n 连续，相等的排名序号不会占用原本紧挨着的序号。（多个最高分排名：都是第一；次高分排名：第二）
③row_number()： 对相等的值不进行区分，如果有相等的值，序号1~n 连续。（排名唯一，即使分数相等，排名也不同）
（2）聚合函数作为窗口函数
聚和窗口函数和上面提到的专用窗口函数用法完全相同，只需要把聚合函数写在窗口函数的位置即可，但是函数后面括号里面不能为空，需要指定聚合的列名。
例如：

select *,
   sum(score) over (order by id) as s_sum,
   avg(score) over (order by id) as s_avg,
   count(score) over (order by id) as s_count,
   max(score) over (order by id) as s_max,
   min(score) over (order by id) as s_min
from t_grade

得到结果为：

如上图，聚合函数sum在窗口函数中，是对自身记录、及位于自身记录以上的数据进行求和的结果。不仅是sum求和，平均、计数、最大最小值，也是同理，都是针对自身记录、以及自身记录之上的所有数据进行计算。
好处为：聚合函数作为窗口函数，可以在每一行的数据里直观的看到，截止到本行数据，统计数据是多少（最大值、最小值等）。同时可以看出每一行数据，对整体统计数据的影响。
（3）窗口函数和group by(),order by()的区别
group by分组汇总后改变了表的行数，一行只有一个类别。而partiition by和rank函数不会减少原表中的行数。

题目：统计每个班的人数：
group by(),order by()：

select class,count(id)
from t_grade
group by class
order by class

窗口函数：

select class,
count(id) over(partition by class order by class) num
from t_grade