SQL 用户客单价及变化趋势：LAG环比+移动平均（拼多多面试题）

一、题目背景

这道题来自拼多多用户增长与商业化部门的数据分析岗面试。客单价（ARPU）是电商平台最核心的收入指标之一。拼多多以低价拼团起家，客单价长期低于天猫和京东——这正是拼多多近年通过"百亿补贴"引入iPhone、茅台、大牌美妆等高价值商品的核心战略动机：拉升客单价、提升用户生命周期价值。

数据分析师需要按用户、按月追踪客单价的变化趋势，回答几个关键问题：补贴是否真的让用户"买得更贵"了？客单价提升是普惠性的（所有用户都在涨）还是头部用户拉动的（少数用户买贵了，大多数用户仍在低价区）？某个用户本月客单价异常上涨或下跌，是什么原因（订单数变化/单笔金额变化）？

业务场景：商业化团队每月输出"用户客单价趋势报告"，按用户分群对比客单价环比变化率。核心SQL技巧是 "先聚合、再窗口"——先按月+用户聚合计算月客单价，再用 lag() 窗口函数获取上月值计算环比。考察的是 substr 日期截取、聚合+窗口嵌套、以及环比计算中除零和 NULL 的防御性处理。

二、题目

计算每个用户每月的客单价（客单价 = 该月订单总金额 / 该月订单数），并要求计算每个用户客单价的环比变化率（与上个月相比）。

假设有订单表 t7_orders：

+----------+----------+--------+-------------------+
| order_id | user_id  | amount | order_time        |
+----------+----------+--------+-------------------+
| 1001     | u01      | 89.00  | 2025-05-10 10:00  |
| 1002     | u01      | 45.00  | 2025-05-15 14:00  |
| 1003     | u01      | 120.00 | 2025-06-05 09:00  |
| 1004     | u02      | 60.00  | 2025-05-12 11:00  |
| 1005     | u02      | 90.00  | 2025-06-08 16:00  |
| 1006     | u02      | 150.00 | 2025-06-20 10:00  |
| 1007     | u01      | 200.00 | 2025-07-02 18:00  |
| 1008     | u02      | 80.00  | 2025-07-15 12:00  |
+----------+----------+--------+-------------------+

三、思路分析

1. 使用 substr(order_time, 1, 7) 或 date_format 提取年月维度；
2. 按用户和月份聚合，计算总金额和订单数，得到客单价；
3. 使用 lag 窗口函数获取每个用户上个月的客单价，计算环比变化率 = (本月-上月)/上月。

维度	评分
题目难度	⭐️⭐️⭐️
题目清晰度	⭐️⭐️⭐️⭐️⭐️
业务常见度	⭐️⭐️⭐️⭐️

四、逐步推导

1.按用户和月份计算客单价

执行SQL

select user_id,
       substr(order_time, 1, 7) as order_month,
       round(sum(amount) / count(order_id), 2) as avg_order_amount
from t7_orders
group by user_id, substr(order_time, 1, 7)

查询结果

+---------+-------------+------------------+
| user_id | order_month | avg_order_amount |
+---------+-------------+------------------+
| u01     | 2025-05     | 67.00            |
| u01     | 2025-06     | 120.00           |
| u01     | 2025-07     | 200.00           |
| u02     | 2025-05     | 60.00            |
| u02     | 2025-06     | 120.00           |
| u02     | 2025-07     | 80.00            |
+---------+-------------+------------------+

2.使用LAG计算环比变化率

执行SQL

select user_id,
       order_month,
       avg_order_amount,
       lag(avg_order_amount, 1) over (partition by user_id order by order_month) as prev_month_amount,
       round((avg_order_amount - lag(avg_order_amount, 1) over (partition by user_id order by order_month))
             / lag(avg_order_amount, 1) over (partition by user_id order by order_month), 4) as mom_change_rate
from (
    select user_id,
           substr(order_time, 1, 7) as order_month,
           round(sum(amount) / count(order_id), 2) as avg_order_amount
    from t7_orders
    group by user_id, substr(order_time, 1, 7)
) t

查询结果

