一文搞定数分面试必考题之：窗口函数

小洛写在前面&＃xff1a;

窗口函数的主要作用是对数据进行分组排序、求和、求平均值、计数等。对于数据从业者来说&＃xff0c; sql窗口函数在实际工作中具备非常广泛的应用场景。可以大大的提高数据查询效率&＃xff0c;同时也是数据类相关岗位的面试/笔试的必考点。所以不论是在职的分析师&＃xff0c;还是准备找工作的同学&＃xff0c;都必须要牢牢掌握窗口函数的概念及用法。感谢群友饭小米的投稿&＃xff0c;接下来让我们详细了解一下窗口函数的前世今生吧。

 窗口函数基本语法

在窗口函数的基本语法中&＃xff0c;最重要的是去理解partition by&＃xff0c;partition by划分的范围被称为窗口&＃xff0c;这也是窗口函数的由来。其次是order by&＃xff0c;它决定着窗口范围内的数据以什么样的方式排序。下面的例子详细的介绍了窗口函数的基本语法和功能。

例一 代码如下

在上面的代码中可以看出&＃xff0c;是按照产品的类型去分组&＃xff0c;在组内以价格的顺序升序排列&＃xff0c;运行的结果如下。&＃xff08;rank的排序下面会单独说&＃xff09;

至于窗口函数与group by的区别&＃xff1a;

• 两个order by的区别&＃xff0c;第一个窗口函数中的order by只是决定着窗口里的数据的排序方式&＃xff0c;第二个普通的order by决定查询出的数据以什么样的方式整体排序&＃xff1b;

• 窗口函数可以在保留原表中的全部数据之后&＃xff0c;可以对某些字段做分组排序或者计算&＃xff0c;而group by只能保留与分组字段聚合的结果&＃xff1b;

• 在加入窗口函数的基础上SQL的执行顺序也会发生变化&＃xff0c;具体的执行顺序如下&＃xff08;window就是窗口函数&＃xff09;&＃xff1b;

窗口函数类别

专用窗口函数例如rank、row_number、lag和lead等&＃xff0c;在窗口函数中有静态函数和动态函数的分类&＃xff0c;具体的划分如下。

作为窗口函数的聚合函数&＃xff0c;常见的聚合函数有sum、avg、max、min跟count。他们跟窗口函数组合到一起&＃xff0c;就会把聚合函数的功能和窗口函数组合在一起。

例二 代码及结果为

从上面的例子可以看出&＃xff0c;在没有partition by 的情况下&＃xff0c;是把整个表作为一个大的窗口&＃xff0c;SUM&＃xff08;&＃xff09;相当于向下累加&＃xff0c;AVG&＃xff08;&＃xff09;相当于求从第一行到当前行的平均值&＃xff0c;其他的聚合函数均是如此。

注意点&＃xff1a;

1 、在使用专用的窗口函数时&＃xff0c;例如rank、lag等&＃xff0c;rank&＃xff08;&＃xff09;括号里是不需要指定任何字段的&＃xff0c;直接空着就可以&＃xff1b;

2 、在使用聚合函数做窗口函数时&＃xff0c;SUM&＃xff08;&＃xff09;括号里必须有字段&＃xff0c;得指定对哪些字段执行聚合的操作。在学习的初期很容易弄混&＃xff0c;不同函数括号里是否需写相应的字段名&＃xff1b;

三种分组排序的区别-row_number、rank、dense_rank

• RANK-计算排序时&＃xff0c;如果存在相同位次的记录&＃xff0c;则会跳过之后的位次。有 3 条记录排在第 1 位时&＃xff1a;1 、1 、1 、4&＃xff1b;

• DENSE_RANK-同样是计算排序&＃xff0c;即使存在相同位次的记录&＃xff0c;也不会跳过之后的位次。有 3 条记录排在第 1 位时&＃xff1a;1 、1 、1 、2&＃xff1b;

• ROW_NUMBER-赋予唯一的连续位次,有 3 条记录排在第 1 位时&＃xff1a;1 、2 、3 、4&＃xff1b;

示例&＃xff0c;在下面的执行结果是以整个表作为窗口&＃xff0c;可以清楚的看出三种排序函数的不同之处。&＃xff08;如果想要唯一的排序就直接用row_number&＃xff09;

窗口函数进阶-滑动窗口函数

