窗口函数Window Function是 MySQL 8.0 引入的一项核心功能专为保留原始行数的复杂数据分析而设计-9-20。 什么是窗口函数与将多行数据折叠成一行的普通聚合函数如配合GROUP BY使用不同--1窗口函数会对每一行数据都返回一个计算结果原始数据的行数保持不变--8。可以将GROUP BY和窗口函数中的PARTITION BY进行对比来帮助理解-8GROUP BY将数据分组后每组只返回一条汇总结果。PARTITION BY将数据分组即“分区”但不合并行窗口函数在每个分区内独立计算并为每一行返回结果-8。一个经典的比喻是聚合函数像计算“全班平均分”只给你一个数字窗口函数则能在每个学生的成绩单旁加一列“全班平均分”-。 窗口函数的核心语法窗口函数通过OVER()子句来定义其操作的“窗口”数据范围--3SELECT function_name(...) OVER ( [PARTITION BY partition_expression, ...] -- 1. 分区定义分组 [ORDER BY order_expression [ASC|DESC], ...] -- 2. 排序定义顺序 [frame_clause] -- 3. 框架定义窗口范围可选 ) FROM ...PARTITION BY可选用于将数据划分为不同的组分区。若不指定则所有数据视为一个分区-8。ORDER BY定义分区内数据的处理顺序对于排名函数RANK等是必需的-8。frame_clause可选用于在分区内定义一个动态移动的子集框架进一步精细控制函数计算所涉及的行范围-。️ 常用窗口函数分类1. 排名函数 (Ranking Functions)用于为分区内的每一行生成一个排名或序号。函数描述特点ROW_NUMBER()为每一行分配一个唯一的序号-无论值是否相同序号都连续且唯一-9。RANK()计算排名相同值排名相同相同值会跳过后续排名如1,2,2,4--9。DENSE_RANK()计算排名相同值排名相同相同值不跳过后续排名如1,2,2,3--9。示例为每个部门内的员工按薪资从高到低分配排名-20。SELECT department, name, salary, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as row_num, RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank_num, DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dense_rank_num FROM employees;2. 偏移函数 (Offset Functions)用于访问同一分区内其他行的数据而不需要自连接-。函数描述LAG(expr, offset, default)访问当前行之前第offset行的数据-。LEAD(expr, offset, default)访问当前行之后第offset行的数据-。示例查看每位员工及其前一位员工的薪资-20。SELECT name, salary, LAG(salary, 1) OVER (PARTITION BY department ORDER BY salary) as prev_salary FROM employees;3. 聚合函数 (Aggregate Functions) 作为窗口函数大部分聚合函数如SUM,AVG,MAX,MIN,COUNT都可以用作窗口函数--9。结合ORDER BY和框架子句可以实现强大的累计和移动计算-3。框架子句 (Frame Clause)精准控制窗口范围这是窗口函数最灵活的部分用于在当前分区内定义一个动态的“滑动窗口”-。ROWS基于物理行数进行偏移--8。RANGE基于逻辑值如日期范围进行偏移--8。框架范围由起点frame_start和终点frame_end定义-35。常用边界有-8UNBOUNDED PRECEDING分区第一行n PRECEDING当前行前 n 行CURRENT ROW当前行n FOLLOWING当前行后 n 行UNBOUNDED FOLLOWING分区最后一行默认框架当存在ORDER BY时默认框架为RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW从分区开头到当前行-8。示例计算每个员工的3行移动平均薪资自己、前一行、后一行-20。SELECT name, salary, AVG(salary) OVER (ORDER BY salary ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING) as moving_avg FROM employees;示例计算每个部门的累计薪资总额。SELECT name, salary, SUM(salary) OVER (PARTITION BY department ORDER BY salary ROWS UNBOUNDED PRECEDING) as cum_salary FROM employees;注ROWS UNBOUNDED PRECEDING是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW的简写️ 命名窗口 (Named Windows)简化重复定义当一个复杂的窗口定义相同的PARTITION BY和ORDER BY被多个函数重复使用时可以使用WINDOW子句为其命名-11。sql复制下载SELECT name, salary, ROW_NUMBER() OVER w as row_num, RANK() OVER w as rank_num FROM employees WINDOW w AS (PARTITION BY department ORDER BY salary DESC); -- 定义一次多次引用-11 窗口函数 vs. 普通聚合函数对比维度普通聚合函数 ( GROUP BY)窗口函数 ( OVER())结果行数减少每组输出一行-不变每行都输出一个结果-主要用途数据汇总、报表统计-数据分析、排名、同比/环比计算-9典型场景计算每个部门的平均薪资计算每个员工薪资在部门内的排名 实战演练结合你的需求窗口函数可以完美替代你之前尝试的复杂自连接使SQL更简洁高效。示例1找出每个用户最早的下单记录这可以使用ROW_NUMBER()对每个用户的订单按时间排序然后取序号为1的记录。sql复制下载WITH ranked_orders AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY order_date ASC) as rn FROM orders ) SELECT * FROM ranked_orders WHERE rn 1;示例2计算每个用户的次日留存率利用LAG()或LEAD()可以轻松访问用户的前后登录记录判断是否连续登录。sql复制下载WITH user_logins AS ( SELECT player_id, event_date, LAG(event_date) OVER (PARTITION BY player_id ORDER BY event_date) as prev_login_date FROM Activity ) SELECT ROUND( COUNT(DISTINCT CASE WHEN DATEDIFF(event_date, prev_login_date) 1 THEN player_id END) / COUNT(DISTINCT player_id), 2) AS retention_rate FROM user_logins;总的来说窗口函数是进行复杂数据查询和分析的利器非常值得掌握。