这是我见过最简单易懂的sql窗口分析函数使用教程了[赞][赞][赞]

上一篇：「sql笔记」left join 还是right join?

一、理解

Mysql8.0开始支持窗口函数，极大增强了Mysql的统计性能，再也不会被Oracle等鄙视了。Mysql8.0还增加了其他实用的高级性能，关注公众号后续文章进行了解。

1、使用场景

可从两个角度来了解和分辨需要使用窗口函数的场景。

（1）要查询的数据有n组性质相同的数据集（订单表可以按品类分成n组、按日期分成n组等），对每组组内的数据都需要做相同的统计计算。比如各品类的销售额前3名的商品分别是？比如各部门出勤率最好的3位员工是？

（2）符合以下5类窗口函数的代表的统计意义

排序函数：row_number() 、rank()、dense_rank()，对每组组内进行排序。

分布函数：percent_rank()、cume_dist()，对每组组内的个体在组内占比或累计占比。

绝对位置函数：first_value()、last_value()、nth_value()，对每组组内的第1位、最后1位、第n位的值。

相对位置函数：lag()、lead()，对每组内的某字段的前n行和后n行的值。

分桶函数：ntile(x)，将每组内的行，再分别分成x个组（桶）。

此前版本的Mysql没有窗口函数，要解决此类问题，会引入变量到sql中，很复杂。现在好啦，最方便的窗口函数可以使用了。

2、理解窗口

窗口可以理解为上面描述的把待分析数据分为n组的组或集合。每组内的每条数据都要在此窗口内执行同一个函数。窗口函数运行后，数据行数保持不变。

以下示例，每个品类的商品按照金额从大到小的排名。金额排名这列就是用窗口函数计算出来的，各窗口内的数据行数保持不变，零食类还是2行，水果类还是3行。

row_number() over(partition by 品类 order by 金额 desc) as 金额排名

当不分组，而是全部数据当作1组来对待，此时窗口只有1个，单一窗口。

row_number() over(order by 金额 desc) as 金额排名

二、函数介绍

1、排序函数

row_number()，1、2、3、4，重复值的序号也是连续+1，不会有重复结果。

rank()，1、1、3、4，有相同值时，序号可能不连续。

dense_rank()，1、1、2、3，有相同值时，序号也是连续的。

2、分布函数

3、相对位置函数

4、绝对位置函数

5、分桶函数

6、聚合函数参与窗口计算

三、特殊窗口

1、单一窗口

在理解窗口章节中已经提及过，over()中不使用pratition by，即where出来的数据作为1组。

2、细分窗口

3、滑动窗口

current row，边界是当前行，一般和其他范围关键字一起使用

rows x preceding/following，边界是当前行减去x行，边界是当前行加上x行

rows unbounded preceding/following，边界是分组的第一行，边界是分组的最后一行

range interval 7 day preceding/following，当前行日期向前、向后7天纳入计算

窗口函数很神奇，很强悍，可以把复杂的逻辑封装起来，简洁调用。对于数据分析师，这是打怪升级过程中必须掌握的，会让你的数据加工提效n倍。

最后提一个问题，本文案例均是partition by和order by一个字段，试问，over()里可以partition by几个字段，order by 几个字段？关注明天文章揭晓答案。

上一篇：「sql笔记」left join 还是right join?