MySQL窗口函数那些实用技巧你真的掌握了吗，还是只会皮毛？

MySQL窗口函数那些实用技巧你真的掌握了吗,还是只会皮毛？

很多人学了窗口函数,可能就知道个ROW_NUMBER()、RANK()，顶多再加个算累计求和的SUM() OVER()，这就像你学开车，只会直行和倒车，遇到复杂的路况就傻眼了，今天我们就来聊聊那些能让你从“会一点”变成“真会用”的窗口函数实用技巧。

用LEAD()和LAG()轻松搞定环比、同比分析

（来源：常见的业务分析场景） 做数据分析，老板最常问的就是“这个月比上个月增长了多少？”或者“今年这个月比去年同月怎么样？”，如果你还在用表连接这种笨重的方法，那就太慢了。

• LAG(列名, N)：获取当前行之前第N行的数据，比如LAG(sales, 1)就是拿到上一期的销售额。
• LEAD(列名, N)：获取当前行之后第N行的数据，比如LEAD(sales, 1)就是拿到下一期的销售额。

举个例子,你有一张月度销售表sales_table，里面有month（月份）和amount（销售额）两列，你想看每个月的环比增长率（（本月-上月）/上月），一句SQL就能搞定：

SELECT
    month,
    amount AS 本月销售额,
    LAG(amount, 1) OVER (ORDER BY month) AS 上月销售额,
    (amount - LAG(amount, 1) OVER (ORDER BY month)) / LAG(amount, 1) OVER (ORDER BY month) AS 环比增长率
FROM sales_table;

同理,做同比分析（和去年同月比），你只需要把LAG(amount, 1)改成LAG(amount, 12)就行了，因为一年有12个月，这比用自连接（JOIN）把表和自己关联起来要清晰、高效得多。

用FIRST_VALUE()和LAST_VALUE()快速找到首尾值

（来源：分组内的极值查找需求） 我们不仅想知道每个分组内的最大值、最小值，还想知道这个最大值、最小值具体是多少，或者想以某个顺序第一个或最后一个值为基准进行比较。

• FIRST_VALUE(列名)：获取窗口内第一行的值。
• LAST_VALUE(列名)：获取窗口内最后一行的值。

这里有个大坑需要注意！默认情况下，LAST_VALUE()的窗口范围是ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW，意思是“从第一行到当前行”，这通常不是我们想要的，我们一般想要的是整个分组的最后一行，用LAST_VALUE()时，必须手动指定窗口范围：

SELECT
    employee_id,
    month,
    sales,
    FIRST_VALUE(sales) OVER (PARTITION BY employee_id ORDER BY month) AS 本年首月销售额,
    LAST_VALUE(sales) OVER (PARTITION BY employee_id ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS 本年最后一月销售额
FROM sales_records;

注意看LAST_VALUE里我们加上了ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING，这表示“从分区的第一行到最后一行”，这样才能正确取出每个销售员的最终销售额，这个细节不知道，LAST_VALUE()基本就等于白学。

用窗口函数给重复数据“瘦身”

（来源：实际数据清洗工作） 你可能会遇到数据重复的情况，比如由于系统原因，同一条记录被插入了多次，你想删除重复项，只保留一条（比如保留ID最小的那条），用窗口函数可以很优雅地标识出这些重复数据。

假设表users中email字段有重复，我们想给每个重复的邮箱标记一个序号，然后删除序号大于1的。

SELECT
    id,
    email,
    ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS row_num
FROM users;

在这个结果里,所有row_num = 1的就是我们要保留的唯一条目，row_num > 1的就是重复的可以删除的，这种方法在处理复杂重复逻辑时，比用GROUP BY和子查询更直观。

用AVG() OVER()创建移动平均线，平滑数据

（来源：金融、时间序列分析） 在看股票K线图时，经常有5日均线、10日均线，这就是移动平均，用来消除短期波动，看长期趋势，在MySQL里，用窗口函数可以轻松实现。

计算每个交易日收盘价的5日移动平均（包含当天）：

SELECT
    trade_date,
    close_price,
    AVG(close_price) OVER (ORDER BY trade_date ROWS BETWEEN 4 PRECEDING AND CURRENT ROW) AS ma_5
FROM stock_prices;

这里的ROWS BETWEEN 4 PRECEDING AND CURRENT ROW就是关键，它定义了一个窗口，包含当前行及之前的4行，正好5行数据来计算平均值，随着当前行移动，这个窗口也跟着移动，就形成了移动平均线。

总结一下

窗口函数真正的威力在于OVER()子句里的灵活定义：PARTITION BY（分组）、ORDER BY（排序）以及最重要的窗口框架（ROWS BETWEEN ...），只学会函数名，不了解窗口框架，就像给你一把枪却没给你子弹，下次当你写SQL时，如果发现自己在用复杂的自连接或者效率低下的子查询，不妨停下来想一想：“能不能用窗口函数更简单地解决？” 多练习这些技巧，你才能真正驾驭窗口函数，让它成为你数据分析中的一把利器。