2025年mysql窗口函数lag（mysql窗口函数rank）

大家好，我是讯享网，很高兴认识大家。

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

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

一、窗口函数：

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

计算移动平均

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

二、面试题

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

复习知识点：窗口函数

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天内后一天比前一天平均时间差

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

MySQL的窗口函数 mysql窗口函数lag_MySQL的窗口函数
讯享网

MySQL的窗口函数 mysql窗口函数lag_数据库_02

MySQL的窗口函数 mysql窗口函数lag_数据分析_03

计算移动平均

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

MySQL的窗口函数 mysql窗口函数lag_sql_04

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

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

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

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

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

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

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

MySQL的窗口函数 mysql窗口函数lag_MySQL的窗口函数_05

MySQL的窗口函数 mysql窗口函数lag_数据库_06

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

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

percent_rank():

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

cume_dist():

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

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：case-when

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

2025年mysql窗口函数lag（mysql窗口函数rank）

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

计算移动平均

相关推荐