各位老铁，各位未来的数据分析大师，大家好！我是今天的主讲人，咱们今天聊聊MySQL 8.0 窗口函数，这玩意儿，绝对是SQL进阶路上的一大利器！
别害怕，虽然名字听起来高大上，什么“新范式”，但其实理解起来并不难。 就像你每天早上打开窗户看看今天的天气一样，窗口函数就是给你的SQL查询打开一扇“窗口”，让你能看到更多的数据，进而做出更骚的操作！
一、 啥是窗口函数？别跟我拽文，说人话！
咱们先忘掉官方的定义，用最通俗的语言来说：
窗口函数，就是在SQL查询里，对一个数据集合（也就是“窗口”）进行计算，然后把计算结果返回到每一行数据里。 注意啊，是每一行！ 它不会像GROUP BY那样把数据聚合起来，而是给每一行都附加一些额外的信息。
这就像啥呢？ 就像你在看电视节目，旁边有个实时弹幕，告诉你当前剧情的各种信息，但并不影响你看节目本身。 窗口函数就像这个弹幕，告诉你一些关于当前行数据，以及和它相关的其他数据的计算结果。
二、 窗口函数的基本语法：套路要记牢！
窗口函数的基本语法是这样的：
函数名(参数) OVER (PARTITION BY 列名 ORDER BY 列名 ASC/DESC rows/range between frame_start and frame_end)看着有点吓人？ 别慌，咱们一点点拆解：
函数名(参数): 这部分就是你要用的窗口函数，比如RANK(),ROW_NUMBER(),SUM(),AVG()等等，后面我们会详细讲。OVER(): 这是窗口函数的灵魂！ 告诉数据库，这是一个窗口函数，不是普通的聚合函数。PARTITION BY 列名: 这部分是用来分组的，和GROUP BY类似。 它可以把数据分成多个“窗口”，每个窗口里的数据分别进行计算。 如果省略，就表示整个结果集是一个窗口。 你可以理解为，把整个班级分成几个学习小组。ORDER BY 列名 ASC/DESC: 这部分是用来排序的，决定了窗口函数计算的顺序。 比如你想算每个学生的排名，就得按分数排序。 可以理解为，在每个学习小组里，按照考试成绩排名。rows/range between frame_start and frame_end: 这部分是定义“窗口”大小的，叫做“窗口帧”。 它决定了当前行数据要和哪些行的数据一起参与计算。 如果省略，就表示整个窗口（也就是PARTITION BY分出来的组）都是窗口帧。
三、 窗口函数的种类：十八般武艺，样样精通！
MySQL 8.0 提供了多种窗口函数，咱们挑几个常用的讲讲：
-
排名函数：
RANK(),DENSE_RANK(),ROW_NUMBER()这三个函数都是用来排名的，但有点区别：
RANK(): 会跳过重复的排名。 比如有两个人并列第一，那么下一个排名就是第三。DENSE_RANK(): 不会跳过重复的排名。 比如有两个人并列第一，那么下一个排名就是第二。ROW_NUMBER(): 不管有没有重复，都会生成唯一的排名。
举个例子：
假设我们有一张
students表，包含name(学生姓名) 和score(分数) 两列。CREATE TABLE students ( name VARCHAR(20), score INT ); INSERT INTO students (name, score) VALUES ('张三', 80), ('李四', 90), ('王五', 90), ('赵六', 70), ('田七', 80), ('周八', 95);我们用这三个函数来给学生排名：
SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank, DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank, ROW_NUMBER() OVER (ORDER BY score DESC) AS row_number FROM students;查询结果如下：
name score rank dense_rank row_number 周八 95 1 1 1 李四 90 2 2 2 王五 90 2 2 3 张三 80 4 3 4 田七 80 4 3 5 赵六 70 6 4 6 可以看到，
RANK()会跳过排名 3，DENSE_RANK()不会跳过，而ROW_NUMBER()则生成了唯一的排名。 -
聚合函数：
SUM(),AVG(),MIN(),MAX(),COUNT()这些函数我们都很熟悉了，但是结合
OVER()子句，它们就可以变成窗口函数，计算累计值、平均值等等。继续用上面的
students表，我们可以计算每个学生的累计总分：SELECT name, score, SUM(score) OVER (ORDER BY score DESC) AS cumulative_sum FROM students;查询结果如下：
name score cumulative_sum 周八 95 95 李四 90 185 王五 90 275 张三 80 355 田七 80 435 赵六 70 505 可以看到，
cumulative_sum列是按照分数降序排列的累计总分。如果我们想计算每个学生的移动平均分（比如计算过去两名的平均分），可以使用
ROWS BETWEEN子句来定义窗口帧：SELECT name, score, AVG(score) OVER (ORDER BY score DESC ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_average FROM students;查询结果如下：
name score moving_average 周八 95 95 李四 90 92.5 王五 90 91.6667 张三 80 86.6667 田七 80 83.3333 赵六 70 76.6667 ROWS BETWEEN 2 PRECEDING AND CURRENT ROW表示窗口帧包括当前行和它前面的两行。 -
取值函数：
LAG(),LEAD(),FIRST_VALUE(),LAST_VALUE()这些函数可以用来获取窗口中其他行的数据。
LAG(列名, offset, default): 获取当前行之前第offset行的列名的值。 如果前面没有足够的行，就返回default值。LEAD(列名, offset, default): 获取当前行之后第offset行的列名的值。 如果后面没有足够的行，就返回default值。FIRST_VALUE(列名): 获取窗口中第一行的列名的值。LAST_VALUE(列名): 获取窗口中最后一行的列名的值。
还是用
students表，我们可以获取每个学生的前一名和后一名的分数：SELECT name, score, LAG(score, 1, NULL) OVER (ORDER BY score DESC) AS prev_score, LEAD(score, 1, NULL) OVER (ORDER BY score DESC) AS next_score FROM students;查询结果如下：
name score prev_score next_score 周八 95 NULL 90 李四 90 95 90 王五 90 90 80 张三 80 90 80 田七 80 80 70 赵六 70 80 NULL 可以看到，
prev_score列是前一名的分数，next_score列是后一名的分数。 第一名的prev_score和最后一名的next_score都是NULL。
四、 窗口函数实战：秀出你的操作！
光说不练假把式，咱们来几个实际的例子，看看窗口函数怎么用：
-
计算每个部门的销售额排名
假设我们有一张
sales表，包含department(部门) 和amount(销售额) 两列。CREATE TABLE sales ( department VARCHAR(20), amount INT ); INSERT INTO sales (department, amount) VALUES ('销售部', 1000), ('销售部', 1200), ('销售部', 800), ('技术部', 1500), ('技术部', 1300), ('市场部', 900), ('市场部', 1100);我们可以用窗口函数来计算每个部门的销售额排名：
SELECT department, amount, RANK() OVER (PARTITION BY department ORDER BY amount DESC) AS rank FROM sales;查询结果如下：
department amount rank 市场部 1100 1 市场部 900 2 技术部 1500 1 技术部 1300 2 销售部 1200 1 销售部 1000 2 销售部 800 3 可以看到，
rank列是每个部门内部的销售额排名。 -
计算每个用户的留存率
假设我们有一张
user_actions表，包含user_id(用户ID) 和action_date(操作日期) 两列。CREATE TABLE user_actions ( user_id INT, action_date DATE ); INSERT INTO user_actions (user_id, action_date) VALUES (1, '2023-01-01'), (1, '2023-01-07'), (1, '2023-01-15'), (2, '2023-01-01'), (2, '2023-01-05'), (3, '2023-01-01'), (4, '2023-01-01'), (4, '2023-01-08');我们可以用窗口函数来计算每个用户在第二天、第七天的留存率：
SELECT user_id, MIN(CASE WHEN DATEDIFF(action_date, first_date) = 1 THEN 1 ELSE 0 END) AS retained_day_2, MIN(CASE WHEN DATEDIFF(action_date, first_date) = 7 THEN 1 ELSE 0 END) AS retained_day_7 FROM ( SELECT user_id, action_date, FIRST_VALUE(action_date) OVER (PARTITION BY user_id ORDER BY action_date) AS first_date FROM user_actions ) AS subquery GROUP BY user_id;查询结果如下：
user_id retained_day_2 retained_day_7 1 0 1 2 1 0 3 0 0 4 0 1 retained_day_2列表示用户在第二天是否留存，retained_day_7列表示用户在第七天是否留存。 这里的关键是使用FIRST_VALUE()函数获取每个用户的首次操作日期，然后用DATEDIFF()函数计算操作日期与首次操作日期之间的天数。 -
查找连续登录的用户
假设我们有一张
login_logs表，包含user_id(用户ID) 和login_date(登录日期) 两列。CREATE TABLE login_logs ( user_id INT, login_date DATE ); INSERT INTO login_logs (user_id, login_date) VALUES (1, '2023-01-01'), (1, '2023-01-02'), (1, '2023-01-03'), (1, '2023-01-05'), (2, '2023-01-01'), (2, '2023-01-02'), (3, '2023-01-01'), (3, '2023-01-03');我们可以用窗口函数来查找连续登录 3 天及以上的用户：
SELECT DISTINCT user_id FROM ( SELECT user_id, login_date, DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY) AS date_group FROM login_logs ) AS subquery GROUP BY user_id, date_group HAVING COUNT(*) >= 3;查询结果如下：
user_id 1 这个查询比较复杂，咱们来解释一下：
ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date): 给每个用户的登录日期生成一个序号。DATE_SUB(login_date, INTERVAL ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY login_date) DAY): 用登录日期减去序号，得到一个日期分组。 如果用户连续登录，那么它们的日期分组就是一样的。- *`GROUP BY user_id, date_group HAVING COUNT() >= 3`:** 按照用户ID和日期分组进行分组，然后筛选出连续登录 3 天及以上的用户。
五、 窗口函数的注意事项：避坑指南！
- 性能问题: 窗口函数可能会影响查询性能，特别是对于大数据量的表。 所以要尽量避免在窗口函数中使用复杂的表达式，并注意优化查询。
- 窗口帧的定义: 窗口帧的定义要小心，不同的定义会产生不同的结果。 要根据实际需求选择合适的窗口帧。
- NULL 值的处理: 窗口函数对 NULL 值的处理方式可能和你想的不一样。 要注意 NULL 值对计算结果的影响。
- 不能在 WHERE 子句中使用: 窗口函数不能直接在
WHERE子句中使用。 如果需要根据窗口函数的结果进行筛选，可以使用子查询或者HAVING子句。
六、 总结：窗口函数，真香！
窗口函数是 MySQL 8.0 引入的一项强大的功能，它可以让你在 SQL 查询中进行更复杂的数据分析。 掌握窗口函数，可以让你少写很多代码，提高查询效率，并能解决很多以前难以解决的问题。
虽然窗口函数看起来有点复杂，但只要理解了它的基本语法和原理，多加练习，就能熟练掌握。 相信我，一旦你用习惯了窗口函数，就会发现它真香！
好了，今天的讲座就到这里。希望大家能从中学到一些东西，并在实际工作中运用起来。 记住，学习编程就像学习武功，光看秘籍是不行的，还要多练习才能真正掌握。 咱们下期再见！