跳到主要内容

SQL 潮牌二级市场价格波动：LAG计算涨跌幅（得物面试题）

一、题目​

得物平台是潮牌二级市场的重要交易场所，运营团队需要监控热门单品的价格走势。请计算 AJ1 High 'Chicago' 各尺码每周较上周的价格环比变化金额和变化率，并统计各尺码在 3 月的价格振幅。

给定 t3_market_price 表，记录了 AJ1 High 'Chicago' 各尺码的每周成交均价。

t3_market_price 二级市场价格表：

+-----+-------------+-------+-------------+------------+---------+
| id  | product_id  | size  | week_start  | avg_price  | volume  |
+-----+-------------+-------+-------------+------------+---------+
| 1   | P2001       | 42    | 2025-03-03  | 5200       | 85      |
| 2   | P2001       | 42    | 2025-03-10  | 5350       | 92      |
| 3   | P2001       | 42    | 2025-03-17  | 5480       | 78      |
| 4   | P2001       | 42    | 2025-03-24  | 5300       | 88      |
| 5   | P2001       | 42    | 2025-03-31  | 5100       | 65      |
| 6   | P2001       | 43    | 2025-03-03  | 4800       | 70      |
| 7   | P2001       | 43    | 2025-03-10  | 4900       | 75      |
| 8   | P2001       | 43    | 2025-03-17  | 4750       | 62      |
| 9   | P2001       | 43    | 2025-03-24  | 4850       | 80      |
| 10  | P2001       | 43    | 2025-03-31  | 4950       | 72      |
| 11  | P2001       | 44    | 2025-03-03  | 4600       | 40      |
| 12  | P2001       | 44    | 2025-03-10  | 4650       | 45      |
| 13  | P2001       | 44    | 2025-03-17  | 4700       | 38      |
| 14  | P2001       | 44    | 2025-03-24  | 4550       | 42      |
| 15  | P2001       | 44    | 2025-03-31  | 4500       | 35      |
+-----+-------------+-------+-------------+------------+---------+

要求：

  1. 按尺码分组，计算每周价格较上一周的环比变化金额和变化率
  2. 统计各尺码在 3 月的价格振幅（振幅 = (最高价 - 最低价) / 最低价）

二、思路分析​

本题是窗口函数 LAG 进行环比分析的典型场景，核心考察 partition by 分组窗口和环比计算的细节处理。

+----------+------+
| 维度     | 评分 |
+----------+------+
| 题目难度 | ⭐⭐⭐  |
| 题目清晰度 | ⭐⭐⭐⭐ |
| 业务常见度 | ⭐⭐⭐⭐ |
+----------+------+
  1. 使用 lag(avg_price) over (partition by size order by week_start) 获取同一尺码下上一周的价格，计算环比变化金额和变化率
  2. partition by size 确保不同尺码之间独立计算，互不干扰
  3. 环比变化金额 = 本周价格 - 上周价格；变化率 = 变化金额 / 上周价格，乘以 100.0 转为百分比
  4. 第二问按 size 分组，使用 max(avg_price) 和 min(avg_price) 聚合，计算振幅

三、逐步推导​

1.使用 LAG 窗口函数计算各尺码每周环比变化​

使用lag窗口函数计算各尺码每周环比变化金额和变化率。

执行SQL

select
    size,
    week_start,
    avg_price,
    lag(avg_price) over (partition by size order by week_start) as prev_week_price,
    avg_price - lag(avg_price) over (partition by size order by week_start) as price_change,
    round(
        (avg_price - lag(avg_price) over (partition by size order by week_start)) * 100.0
        / lag(avg_price) over (partition by size order by week_start), 2
    ) as change_rate_pct
from t3_market_price
where product_id = 'P2001'
order by size, week_start;

执行结果

