跳到主要内容

SQL 游戏经济系统通胀分析：物价随时间变化趋势（网易面试题）

一、题目背景​

这道题来自网易游戏经济系统组的数据分析岗面试。游戏内有一套虚拟经济体系——玩家通过打金、交易获取装备和材料。物价的稳定直接关系到游戏寿命：通胀过快会导致新玩家无法追赶，通缩则意味着经济活力不足。运营需要持续监控各物品的每日成交均价和环比变化率，及时预警异常波动。

业务场景：经济系统周报中，运营会列出"本周环比涨幅 Top 10 物品"，如果某物品连续多日环比上涨超过阈值，则需要调查是否存在工作室囤货炒价行为。这道题的 LAG 环比计算就是该监控报表的基础 SQL。

二、题目​

现有一张游戏内交易记录汇总表 t6_trade_summary，记录了每天游戏中各物品的平均成交价格。请计算每种物品每日成交均价的环比增长率（即 (当日均价 - 前一日均价) / 前一日均价），若前一日无数据则环比为空。

交易汇总表 t6_trade_summary：

+----------+-------------+------------+------------+
| item_id  | trade_date  | avg_price  | trade_vol  |
+----------+-------------+------------+------------+
| I001     | 2024-01-01  | 100.0      | 500        |
| I001     | 2024-01-02  | 105.0      | 600        |
| I001     | 2024-01-03  | 108.0      | 550        |
| I001     | 2024-01-04  | 112.0      | 700        |
| I002     | 2024-01-01  | 50.0       | 300        |
| I002     | 2024-01-02  | 48.0       | 400        |
| I002     | 2024-01-03  | 52.0       | 350        |
| I002     | 2024-01-04  | 55.0       | 500        |
| I003     | 2024-01-02  | 200.0      | 200        |
| I003     | 2024-01-03  | 210.0      | 180        |
+----------+-------------+------------+------------+

三、思路分析​

本题考察 LAG() 窗口函数计算环比变化率。需要按物品分组，按日期排序，用 LAG() 获取前一日的均价。

解题步骤：

  1. 使用 LAG(avg_price, 1) OVER (PARTITION BY item_id ORDER BY trade_date) 获取每个物品前一日的均价；
  2. 计算环比增长率 = (avg_price - prev_avg_price) / prev_avg_price；
  3. 若前一日无数据，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 t6_trade_summary

执行结果

+----------+-------------+------------+------------+-----------------+
| item_id  | trade_date  | avg_price  | trade_vol  | prev_avg_price  |
+----------+-------------+------------+------------+-----------------+
| I001     | 2024-01-01  | 100.0      | 500        | NULL            |
| I001     | 2024-01-02  | 105.0      | 600        | 100.0           |
| I001     | 2024-01-03  | 108.0      | 550        | 105.0           |
| I001     | 2024-01-04  | 112.0      | 700        | 108.0           |
| I002     | 2024-01-01  | 50.0       | 300        | NULL            |
| I002     | 2024-01-02  | 48.0       | 400        | 50.0            |
| I002     | 2024-01-03  | 52.0       | 350        | 48.0            |
| I002     | 2024-01-04  | 55.0       | 500        | 52.0            |
| I003     | 2024-01-02  | 200.0      | 200        | NULL            |
| I003     | 2024-01-03  | 210.0      | 180        | 200.0           |
+----------+-------------+------------+------------+-----------------+
10 rows selected (0.856 seconds)(https://www.dwsql.com)

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 t6_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.0      | NULL            | NULL      |
| I001     | 2024-01-02  | 105.0      | 100.0           | 0.05      |
| I001     | 2024-01-03  | 108.0      | 105.0           | 0.0286    |
| I001     | 2024-01-04  | 112.0      | 108.0           | 0.037     |
| I002     | 2024-01-01  | 50.0       | NULL            | NULL      |
| I002     | 2024-01-02  | 48.0       | 50.0            | -0.04     |
| I002     | 2024-01-03  | 52.0       | 48.0            | 0.0833    |
| I002     | 2024-01-04  | 55.0       | 52.0            | 0.0577    |
| I003     | 2024-01-02  | 200.0      | NULL            | NULL      |
| I003     | 2024-01-03  | 210.0      | 200.0           | 0.05      |
+----------+-------------+------------+-----------------+-----------+
10 rows selected (0.582 seconds)(https://www.dwsql.com)

五、常见坑点​

坑1：LAG 取到的前一行为 NULL

每个物品的第一天没有"前一日均价"，LAG(avg_price, 1) 返回 NULL。此时环比增长率 (avg_price - NULL) / NULL 结果为 NULL，这是正确的——第一天确实无法计算环比。但如果业务要求显示 0，需要用 COALESCE(mom_rate, 0) 包装。

坑2：除零保护

前一日均价可能为 0（免费赠送或异常数据），分母为 0 时会报除零错误。应用 (avg_price - prev_avg_price) / NULLIF(prev_avg_price, 0) 防护，此时环比返回 NULL 而非中断查询。

坑3：没有 PARTITION BY 导致物品间数据串行

如果忘记写 PARTITION BY item_id，LAG 会跨物品取前一行的 avg_price，导致 I001 某天的"前一日均价"实际上是 I002 的价格。环比数据全部错乱，必须按物品分组。

坑4：交易量为 0 的异常日期

某些物品可能某天有成交但价格为 0（系统异常或测试数据），或者有价格但没有成交量。这类数据需要在 WHERE 中过滤 WHERE trade_vol > 0 AND avg_price > 0，否则会产出无意义的环比结果。

六、举一反三​

  1. 多周期环比：同时计算 LAG(avg_price, 7) 周环比和 LAG(avg_price, 30) 月环比，在一行中对比日/周/月三个维度的价格变化趋势

  2. 涨幅/跌幅告警：WHERE mom_rate > 0.2 OR mom_rate < -0.2 筛选单日涨跌幅超过 20% 的异常物品，运营介入调查是否有人为操纵物价

  3. 移动平均平滑：环比受单日波动影响大，先用 AVG(avg_price) OVER (PARTITION BY item_id ORDER BY trade_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) 计算 7日移动平均价，再对移动均价做环比，排除偶然波动

  4. 物以类聚——按品类汇总物价指数：先 JOIN 物品品类表，按品类 GROUP BY 求各品类日均价，再对品类均价做 LAG 环比，产出"武器类物价指数环比 +5%，防具类 -2%"这种宏观报告

七、知识点总结​

考点说明
LAG(column, N)获取当前行前 N 行的值，PARTITION BY item_id 确保物品独立计算
NULLIF 除零防护NULLIF(prev_avg_price, 0) 将零值转为 NULL，避免除零报错
PARTITION BY 分组窗口函数按物品分组、按日期排序，保证环比计算不跨物品串行
ROUND 精度控制环比保留 4 位小数，0.05 = 5% 涨幅，负数表示跌幅

八、建表语句和数据插入​

点击展开 DDL & DML
-- 建表语句
CREATE TABLE t6_trade_summary (
    item_id    string COMMENT '物品ID',
    trade_date string COMMENT '交易日期',
    avg_price  double COMMENT '当日平均成交价格',
    trade_vol  int    COMMENT '交易量'
) COMMENT '游戏内交易汇总表';

-- 数据插入
INSERT INTO t6_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真题

交流微信二维码

你可能还想看

专题
窗口函数专题
相关题目
01. 游戏ARPU和ARPPU计算04. 游戏玩家留存率02. 副本通关时间TopN队伍