目标：掌握 SQL 中分析函数（窗口函数）与聚合函数的组合使用，通过实际案例实现复杂业务需求，如同比、环比和趋势分析。
1. 分析函数与聚合函数的区别
- 聚合函数（Aggregate Functions）：对多行数据进行汇总，返回一个结果。常见的有
SUM、AVG、COUNT、MAX等。 - 分析函数（Analytic/Window Functions）：在不缩减行数的前提下，基于某个窗口执行计算。常见的有
SUM() OVER、RANK()、LEAD()、LAG()等。
2. 核心函数介绍
SUM() OVER：在特定窗口内累加数据，返回每一行对应窗口的累积值。AVG() OVER：在窗口内计算平均值，常用于移动平均。PERCENT_RANK()：计算当前行在窗口内的百分比排名。
3. 案例：计算用户每月销售额及同比、环比增长率
需求描述
- 计算每个用户在每个月的总销售额。
- 计算每个月的环比增长率（本月与上月相比）。
- 计算每个月的同比增长率（本月与去年同月相比）。
示例数据
sales 表结构：
| sale_id | user_id | sale_amount | sale_date |
|---|---|---|---|
| 1 | 101 | 500 | 2023-01-15 |
| 2 | 101 | 600 | 2023-02-10 |
| 3 | 101 | 700 | 2024-01-20 |
| 4 | 102 | 400 | 2023-01-18 |
| 5 | 102 | 450 | 2024-01-25 |
SQL 实现
WITH monthly_sales AS (SELECT user_id,DATE_FORMAT(sale_date, '%Y-%m') AS sale_month,SUM(sale_amount) AS total_salesFROM salesGROUP BY user_id, DATE_FORMAT(sale_date, '%Y-%m')
),
sales_with_trends AS (SELECT user_id,sale_month,total_sales,LAG(total_sales, 1) OVER (PARTITION BY user_id ORDER BY sale_month) AS previous_month_sales,LAG(total_sales, 12) OVER (PARTITION BY user_id ORDER BY sale_month) AS last_year_salesFROM monthly_sales
)
SELECT user_id,sale_month,total_sales,ROUND((total_sales - previous_month_sales) / NULLIF(previous_month_sales, 0) * 100, 2) AS month_over_month_growth,ROUND((total_sales - last_year_sales) / NULLIF(last_year_sales, 0) * 100, 2) AS year_over_year_growth
FROM sales_with_trends
ORDER BY user_id, sale_month;
代码解析
- 第一步（
monthly_sales）：按用户和月份汇总销售数据，计算每月销售总额。 - 第二步（
sales_with_trends）：- 使用
LAG()计算前一个月的销售额，计算环比。 - 使用
LAG()结合 12 个月偏移量计算去年的同月销售额，实现同比。
- 使用
- 最终结果：计算环比、同比增长率，
NULLIF防止除零错误。
结果示例
| user_id | sale_month | total_sales | month_over_month_growth | year_over_year_growth |
|---|---|---|---|---|
| 101 | 2023-01 | 500 | NULL | NULL |
| 101 | 2023-02 | 600 | 20.00 | NULL |
| 101 | 2024-01 | 700 | 16.67 | 40.00 |
4. 亮点解读
- 环比计算：通过
LAG()直接获取上个月数据，无需自联表。 - 同比计算：利用
LAG()向前偏移12个月，直观且高效。 - 窗口函数优势：保留所有行数据，且在不改变原始行的基础上计算额外指标。
5. 扩展思考
- 可以使用
LEAD()预测未来趋势或计算未来一个月的数据变化。 - 结合
PERCENT_RANK()分析各用户在销售额中的排名，实现销售精英筛选。 - 使用
NTILE(4)将用户按季度或销售额分组，分析不同等级用户的增长趋势。
这种 SQL 方案适合在业务系统中监控用户销售趋势，适用于电商、金融和 SaaS 产品的业务数据分析。