+-------+-------------+------------+------------------+---------------+------------------+
| size  | week_start  | avg_price  | prev_week_price  | price_change  | change_rate_pct  |
+-------+-------------+------------+------------------+---------------+------------------+
| 42    | 2025-03-03  | 5200       | NULL             | NULL          | NULL             |
| 42    | 2025-03-10  | 5350       | 5200             | 150           | 2.88             |
| 42    | 2025-03-17  | 5480       | 5350             | 130           | 2.43             |
| 42    | 2025-03-24  | 5300       | 5480             | -180          | -3.28            |
| 42    | 2025-03-31  | 5100       | 5300             | -200          | -3.77            |
| 43    | 2025-03-03  | 4800       | NULL             | NULL          | NULL             |
| 43    | 2025-03-10  | 4900       | 4800             | 100           | 2.08             |
| 43    | 2025-03-17  | 4750       | 4900             | -150          | -3.06            |
| 43    | 2025-03-24  | 4850       | 4750             | 100           | 2.11             |
| 43    | 2025-03-31  | 4950       | 4850             | 100           | 2.06             |
| 44    | 2025-03-03  | 4600       | NULL             | NULL          | NULL             |
| 44    | 2025-03-10  | 4650       | 4600             | 50            | 1.09             |
| 44    | 2025-03-17  | 4700       | 4650             | 50            | 1.08             |
| 44    | 2025-03-24  | 4550       | 4700             | -150          | -3.19            |
| 44    | 2025-03-31  | 4500       | 4550             | -50           | -1.10            |
+-------+-------------+------------+------------------+---------------+------------------+
15 rows selected (1.363 seconds)(https://www.dwsql.com)

2.按尺码统计3月价格振幅​

按尺码统计3月最高价、最低价和价格振幅。

执行SQL

select
    size,
    max(avg_price) as max_price,
    min(avg_price) as min_price,
    round((max(avg_price) - min(avg_price)) * 100.0 / min(avg_price), 2) as amplitude_pct
from t3_market_price
where product_id = 'P2001'
group by size
order by size;

执行结果

+-------+------------+------------+----------------+
| size  | max_price  | min_price  | amplitude_pct  |
+-------+------------+------------+----------------+
| 42    | 5480       | 5100       | 7.45           |
| 43    | 4950       | 4750       | 4.21           |
| 44    | 4700       | 4500       | 4.44           |
+-------+------------+------------+----------------+
3 rows selected (0.711 seconds)(https://www.dwsql.com)

四、常见坑点​

坑1：LAG 首周数据为 NULL，环比不可计算 — 每个尺码的第一周没有上一周数据，lag() 返回 null，导致价格变化和变化率均为 null。这是正常现象，无需处理，只需理解 null 的含义。

坑2：整数相除的精度问题 — price_change / prev_price 两个整数相除在部分 SQL 引擎中会得到整数结果（小数部分被截断）。应写为 price_change * 100.0 / prev_price，通过 100.0 引入浮点数确保结果保留小数精度。

坑3：partition by size 是必须的 — 如果不指定 partition by size，LAG 会将所有尺码的数据视为一个序列，42 码的最后一周会与 43 码的第一周进行环比计算，导致结果完全错误。不同尺码必须独立计算环比。

五、知识点总结​

+----------------+--------------------------------------------------------+
| 考点           | 说明                                                   |
+----------------+--------------------------------------------------------+
| LAG 窗口函数   | 获取分组内前一行数据，用于环比计算、时序对比           |
| PARTITION BY   | 按指定列分组计算窗口函数，确保各组独立计算             |
| 整数除法精度   | 分子或分母引入浮点数（`* 100.0`），避免小数截断        |
| GROUP BY 聚合  | 按尺寸分组统计最高价、最低价，计算振幅                 |
+----------------+--------------------------------------------------------+

六、建表语句​

点击展开 DDL & DML
create table t3_market_price (
    id int,
    product_id string,
    size int,
    week_start string,
    avg_price int,
    volume int
);

insert into t3_market_price values
(1, 'P2001', 42, '2025-03-03', 5200, 85),
(2, 'P2001', 42, '2025-03-10', 5350, 92),
(3, 'P2001', 42, '2025-03-17', 5480, 78),
(4, 'P2001', 42, '2025-03-24', 5300, 88),
(5, 'P2001', 42, '2025-03-31', 5100, 65),
(6, 'P2001', 43, '2025-03-03', 4800, 70),
(7, 'P2001', 43, '2025-03-10', 4900, 75),
(8, 'P2001', 43, '2025-03-17', 4750, 62),
(9, 'P2001', 43, '2025-03-24', 4850, 80),
(10, 'P2001', 43, '2025-03-31', 4950, 72),
(11, 'P2001', 44, '2025-03-03', 4600, 40),
(12, 'P2001', 44, '2025-03-10', 4650, 45),
(13, 'P2001', 44, '2025-03-17', 4700, 38),
(14, 'P2001', 44, '2025-03-24', 4550, 42),
(15, 'P2001', 44, '2025-03-31', 4500, 35);
📱关注公众号

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

微信公众号二维码
💬加群交流

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

交流微信二维码

你可能还想看

专题
窗口函数专题
相关题目
01. 商品真伪鉴定通过率02. 限量款抽签中签率04. 用户潮流偏好标签