在写窗口函数时&＃xff0c;order by后面可以有参数&＃xff0c;rows/range 和preceding跟following&＃xff0c;在组合使用这些参数后&＃xff0c;窗口就会变成滑动窗口&＃xff0c;因为涉及到动态窗口&＃xff0c;所以在理解上比较抽象。

1、 Preceding

Rows 2 preceding 中文的意思是之前的两行&＃xff0c;preceding可以把它理解为不含当前行情况下截止到之前几行。根据上图可以看出在每一行&＃xff0c;都会求出当前行附近的3行(当前行&＃43;附近2行)数据的平均值&＃xff0c;这种方法也叫作移动平均。

2、Following

Rows 2 following 中文意思是之后的两行&＃xff0c;跟preceding正好相反&＃xff0c;Preceding是向前&＃xff0c;following是向后。

3、preceding跟following相结合

代码及运行结果为&＃xff1a;

从以上的运行结果可以看出是把每一行&＃xff08;当前行&＃xff09;的前一行和后一行作为汇总的依据。

窗口函数应用真题解析

1、topN问题或者组内排序问题

在实际的场景中&＃xff0c;我们会经常会遇到排序或者排名问题&＃xff0c;这个时候使用窗口函数会使问题变的简单。

求出每个课程的学生成绩排名&＃xff1a;

2、连续登录问题

假设有一张含两列(用户id、登陆日期)的表&＃xff0c;查询每个用户连续登陆的天数、最早登录时间、最晚登录时间和登录次数。

• 首先要对数据进行去重&＃xff0c;防止同一个用户一天之内出现连续登录的情况&＃xff1b;

• 假如一个用户是连续登录的话&＃xff0c;用login_time-窗口函数的排序后得到的日期应该是一样的&＃xff0c;连续登录的用户前后之间的时间差就是一个差值为1的等差数列&＃xff1b;

第一步&＃xff0c;先用row_number&＃xff08;&＃xff09;函数排序&＃xff0c;然后用登录日期减去排名&＃xff0c;得到辅助列日期&＃xff0c;如果辅助列日期是相同的话&＃xff0c;证明用户是连续登录。

运行的代码及结果为&＃xff1a;

第二步&＃xff0c;用user_id和辅助列作为分组依据&＃xff0c;分到一组的就是连续登录的用户。在每一组中最小的日期就是最早的登陆日期&＃xff0c;最大的日期就是最近的登陆日期&＃xff0c;对每个组内的用户进行计数就是用户连续登录的天数。

运行代码及结果为&＃xff1a;

若求解每个用户的最大登录天数。其实可以在以上的查询结果为基础&＃xff0c;利用聚合函数就可以求出最大的登录天数问题。假如求解连续登录5天的用户&＃xff0c;除了可以使用上述的方法&＃xff0c;还可以使用lead函数进行窗口偏移来进行求解。

示例&＃xff1a;数据还是上题中的数据&＃xff0c;求解连续登录五天的用户

第一步&＃xff0c;用lead函数进行窗口偏移&＃xff0c;查找每个用户5天后的登陆日期是多少&＃xff0c;如果是空值&＃xff0c;说明他没有登录。运行的代码为

在lead函数里&＃xff0c;为何偏移行数的参数设置为4而不是5呢&＃xff0c;这是因为求解的是连续登录5天的用户&＃xff0c;包括当前行在内一共是5行&＃xff0c;所以应该向下偏移4行。运行的结果如下&＃xff1a;

第二步&＃xff0c;用datediff函数计算 &＃xff08;日期-第五次登陆日期&＃xff09;&＃43;1是否等于5&＃xff0c;等于5证明用户是连续5天登录的&＃xff0c;为空值或者大于5都不是5天连续登陆的用户。

第三步&＃xff0c;用where设定条件&＃xff0c;差值&＃61;5筛选连续登录的用户。

二、三步运行的代码为&＃xff1a;

用lead函数求解连续登录的问题还有一个好处就是当表中的数据不在同一个月份时也可以完美的解决&＃xff0c;不用再考虑月份带来的影响。

熟练掌握本篇所提到的用法&＃xff0c;即可轻松应对绝大部分数据分析面试中的窗口函数考题&＃xff0c;窗口函数&＃xff0c;你了解了吗&＃xff1f;

感谢大家的阅读&＃xff0c;关注小洛的公众号&＃xff0c;一起交流数据分析话题~