播面 根据定订单表（包含订单ID、用户ID、订单金额、支付时间），计算 2025 年每个月的 GMV，以及每个月的环比增长率和同比增长率
面试真题：2025年每月 GMV 及其环比与同比增长率计算
1. 题目背景
在电商数据分析中，GMV（成交总额）、环比增长率（MoM, Month-on-Month） 和 同比增长率（YoY, Year-on-Year） 是评估业务增长的核心指标。
请编写一段 SparkSQL 逻辑，根据给定的订单表，计算 2025 年每个月 的 GMV，以及该月的环比增长率和同比增长率。
- 环比增长率 = (本月GMV - 上月GMV) / 上月GMV * 100%
- 同比增长率 = (本月GMV - 去年同期GMV) / 去年同期GMV * 100%
2. 样例输入数据（订单表：t_order）
| order_id (订单ID) | user_id (用户ID) | order_amount (订单金额) | pay_time (支付时间) |
|---|---|---|---|
| O001 | U01 | 1000.00 | 2024-01-15 10:00:00 |
| O002 | U02 | 1500.00 | 2024-02-18 11:30:00 |
| O003 | U03 | 2000.00 | 2024-12-25 18:20:00 |
| O004 | U01 | 1200.00 | 2025-01-10 14:15:00 |
| O005 | U04 | 1800.00 | 2025-02-14 09:00:00 |
| O006 | U02 | 2000.00 | 2025-03-05 16:45:00 |
3. 期望输出结果（只展示 2025 年的数据）
| year_month (月份) | gmv (本月GMV) | mom_rate (环比增长率%) | yoy_rate (同比增长率%) |
|---|---|---|---|
| 2025-01 | 1200.00 | -40.00% | 20.00% |
| 2025-02 | 1800.00 | 50.00% | 20.00% |
| 2025-03 | 2000.00 | 11.11% | NULL (或 0.00%) |
SparkSQL 解决方案
sql
WITH monthly_sales AS (
-- Step 1: 按月聚合，并计算用于错位关联的连续月份索引 (Year * 12 + Month)
SELECT
year(pay_time) AS yr,
month(pay_time) AS mth,
date_format(pay_time, 'yyyy-MM') AS year_month,
sum(order_amount) AS gmv,
year(pay_time) * 12 + month(pay_time) AS month_idx
FROM t_order
GROUP BY year(pay_time), month(pay_time), date_format(pay_time, 'yyyy-MM')
),
lag_sales AS (
-- Step 2: 利用窗口函数的 RANGE 范围计算上月(index-1)和去年同期(index-12)的GMV
-- 这样可以完美解决“月份不连续/缺失”导致的错位问题
SELECT
year_month,
yr,
gmv,
-- 获取上月 GMV (month_idx - 1)
MAX(gmv) OVER(
ORDER BY month_idx
RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING
) AS last_month_gmv,
-- 获取去年同期 GMV (month_idx - 12)
MAX(gmv) OVER(
ORDER BY month_idx
RANGE BETWEEN 12 PRECEDING AND 12 PRECEDING
) AS last_year_gmv
FROM monthly_sales
)
-- Step 3: 过滤出2025年的数据，并计算环比和同比
SELECT
year_month,
round(gmv, 2) AS gmv,
-- 环比计算
CASE
WHEN last_month_gmv IS NULL THEN NULL
ELSE concat(round((gmv - last_month_gmv) / last_month_gmv * 100, 2), '%')
END AS mom_rate,
-- 同比计算
CASE
WHEN last_year_gmv IS NULL THEN NULL
ELSE concat(round((gmv - last_year_gmv) / last_year_gmv * 100, 2), '%')
END AS yoy_rate
FROM lag_sales
WHERE yr = 2025
ORDER BY year_month;核心考点与深度解析（面试必加分）
在面试中，这道题不仅仅考查简单的 GROUP BY，更是考查大数据处理中的边界情况处理能力和高级窗口函数的应用。以下是该 SQL 的核心技术亮点解析：
1. 为什么不用 LAG(gmv, 1) 而是用 RANGE 窗口？
- 传统做法的缺陷：普通候选人通常会直接使用
LAG(gmv, 1) OVER (ORDER BY year_month)。但如果数据中缺失了某些月份（例如 2024-11 没有数据，2024-12 的上一行变成了 2024-10），LAG(1)就会错误地将 10 月当作 11 月来计算环比。 - 高分设计方案：通过定义一个连续的虚拟轴值
month_idx = Year * 12 + Month。- 2024-12 的
month_idx是 。 - 2025-01 的
month_idx是 。
使用RANGE BETWEEN 1 PRECEDING AND 1 PRECEDING能够严格锁定只拿比当前month_idx刚好小 1 的那一行。如果上个月数据缺失，结果会正确输出为NULL，避免了数据漂移。
- 2024-12 的
2. 同比（YoY）的优雅实现
- 同理，去年同期的
month_idx刚好相差 12。 - 通过
RANGE BETWEEN 12 PRECEDING AND 12 PRECEDING能够一步到位获取去年同期的 GMV，避免了复杂的自关联（Self-Join），在处理海量 Spark 数据时减少了 Shuffle 操作，极大地提升了执行效率。
3. 边界和空值（NULL）处理
- 分母为 0 或为空的防错：使用
CASE WHEN提前对last_month_gmv和last_year_gmv进行NULL判定，防止运行时出现Divide by zero（除以零）的报错，展现生产级代码的严谨性。