+---------+-------------+------------------+-------------------+-----------------+
| user_id | order_month | avg_order_amount | prev_month_amount | mom_change_rate |
+---------+-------------+------------------+-------------------+-----------------+
| u01     | 2025-05     | 67.00            | NULL              | NULL            |
| u01     | 2025-06     | 120.00           | 67.00             | 0.7910          |
| u01     | 2025-07     | 200.00           | 120.00            | 0.6667          |
| u02     | 2025-05     | 60.00            | NULL              | NULL            |
| u02     | 2025-06     | 120.00           | 60.00             | 1.0000          |
| u02     | 2025-07     | 80.00            | 120.00            | -0.3333         |
+---------+-------------+------------------+-------------------+-----------------+

五、常见坑点

坑1：首月环比必然是 NULL — 每个用户的第一个月没有"上个月"，lag 取不到上一行时返回 NULL，NULL 参与四则运算结果仍是 NULL，所以首月的 mom_change_rate 一定是 NULL。这不是 bug，展示时可以保留 NULL 表示"无环比"，或用 coalesce 显示为占位值，但不要误用 0 填充——0 表示"环比持平"，含义完全不同。

坑2：除零问题 — 环比公式的分母是 prev_month_amount，如果上月客单价为 0（例如订单金额被退款冲抵为 0），除法会出问题：有的引擎返回 NULL，有的直接报错或产生 Infinity。稳妥写法是加保护：case when prev_month_amount = 0 then null else round((avg_order_amount - prev_month_amount) / prev_month_amount, 4) end。

坑3：整数除法截断 — 客单价 = sum(amount) / count(order_id)，当 amount 是整型时，部分引擎（如 Hive 旧版本、MySQL 的 div）会执行整数除法直接截断小数。稳妥写法是先乘 1.0 或显式转换：sum(amount) * 1.0 / count(order_id)，再用 round 控制小数位。

六、举一反三

1. 3个月移动平均：单月客单价波动大，可用 avg(avg_order_amount) over (partition by user_id order by order_month rows between 2 preceding and current row) 计算近3个月移动平均，得到更平滑的趋势线

2. 按用户分层对比：用 case when 把用户分为高价值/低价值两层（如按历史消费总额），分层对比客单价趋势，评估百亿补贴对哪类用户的拉动更明显

3. 增加品类维度：按 user_id + category + 月份 聚合，观察客单价的上升是由哪些品类驱动的（是买了更贵的3C数码，还是农产品买得更多）

七、知识点总结

考点	说明
lag 窗口函数	取同一分组内按顺序排列的上一行值，是环比/同比计算的标准工具，取不到时返回 NULL
子查询分层计算	内层先完成分组聚合得到月度客单价，外层再套窗口函数计算环比，层次清晰不易出错
round 函数	控制小数位数，金额保留2位、比率保留4位是常见约定
substr 截取年月	substr(order_time, 1, 7) 从时间字符串截取 yyyy-MM，实现按月聚合，也可用 date_format

八、建表语句和数据插入

点击展开 DDL & DML

-- 建表语句
create table t7_orders (
    order_id   bigint comment '订单ID',
    user_id    string comment '用户ID',
    amount     decimal(10,2) comment '订单金额',
    order_time string comment '下单时间'
) comment '订单表';

-- 插入数据
insert into t7_orders(order_id, user_id, amount, order_time) values
(1001, 'u01', 89.00, '2025-05-10 10:00'),
(1002, 'u01', 45.00, '2025-05-15 14:00'),
(1003, 'u01', 120.00, '2025-06-05 09:00'),
(1004, 'u02', 60.00, '2025-05-12 11:00'),
(1005, 'u02', 90.00, '2025-06-08 16:00'),
(1006, 'u02', 150.00, '2025-06-20 10:00'),
(1007, 'u01', 200.00, '2025-07-02 18:00'),
(1008, 'u02', 80.00, '2025-07-15 12:00');

📱关注公众号

「数据仓库技术」文章同步更新，不错过每一篇干货

💬加群交流

备注「数据仓库技术」加入社群，每日一道大厂SQL真题