在业务数据统计中，同比反映本期数据与上年同期数据的对比情况，环比反映本期数据与上期数据的对比情况，是运营分析、财务统计等场景的核心指标。传统生成同比环比报表的方式通常需要先分别计算各期汇总值，再通过多表关联拼接结果，不仅SQL逻辑复杂，执行效率也较低。使用SQL窗口函数可以一次性完成所有期数的汇总与前后期数据提取，大幅简化计算流程。
同比环比的计算逻辑
同比和环比的计算公式如下：
- 环比增长率 = (本期数值 - 上期数值) / 上期数值 * 100%
- 同比增长率 = (本期数值 - 上年同期数值) / 上年同期数值 * 100%
如果只需要绝对值对比，也可以直接展示本期、上期、上年同期的数值，无需计算增长率。
窗口函数相关语法说明
本文主要使用两个窗口函数实现前后期数据的提取：
LAG函数
用于获取当前行之前指定偏移量的行的数据，语法为：
LAG(列名, 偏移量, 默认值) OVER (PARTITION BY 分组列 ORDER BY 排序列)
例如LAG(sales, 1)表示获取当前行上一行的sales值，如果上一行不存在则返回默认值。
LEAD函数
用于获取当前行之后指定偏移量的行的数据，语法与LAG类似：
LEAD(列名, 偏移量, 默认值) OVER (PARTITION BY 分组列 ORDER BY 排序列)
完整示例：生成月度汇总同比环比报表
假设我们有一张销售明细表sales_detail，结构如下：
| 字段名 | 类型 | 说明 |
|---|---|---|
| sale_date | DATE | 销售日期 |
| product_category | VARCHAR | 产品类别 |
| amount | DECIMAL | 销售金额 |
需求是按产品类别、按月汇总销售金额，同时计算每月的环比和同比数据，完整SQL如下：
-- 先按类别和月份汇总销售金额
WITH monthly_summary AS (
SELECT
product_category,
DATE_FORMAT(sale_date, '%Y-%m') AS sale_month,
SUM(amount) AS total_amount
FROM sales_detail
GROUP BY product_category, DATE_FORMAT(sale_date, '%Y-%m')
),
-- 用窗口函数提取上期、上年同期数据
summary_with_compare AS (
SELECT
product_category,
sale_month,
total_amount,
-- 获取上月的销售金额，按月排序偏移1
LAG(total_amount, 1) OVER (
PARTITION BY product_category
ORDER BY sale_month
) AS last_month_amount,
-- 获取上年同期的销售金额，按月排序偏移12
LAG(total_amount, 12) OVER (
PARTITION BY product_category
ORDER BY sale_month
) AS last_year_same_month_amount
FROM monthly_summary
)
-- 计算环比和同比增长率
SELECT
product_category AS 产品类别,
sale_month AS 统计月份,
total_amount AS 本月销售额,
last_month_amount AS 上月销售额,
-- 计算环比增长率，处理除数为0的情况
CASE
WHEN last_month_amount IS NULL OR last_month_amount = 0 THEN NULL
ELSE ROUND((total_amount - last_month_amount) / last_month_amount * 100, 2)
END AS 环比增长率_百分比,
last_year_same_month_amount AS 上年同期销售额,
-- 计算同比增长率，处理除数为0的情况
CASE
WHEN last_year_same_month_amount IS NULL OR last_year_same_month_amount = 0 THEN NULL
ELSE ROUND((total_amount - last_year_same_month_amount) / last_year_same_month_amount * 100, 2)
END AS 同比增长率_百分比
FROM summary_with_compare
ORDER BY product_category, sale_month;
注意事项
- 窗口函数中的
PARTITION BY需要根据统计维度调整，如果是全量统计不需要分区则去掉该子句 - 使用LAG取上年同期数据时，需要确保月份是连续排序的，避免跨年排序错误
- 计算增长率时需要处理除数为0或者上期数据不存在的情况，避免SQL报错
- 如果数据中存在缺失月份，LAG(12)可能无法正确取到上年同期数据，需要先补全月份维度再计算