网易大数据面试SQL-游戏内经济系统通胀分析
⚠️ 待修正
一、题目背景
这道题来自网易的数据分析岗面试。网易是游戏和音乐，数据分析师需要从海量业务数据中挖掘洞见。
业务场景：游戏运营相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。
一、题目
现有一张游戏内交易记录汇总表 t1_trade_summary，记录了每天游戏中各物品的平均成交价格。请计算每种物品每日成交均价的环比增长率（即 (当日均价 - 前一日均价) / 前一日均价），若前一日无数据则环比为空。
交易汇总表 t1_trade_summary：
+----------+------------+-----------------+-------------+
| item_id | trade_date | avg_price | trade_vol |
+----------+------------+-----------------+-------------+
| I001 | 2024-01-01 | 100.00 | 500 |
| I001 | 2024-01-02 | 105.00 | 600 |
| I001 | 2024-01-03 | 108.00 | 550 |
| I001 | 2024-01-04 | 112.00 | 700 |
| I002 | 2024-01-01 | 50.00 | 300 |
| I002 | 2024-01-02 | 48.00 | 400 |
| I002 | 2024-01-03 | 52.00 | 350 |
| I002 | 2024-01-04 | 55.00 | 500 |
| I003 | 2024-01-02 | 200.00 | 200 |
| I003 | 2024-01-03 | 210.00 | 180 |
+----------+------------+-----------------+-------------+
三、思路分析
本题考察 LAG() 窗口函数计算环比变化率。需要按物品分组，按日期排序，用 LAG() 获取前一日的均价。
解题步骤：
- 使用
LAG(avg_price, 1) OVER (PARTITION BY item_id ORDER BY trade_date)获取每个物品前一日的均价； - 计算环比增长率 =
(avg_price - prev_avg_price) / prev_avg_price； - 若前一日无数据，LAG 返回 NULL，自然导致环比为空。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
1. 使用 LAG 获取前一日均价
执行SQL
select item_id,
trade_date,
avg_price,
trade_vol,
lag(avg_price, 1) over (partition by item_id order by trade_date) as prev_avg_price
from t1_trade_summary
执行结果
+----------+------------+-----------+-----------+----------------+
| item_id | trade_date | avg_price | trade_vol | prev_avg_price |
+----------+------------+-----------+-----------+----------------+
| I001 | 2024-01-01 | 100.00 | 500 | NULL |
| I001 | 2024-01-02 | 105.00 | 600 | 100.00 |
| I001 | 2024-01-03 | 108.00 | 550 | 105.00 |
| I001 | 2024-01-04 | 112.00 | 700 | 108.00 |
| I002 | 2024-01-01 | 50.00 | 300 | NULL |
| I002 | 2024-01-02 | 48.00 | 400 | 50.00 |
| I002 | 2024-01-03 | 52.00 | 350 | 48.00 |
| I002 | 2024-01-04 | 55.00 | 500 | 52.00 |
| I003 | 2024-01-02 | 200.00 | 200 | NULL |
| I003 | 2024-01-03 | 210.00 | 180 | 200.00 |
+----------+------------+-----------+-----------+----------------+
2. 计算环比增长率
执行SQL
select item_id,
trade_date,
avg_price,
prev_avg_price,
round((avg_price - prev_avg_price) / prev_avg_price, 4) as mom_rate
from (
select item_id,
trade_date,
avg_price,
lag(avg_price, 1) over (partition by item_id order by trade_date) as prev_avg_price
from t1_trade_summary
) t
order by item_id, trade_date
执行结果
+----------+------------+-----------+----------------+----------+
| item_id | trade_date | avg_price | prev_avg_price | mom_rate |
+----------+------------+-----------+----------------+----------+
| I001 | 2024-01-01 | 100.00 | NULL | NULL |
| I001 | 2024-01-02 | 105.00 | 100.00 | 0.0500 |
| I001 | 2024-01-03 | 108.00 | 105.00 | 0.0286 |
| I001 | 2024-01-04 | 112.00 | 108.00 | 0.0370 |
| I002 | 2024-01-01 | 50.00 | NULL | NULL |
| I002 | 2024-01-02 | 48.00 | 50.00 | -0.0400 |
| I002 | 2024-01-03 | 52.00 | 48.00 | 0.0833 |
| I002 | 2024-01-04 | 55.00 | 52.00 | 0.0577 |
| I003 | 2024-01-02 | 200.00 | NULL | NULL |
| I003 | 2024-01-03 | 210.00 | 200.00 | 0.0500 |
+----------+------------+-----------+----------------+----------+
五、常见坑点
坑1：NULL值的隐式处理 — 聚合函数跳过NULL，窗口函数不跳过。WHERE需显式过滤或用COALESCE替代。
坑2：数据类型不一致导致JOIN失效 — string vs int隐式转换可能触发全表扫描，性能骤降。
六、举一反三
-
增加时间维度对比：按天/周/月分组，观察指标的趋势和季节性波动
-
增加分组维度：按品类/地区/渠道拆解，发现差异化的业务洞察
-
设置预警阈值：在WHERE中加阈值判断，自动筛选异常数据
七、知识点总结
| 考点 | 说明 |
|---|---|
| LAG / LEAD | 获取前/后一行数据，用于环比计算、状态变更检测 |
| 日期函数 | DATEDIFF / DATE_ADD / unix_timestamp处理日期运算 |
| NULL值处理 | NULL在聚合和窗口函数中的差异，COALESCE/NVL的替代方案 |
| PARTITION BY | 窗口函数按列分组，实现组内独立计算 |
八、建表语句和数据插入
点击展开 DDL & DML
-- 建表语句
CREATE TABLE t1_trade_summary (
item_id string COMMENT '物品ID',
trade_date string COMMENT '交易日期',
avg_price double COMMENT '当日平均成交价格',
trade_vol int COMMENT '交易量'
) COMMENT '游戏内交易汇总表';
-- 数据插入
INSERT INTO t1_trade_summary VALUES
('I001', '2024-01-01', 100.00, 500),
('I001', '2024-01-02', 105.00, 600),
('I001', '2024-01-03', 108.00, 550),
('I001', '2024-01-04', 112.00, 700),
('I002', '2024-01-01', 50.00, 300),
('I002', '2024-01-02', 48.00, 400),
('I002', '2024-01-03', 52.00, 350),
('I002', '2024-01-04', 55.00, 500),
('I003', '2024-01-02', 200.00, 200),
('I003', '2024-01-03', 210.00, 180);
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
