阅读之前看这里👉：博主是正在学习数据分析的一员，博客记录的是在学习过程中一些总结，也希望和大家一起进步，在记录之时，未免存在很多疏漏和不全，如有问题，还请私聊博主指正。

MYSQL常见面试题之——窗口函数知识总结

• 一、窗口函数：

• 1.窗口函数的基本用法：
• 2.窗口函数与普通聚合函数的区别：

• 计算移动平均

• 3.（面试考点）序号函数:row_number(),rank(),dense_rank()的区别
• 4.分布函数:percent_rank(),cume_dist()
• 5.前后函数:lag(expr,n),lead(expr,n)

• 二、面试题

• 1.用户行为分析
• 2.学生成绩分析

复习知识点：窗口函数

窗口函数也称为OLAP函数，OLAP 是OnLine Analytical Processing 的简称，意思是对数据库数据
进行实时分析处理。例如，市场分析、创建财务报表、创建计划等日常性商务工作。窗口函数就是为了实现OLAP 而添加的标准SQL 功能。

over关键字用来指定函数执行的窗口范围,若后面括号中什么都不写,则意味着窗口包含满足WHERE条件的所有行，窗口函数基于所有行进行计算；如果不为空,则支持以下4中语法来设置窗口。

①window_name:给窗口指定一个别名。如果SQL中涉及的窗口较多,采用别名可以看起来更清晰易读

②partition by子句：窗口按照哪些字段进行分组,窗口函数在不同的分组上分别执行

③order by子句：按照哪些字段进行排序,窗口函数将按照排序后的记录顺序进行编号

④frame子句：frame是当前分区的一个子集,子句用来定义子集的规则,通常用来作为滑动窗口使用

①聚合函数是将多条记录聚合为一条；窗口函数是每条记录都会执行，有几条记录执行完还是几条。

窗口函数兼具之前我们学过的GROUP BY 子句的分组功能以及ORDER BY 子句的排序功能。但是，PARTITION BY子句并不具备GROUP BY 子句的汇总功能。因此，使用RANK 函数并不会减少原表中 记录的行数，结果中仍然包含8 行数据。

窗口函数兼具分组和排序两种功能。

②聚合函数也可以用于窗口函数。

原因就在于窗口函数的执行顺序（逻辑上的）是在FROM，JOIN，WHERE，GROUP　BY，HAVING之后，在ORDER　BY，LIMIT，SELECT　DISTINCT之前。它执行时GROUP　BY的聚合过程已经完成了，所以不会再产生数据聚合。

注:窗口函数是在where之后执行的，所以如果where子句需要用窗口函数作为条件，需要多一层查询，在子查询外面进行,例如:求30天内后一天比前一天平均时间差

作为窗口函数使用的聚合函数

所有的聚合函数都能用作窗口函数，其语法和专用窗口函数完全相同。但大家可能对所能得到的结果还没有一个直观的印象，所以我们还是通过具体的示例来学习。

我们得到的并不仅仅是合计值，而是按照ORDER BY 子句指定的product_id 的升序进行排列，计算出商品编号“小于自己”的商品的销售单价的合计值。因此，计算该合计值的逻辑就像金字塔堆积那样，一行一行逐渐添加计算对象。在按照时间序列的顺序，计算各个时间的销售额总额等的时候，通常都会使用这种称为累计的统计方法。使用其他聚合函数时的操作逻辑也和本例相同。例如，使用AVG 来代替SELECT 语句中的SUM:

从结果中我们可以看到，current_avg 的计算方法确实是计算平均值的方法，但作为统计对象的却只是“排在自己之上”的记录。像这样以“自身记录（当前记录）”作为基准进行统计，就是将聚合函数当作窗口函数使用时的最大特征。

计算移动平均

窗口函数就是将表以窗口为单位进行分割，并在其中进行排序的函数。其实其中还包含在窗口中指定更加详细的汇总范围的备选功能，该备选功能中的汇总范围称为框架。
例：指定“最靠近的3行”作为汇总对象

