窗口函数,也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。
mysql中的窗口函数主要用来解决两类问题:
1.排名问题:比如每个班级按数学成绩排名
2.TopN问题:比如找出每个班级排名前N的同学
优势:
1.同时具有分组和排序的功能
2.不减少原表的行数
基本语法:
partition by 当不定分组时可省略
<窗口函数> over (partition by <用于分组的列名>
order by <用于排序的列名>)
常用窗口函数
1.rank
select *,rank() over (partition by 班级 order by 成绩 desc) as ranking
from 班级表
rank()是排序函数
partition by用来对表分组
order by子句的功能是对分组后的结果进行排序,默认是按照升序(asc)排列。
在本例中(order by 成绩 desc)是按成绩这一列排序,加了desc关键词表示降序排列。
如果有并列名次的行,会占用下一名次的位置。(比如三个人分数都是最高,那三个人并列第一,下一个人第四)
2.dense_rank
如果有并列名次的行,不占用下一名次的位置。(比如三个人分数都是最高,那三个人并列第一,下一个人第二)
3.row_number
不考虑并列名次的情况(比如三个人相同名次直接第一第二第三名)
4.聚合函数作为窗口函数
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 班级表;
聚和窗口函数和上面提到的专用窗口函数用法完全相同,只需要把聚合函数写在窗口函数的位置即可,但是函数后面括号里面不能为空,需要指定聚合的列名。