窗口函数:也叫OLAP函数(Online Anallytical Processing,联机分析处理),可以对数据库数据进行实时分析处理。”窗口”可以理解为记录集合,窗口函数就是在满足某种条件的记录集合上执行的特殊函数。按照功能划分,可以把MySQL支持的窗口函数分为如下几类:
- 序号函数: row_number () / rank () / dense_rank()
- 分布函数: percent_rank() / cume_dist ( )
- 前后函数: lag () / lead ()
- 头尾函数: first_val () / last_val ()
- 其他函数: nth_value () / nfile ()
基本语法:
<窗口函数> over (partition by <用于分组的列名>
order by <用于排序的列名>)
因为窗口函数是对where或者group by子句处理后的结果进行操作,所以窗口函数原则上只能写在select子句中。
row_number()、 rank( )、dense_rank()的区别
- row_number():依次排序,不会出现相同排名
- rank():出现相同排名时,跳跃排序
- dense_rank( ):出现相同排名时,连续排序
SELECT *, rank() over ( ORDER BY score DESC ) AS ranking, dense_rank() over ( ORDER BY score DESC ) AS dese_rank, row_number() over ( ORDER BY score DESC ) AS row_num FROM score
加上PARTITION BY的查询结果:SELECT *, rank() over ( PARTITION BY cid ORDER BY score DESC ) AS ranking, dense_rank() over ( PARTITION BY cid ORDER BY score DESC ) AS dese_rank, row_number() over ( PARTITION BY cid ORDER BY score DESC ) AS row_num FROM score;