根据定订单表（包含订单ID、用户ID、订单金额、支付时间），计算 2025 年每个月的 GMV，以及每个月的环比增...

面试题：2025 年每个月 GMV 的环比与同比增长率计算

1. 题目背景

在电商数据分析中，GMV（商品交易总额）的环比增长率（MoM, Month-on-Month）和同比增长率（YoY, Year-on-Year）是衡量业务增长趋势的核心指标。

环比增长率 =（本月GMV - 上月GMV）/ 上月GMV
同比增长率 =（本月GMV - 去年同期GMV）/ 去年同期GMV

2. 示例数据表

订单表：orders

订单ID (order_id)	用户ID (user_id)	订单金额 (order_amount)	支付时间 (pay_time)
1	U01	1000.00	2024-01-15 10:00:00
2	U02	2000.00	2024-12-20 15:00:00
3	U03	1500.00	2025-01-10 09:00:00
4	U04	1200.00	2025-01-20 18:30:00
5	U01	1800.00	2025-02-14 14:00:00
6	U02	2500.00	2025-12-25 12:00:00
7	U05	2000.00	2024-12-10 08:00:00

3. 期望输出结果（以2025年有数据的月份为例）

月份	GMV	环比增长率(%)	同比增长率(%)
2025-01	2700.00	-32.50	170.00
2025-02	1800.00	-33.33	NULL
2025-12	2500.00	NULL	-37.50

4. SQL 解决方案

在实际面试或业务场景中，由于部分月份可能会出现数据缺失（如示例数据中 2025 年 3 月至 11 月无数据），使用传统的窗口函数 LAG(gmv, 1) 可能会因为行错位而导致环比计算错误。

因此，这里提供两种写法：自关联法（更鲁棒，推荐） 和 窗口函数法（假设月份连续）。

写法 A：自关联法（容错性高，推荐）

该方法通过年份和月份的数学关系进行 LEFT JOIN，即使某些月份数据缺失，也不会出现“跨月错位对比”的问题。

sql

WITH monthly_gmv AS (
    -- 步骤 1：按月汇总 GMV
    SELECT 
        YEAR(pay_time) AS pay_year,
        MONTH(pay_time) AS pay_month,
        SUM(order_amount) AS gmv
    FROM orders
    GROUP BY YEAR(pay_time), MONTH(pay_time)
)
SELECT 
    CONCAT(curr.pay_year, '-', LPAD(curr.pay_month, 2, '0')) AS `月份`,
    curr.gmv AS `GMV`,
    -- 环比增长率计算：对比上一个月（处理跨年1月份对比去年12月的情况）
    ROUND((curr.gmv - prev_m.gmv) / prev_m.gmv * 100, 2) AS `环比增长率(%)`,
    -- 同比增长率计算：对比去年同期
    ROUND((curr.gmv - prev_y.gmv) / prev_y.gmv * 100, 2) AS `同比增长率(%)`
FROM monthly_gmv curr
-- 关联上一个月
LEFT JOIN monthly_gmv prev_m 
    ON (curr.pay_year = prev_m.pay_year AND curr.pay_month - 1 = prev_m.pay_month)
    OR (curr.pay_year - 1 = prev_m.pay_year AND curr.pay_month = 1 AND prev_m.pay_month = 12)
-- 关联去年同期
LEFT JOIN monthly_gmv prev_y 
    ON curr.pay_year - 1 = prev_y.pay_year AND curr.pay_month = prev_y.pay_month
WHERE curr.pay_year = 2025
ORDER BY curr.pay_month;

写法 B：窗口函数法（基于月份连续的假设）

如果面试官明确表示数据中不存在缺失月份，或者默认进行了月份补全，使用 LAG 窗口函数会更加简洁。

sql

WITH monthly_gmv AS (
    -- 步骤 1：按 yyyy-MM 格式汇总
    SELECT 
        DATE_FORMAT(pay_time, '%Y-%m') AS ym,
        SUM(order_amount) AS gmv
    FROM orders
    GROUP BY DATE_FORMAT(pay_time, '%Y-%m')
),
calculated AS (
    -- 步骤 2：利用 LAG 函数获取上一期和去年同期数据
    SELECT 
        ym,
        gmv,
        LAG(gmv, 1) OVER (ORDER BY ym) AS prev_month_gmv,
        LAG(gmv, 12) OVER (ORDER BY ym) AS prev_year_gmv
    FROM monthly_gmv
)
-- 步骤 3：在外层过滤出 2025 年的数据并计算比例
SELECT 
    ym AS `月份`,
    gmv AS `GMV`,
    ROUND((gmv - prev_month_gmv) / prev_month_gmv * 100, 2) AS `环比增长率(%)`,
    ROUND((gmv - prev_year_gmv) / prev_year_gmv * 100, 2) AS `同比增长率(%)`
FROM calculated
WHERE ym LIKE '2025-%'
ORDER BY ym;

5. SQL 深度解析与面试官关注点

执行顺序陷阱（重点）
在写法 B 中，为什么需要使用两层 CTE（或子查询）而不是直接在 WHERE 过滤 2025 年后再使用 LAG？
- 原因：SQL 的执行顺序中，WHERE 过滤早于窗口函数（Window Functions）。如果在汇总层直接过滤 WHERE DATE_FORMAT(pay_time, '%Y-%m') LIKE '2025-%'，那么窗口函数在计算时就只能看到 2025 年的数据。这样会导致 2025 年 1 月无法获取 2024 年 12 月的数据（环比变为 NULL），且所有月份都无法获取 2024 年的同期数据（同比全为 NULL）。因此，必须先在子查询中计算完所有年份的 LAG，最后在外层进行年份过滤。
数据缺失与稀疏矩阵处理
在实际业务中，并不是每个月都有交易数据。
- 如果使用 LAG(gmv, 1)，当 2025 年 11 月无交易时，2025 年 12 月的 LAG(gmv, 1) 实际上会取到 2025 年 10 月的值，从而导致环比计算逻辑发生错误（变成了隔月对比）。
- 针对此问题，写法 A 通过具体的月份数字差值（curr.pay_month - 1 = prev_m.pay_month）进行强行匹配，保证了即使前一个月无数据，也只会返回 NULL，这在生产环境中更加安全。
分母为 0 的边界处理
如果上月 GMV 或去年同期 GMV 刚好为 0，直接相除会导致 Division by zero 报错。在严谨的面试中，可以主动提及或使用 NULLIF(prev_gmv, 0) 或 CASE WHEN 来规避该风险，这能体现出良好的编码防错意识。

播面

根据定订单表（包含订单ID、用户ID、订单金额、支付时间），计算 2025 年每个月的 GMV，以及每个月的环比增长率和同比增长率。