SQL 实战：窗口函数进阶 – 实现复杂滑动窗口与动态累计计算

窗口函数是 SQL 中非常强大的工具，能够在不改变原始数据粒度的情况下，动态进行排名、累计、滑动平均以及环比同比计算。
在实际业务场景中，窗口函数常用于构建复杂的时间序列分析，如滚动累计、移动平均、同比/环比增长等。

本文将深入探讨窗口函数的高级用法，通过具体案例展示如何利用 LAG()、LEAD()、NTILE() 和 CUME_DIST() 解决滑动窗口和动态累计计算问题。

一、窗口函数回顾与高级函数简介

1. 窗口函数核心概念

• 窗口函数 是一种在结果集中按分区或按顺序对数据进行计算的函数，返回的结果与原始表数据具有相同的行数。
• 与聚合函数不同，窗口函数不折叠行，而是在每一行上执行计算。

2. 常用窗口函数及其功能

二、实战案例：滑动窗口与动态累计计算

案例 1：计算滚动 3 个月的销售额总和

需求描述：
在电商系统中，按月份统计用户的订单销售额，并计算滚动 3 个月的累计销售额。

表结构 sales

SQL 实现

SELECT user_id,  
       sale_date,  
       amount,  
       SUM(amount) OVER (
           PARTITION BY user_id  
           ORDER BY sale_date  
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS rolling_3m_sum  
FROM sales;

查询结果

解释：

• ROWS BETWEEN 2 PRECEDING AND CURRENT ROW 定义了一个滚动窗口，包含当前行及前两行的数据。
• SUM(amount) 在该窗口内累计销售额，形成滚动 3 个月销售额总和。

案例 2：计算同比增长率

需求描述：
统计每个用户按月份的销售额，并计算与上个月相比的销售增长率（环比）和同比增长率（去年同月）。

SQL 实现

SELECT user_id,  
       sale_date,  
       amount,  
       LAG(amount, 1) OVER (
           PARTITION BY user_id  
           ORDER BY sale_date
       ) AS prev_month_amount,  
       ROUND((amount - LAG(amount, 1) OVER (
           PARTITION BY user_id ORDER BY sale_date
       )) / LAG(amount, 1) OVER (
           PARTITION BY user_id ORDER BY sale_date
       ) * 100, 2) AS month_growth_rate  
FROM sales;

查询结果

解释：

• LAG() 函数获取前一行的销售额，用于计算环比增长率。
• ROUND() 保留两位小数，NULL 表示首月无前一月数据。

案例 3：按销售额分位排名（四分位数）

需求描述：
将用户的销售额按四分位数进行排名（将用户数据分为 4 组，每组约占 25%）。

SQL 实现

SELECT user_id,  
       amount,  
       NTILE(4) OVER (
           ORDER BY amount DESC
       ) AS quartile  
FROM sales;

查询结果

解释：

• NTILE(4) 将数据平均分为 4 份，金额最高的前 25% 用户归为第 1 组，依次类推。

总结

• 滑动窗口和滚动累计常用于时间序列分析，如销售额、活跃用户等。
• 使用 LAG() 和 LEAD() 可以轻松实现环比和同比计算。
• NTILE() 和 CUME_DIST() 是分布分析的利器，适合用户分层和分位排名场景。