SQL 分析函数与聚合函数的组合应用

目标：掌握 SQL 中分析函数（窗口函数）与聚合函数的组合使用，通过实际案例实现复杂业务需求，如同比、环比和趋势分析。

---

1. 分析函数与聚合函数的区别

• 聚合函数（Aggregate Functions）：对多行数据进行汇总，返回一个结果。常见的有 SUM、AVG、COUNT、MAX 等。
• 分析函数（Analytic/Window Functions）：在不缩减行数的前提下，基于某个窗口执行计算。常见的有 SUM() OVER、RANK()、LEAD()、LAG() 等。

---

2. 核心函数介绍

• SUM() OVER：在特定窗口内累加数据，返回每一行对应窗口的累积值。
• AVG() OVER：在窗口内计算平均值，常用于移动平均。
• PERCENT_RANK()：计算当前行在窗口内的百分比排名。

---

3. 案例：计算用户每月销售额及同比、环比增长率

需求描述

1. 计算每个用户在每个月的总销售额。
2. 计算每个月的环比增长率（本月与上月相比）。
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;

---

代码解析

1. 第一步（monthly_sales）：按用户和月份汇总销售数据，计算每月销售总额。
2. 第二步（sales_with_trends）：
   • 使用 LAG() 计算前一个月的销售额，计算环比。
   • 使用 LAG() 结合 12 个月偏移量计算去年的同月销售额，实现同比。
3. 最终结果：计算环比、同比增长率，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 产品的业务数据分析。