拼多多大数据面试SQL-每个用户的客单价及变化趋势
⚠️ 待修正
一、题目背景
这道题来自拼多多的数据分析岗面试。拼多多是社交裂变电商，数据分析师需要从海量业务数据中挖掘洞见。
业务场景：用户消费行为相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。
一、题目
计算每个用户每月的客单价（客单价 = 该月订单总金额 / 该月订单数），并要求计算每个用户客单价的环比变化率（与上个月相比）。
假设有订单表 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 |
+----------+----------+--------+-------------------+
三、思路分析
- 使用
substr(order_time, 1, 7)或date_format提取年月维度； - 按用户和月份聚合，计算总金额和订单数，得到客单价；
- 使用
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 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 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值的隐式处理 — 聚合函数跳过NULL，窗口函数不跳过。WHERE需显式过滤或用COALESCE替代。
坑2：数据类型不一致导致JOIN失效 — string vs int隐式转换可能触发全表扫描，性能骤降。
六、举一反三
-
增加时间维度对比：按天/周/月分组，观察指标的趋势和季节性波动
-
增加分组维度：按品类/地区/渠道拆解，发现差异化的业务洞察
-
设置预警阈值：在WHERE中加阈值判断，自动筛选异常数据
七、知识点总结
| 考点 | 说明 |
|---|---|
| LAG / LEAD | 获取前/后一行数据，用于环比计算、状态变更检测 |
| GROUP BY + 聚合函数 | 分组聚合是数据分析的基础，配合HAVING筛选分组结果 |
| 日期函数 | DATEDIFF / DATE_ADD / unix_timestamp处理日期运算 |
| NULL值处理 | NULL在聚合和窗口函数中的差异，COALESCE/NVL的替代方案 |
八、建表语句和数据插入
点击展开 DDL & DML
--建表语句
CREATE TABLE orders (
order_id bigint COMMENT '订单ID',
user_id string COMMENT '用户ID',
amount decimal(10,2) COMMENT '订单金额',
order_time string COMMENT '下单时间'
) COMMENT '订单表';
-- 插入数据
insert into 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真题