• 指定框架（汇总范围）
  我们将上述结果与之前的结果进行比较，可以发现商品编号为“0004”的“菜刀”以下的记录和窗口函数的计算结果并不相同。这是因为我们指定了框架，将汇总对象限定为了“最靠近的3 行”。

这里我们使用了**ROWS（“行”）和PRECEDING（“之前”）**两个关键字，将框架指定为“截止到之前~ 行”，因此“ROWS 2 PRECEDING”就是将框架指定为“截止到之前2 行”，也就是将作为汇总对象的记录限
定为如下的“最靠近的3 行”。

● 自身（当前记录）
● 之前1行的记录
● 之前2行的记录

也就是说，由于框架是根据当前记录来确定的，因此和固定的窗口不同，其范围会随着当前记录的变化而变化。

这样的统计方法称为移动平均（moving average）。由于这种方法在希望实时把握“最近状态”时非常方便，因此常常会应用在对股市趋势的实时跟踪当中。

使用关键字FOLLOWING（“之后”）替换PRECEDING，就可以指定“截止到之后~ 行”作为框架了。

将当前记录的前后行作为汇总对象
如果希望将当前记录的前后行作为汇总对象时，就可以同时使PRECEDING（“之前”）和FOLLOWING（“之后”）关键字来实现。

例：将当前记录的前后行作为汇总对象

在上述代码中，我们通过指定框架，将“1 PRECEDING”（之前1 行）和“1 FOLLOWING”（之后1 行）的区间作为汇总对象。具体来说，就是将如下3 行作为汇总对象来进行计算：

ROW_NUMBER():顺序排序——1、2、3

RANK():并列排序，跳过重复序号——1、1、3

DENSE_RANK():并列排序，不跳过重复序号——1、1、2
详见：MySQL数据库知识要点归纳和总结中窗口排序函数

percent_rank():

每行按照公式(rank-1) / (rows-1)进行计算。其中，rank为RANK()函数产生的序号，rows为当前窗口的记录总行数

cume_dist():

分组内小于、等于当前rank值的行数 / 分组内总行数 eg:查询小于等于当前成绩（score）的比例

用途：返回位于当前行的前n行（LAG(expr,n)）或后n行（LEAD(expr,n)）的expr的值
应用场景：查询前1名同学的成绩和当前同学成绩的差值

6.头尾函数：FIRST_VALUE(expr),LAST_VALUE(expr)
用途：返回第一个（FIRST_VALUE(expr)）或最后一个（LAST_VALUE(expr)）expr的值
应用场景：截止到当前成绩，按照日期排序查询第1个和最后1个同学的分数

表1——用户行为表tracking_log，大概字段有（user_id‘用户编号’,opr_id‘操作编号’,log_time‘操作时间’）如下所示：

问题：
1.统计每天符合以下条件的用户数：A操作之后是B操作，AB操作必须相邻

分析：
（1）统计每天，所以需要按天分组统计求和
（2）A操作之后是B，且AB操作必须相邻，那就涉及一个前后问题，所以想到用窗口函数中的lag()或lead()。

解答：

2.统计用户行为序列为A-B-D的用户数,其中:A-B之间可以有任何其他浏览记录(如C,E等),B-D之间除了C记录可以有任何其他浏览记录(如A,E等)

表：Enrollments

1.查询每位学生获得的最高成绩和它所对应的科目，若科目成绩并列，取 course_id 最小的一门。查询结果需按 student_id 增序进行排序。

分析：因为需要最高成绩和所对应的科目，所以可采用窗口函数排序分组取第一个

按每位学生的成绩排名

取其中最高的成绩

解法2：IN 解法
先取最大成绩

然后取成绩在最大成绩之中的学生的最小课程号的课程

2.查询每一科目成绩最高和最低分数的学生,输出course_id,student_id,score
我们可以按科目查找成绩最高的同学和最低分的同学，然后利用union连接起来

解法2：case-when

读到这里的同学，相信大家对于窗口函数有了深刻的认识和掌握了吧。

————————————————————————————————————————————————

博主码字不易，大家关注点个赞转发再走呗 ，您的三连是对我创作的最大支持^ - ^

参考：《SQL基础教程》