得物大数据面试SQL-潮牌二级市场价格波动

Q: 潮牌二级市场价格波动

得物大数据面试SQL-潮牌二级市场价格波动分析

⚠️ 待修正

一、题目背景

这道题来自得物的数据分析岗面试。得物是潮流电商平台，数据分析师需要从海量业务数据中挖掘洞见。

业务场景：潮流电商交易相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。

一、题目

得物平台是潮牌二级市场的重要交易场所，价格波动直接反映市场热度。运营团队需要监控热门单品的价格走势。给定 market_price 表，记录了某款 AJ1 High 'Chicago' 各尺码的每周成交均价。

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

要求：

按尺码分组，计算每周价格相比上一周的环比变化金额和变化率
统计整个3月各尺码的最高价、最低价、振幅（（最高价-最低价）/最低价）
找出3月价格波动（环比变化绝对值）最大的尺码

三、思路分析

本题是多维度窗口函数分析的典型场景。核心考察 LAG 函数获取前序数据、PARTITION BY 分组窗口、以及分组聚合 + 二次排序的能力。

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

解题思路：

第一问：使用 LAG(avg_price) OVER (PARTITION BY size ORDER BY week_start) 获取同尺码上一周价格
环比变化金额 = 本周价格 - 上周价格，变化率 = 变化金额 / 上周价格
第二问：按 size 分组，使用 MAX、MIN 聚合，计算振幅
第三问：在第一问的环比变化基础上，按 size 聚合求平均环比变化绝对值，取最大的尺码
注意 LAG 函数的 PARTITION BY 确保不同尺码之间独立计算

四、逐步推导

步骤1：计算各尺码每周环比变化

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,
    volume
FROM market_price
WHERE product_id = 'P2001'
ORDER BY size, week_start;

执行结果（部分）：

size	week_start	avg_price	prev_week_price	price_change	change_rate_pct	volume
42	2025-03-03	5200	NULL	NULL	NULL	85
42	2025-03-10	5350	5200	150	2.88	92
42	2025-03-17	5480	5350	130	2.43	78
42	2025-03-24	5300	5480	-180	-3.28	88
42	2025-03-31	5100	5300	-200	-3.77	65
43	2025-03-03	4800	NULL	NULL	NULL	70
...	...	...	...	...	...	...

步骤2：统计各尺码3月价格振幅和波动

WITH price_stats AS (
    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 market_price
    WHERE product_id = 'P2001'
    GROUP BY size
),
volatility AS (
    SELECT
        size,
        AVG(ABS(avg_price - LAG(avg_price) OVER (PARTITION BY size ORDER BY week_start))) AS avg_abs_change
    FROM market_price
    WHERE product_id = 'P2001'
    GROUP BY size
)
SELECT
    p.size,
    p.max_price,
    p.min_price,
    p.amplitude_pct,
    ROUND(v.avg_abs_change, 2) AS avg_weekly_volatility
FROM price_stats p
JOIN volatility v ON p.size = v.size
ORDER BY v.avg_abs_change DESC;

执行结果：

size	max_price	min_price	amplitude_pct	avg_weekly_volatility
42	5480	5100	7.45	165.00
43	4950	4750	4.21	87.50
44	4700	4500	4.44	66.67

五、常见坑点

坑1：NULL值的隐式处理 — 聚合函数跳过NULL，窗口函数不跳过。WHERE需显式过滤或用COALESCE替代。

坑2：数据类型不一致导致JOIN失效 — string vs int隐式转换可能触发全表扫描，性能骤降。

六、举一反三

增加时间维度对比：按天/周/月分组，观察指标的趋势和季节性波动
增加分组维度：按品类/地区/渠道拆解，发现差异化的业务洞察
设置预警阈值：在WHERE中加阈值判断，自动筛选异常数据

七、知识点总结

考点	说明
LAG / LEAD	获取前/后一行数据，用于环比计算、状态变更检测
多表JOIN	LEFT JOIN保留主表所有数据，COALESCE处理NULL
GROUP BY + 聚合函数	分组聚合是数据分析的基础，配合HAVING筛选分组结果
日期函数	DATEDIFF / DATE_ADD / unix_timestamp处理日期运算

八、建表语句和数据插入

点击展开 DDL & DML

CREATE TABLE market_price (
    id INT PRIMARY KEY,
    product_id VARCHAR(10),
    size INT,
    week_start DATE,
    avg_price DECIMAL(10,2),
    volume INT
);

INSERT INTO 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真题

一、题目背景​

一、题目​

三、思路分析​

四、逐步推导​

五、常见坑点​

六、举一反三​

七、知识点总结​

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

你可能还想看