携程大数据面试SQL-机票价格波动趋势

⚠️ 待修正

一、题目背景

这道题来自携程的数据分析岗面试。携程是在线旅游平台，数据分析师需要从海量业务数据中挖掘洞见。

业务场景：旅游出行相关需求在日常工作中频繁出现，这类型的SQL题是面试高频考点。

一、题目

携程机票部门需要监控各航线机票价格的波动趋势，以辅助定价策略调整。给定 flight_price 表，记录了北京-上海航线每日的机票平均价格。

flight_price 每日机票均价表：

id	flight_route	price_date	avg_price	ticket_volume
1	北京-上海	2025-06-01	680	320
2	北京-上海	2025-06-02	720	350
3	北京-上海	2025-06-03	650	280
4	北京-上海	2025-06-04	700	310
5	北京-上海	2025-06-05	850	400
6	北京-上海	2025-06-06	820	390
7	北京-上海	2025-06-07	710	300
8	北京-上海	2025-06-08	690	290
9	北京-上海	2025-06-09	660	270
10	北京-上海	2025-06-10	750	340

要求：

1. 计算每日机票价格相比前一天的环比变化金额和环比变化率
2. 标记"价格上涨"、"价格下跌"、"持平"三种趋势
3. 统计连续上涨或连续下跌的最大持续天数

三、思路分析

本题是典型的窗口函数应用题，考察 LAG 函数获取前一行数据、环比计算、以及连续区间识别技巧。核心难点在于第三问的"连续上涨/下跌天数"——需要先识别趋势变化点，再分组计数。

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

解题思路：

1. 使用 LAG(avg_price) OVER (ORDER BY price_date) 获取前一天价格
2. 计算环比变化金额 = 当天价格 - 前一天价格，变化率 = 变化金额 / 前一天价格
3. 使用 CASE WHEN 根据变化金额的正负标记趋势方向
4. 识别连续上涨/下跌：先标记"趋势是否与前一天相同"，用 SUM() + CASE WHEN 分组技巧（常见的连续区间问题），对相同趋势方向累加不同的分组号，再按分组号聚合求持续天数
5. 取各趋势方向的最大持续天数

四、逐步推导

步骤1：计算环比变化及趋势标记

SELECT
    price_date,
    avg_price,
    LAG(avg_price) OVER (ORDER BY price_date) AS prev_price,
    avg_price - LAG(avg_price) OVER (ORDER BY price_date) AS price_change,
    ROUND(
        (avg_price - LAG(avg_price) OVER (ORDER BY price_date)) * 100.0
        / LAG(avg_price) OVER (ORDER BY price_date),
        2
    ) AS change_rate_pct,
    CASE
        WHEN avg_price > LAG(avg_price) OVER (ORDER BY price_date) THEN '上涨'
        WHEN avg_price < LAG(avg_price) OVER (ORDER BY price_date) THEN '下跌'
        ELSE '持平'
    END AS trend
FROM flight_price
WHERE flight_route = '北京-上海'
ORDER BY price_date;

执行结果：

price_date	avg_price	prev_price	price_change	change_rate_pct	trend
2025-06-01	680	NULL	NULL	NULL	NULL
2025-06-02	720	680	40	5.88	上涨
2025-06-03	650	720	-70	-9.72	下跌
2025-06-04	700	650	50	7.69	上涨
2025-06-05	850	700	150	21.43	上涨
2025-06-06	820	850	-30	-3.53	下跌
2025-06-07	710	820	-110	-13.41	下跌
2025-06-08	690	710	-20	-2.82	下跌
2025-06-09	660	690	-30	-4.35	下跌
2025-06-10	750	660	90	13.64	上涨

步骤2：计算连续趋势的最大持续天数

WITH price_trend AS (
    SELECT
        price_date,
        avg_price,
        LAG(avg_price) OVER (ORDER BY price_date) AS prev_price,
        CASE
            WHEN avg_price > LAG(avg_price) OVER (ORDER BY price_date) THEN '上涨'
            WHEN avg_price < LAG(avg_price) OVER (ORDER BY price_date) THEN '下跌'
            ELSE '持平'
        END AS trend
    FROM flight_price
    WHERE flight_route = '北京-上海'
),
trend_group AS (
    SELECT
        price_date,
        avg_price,
        trend,
        -- 当趋势变化时，分组标记+1
        SUM(CASE WHEN trend = LAG(trend) OVER (ORDER BY price_date) THEN 0 ELSE 1 END)
            OVER (ORDER BY price_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS grp
    FROM price_trend
    WHERE trend IS NOT NULL
)
SELECT
    trend,
    MAX(consecutive_days) AS max_consecutive_days
FROM (
    SELECT
        trend,
        grp,
        COUNT(*) AS consecutive_days
    FROM trend_group
    GROUP BY trend, grp
) t
GROUP BY trend
ORDER BY max_consecutive_days DESC;

执行结果：

trend	max_consecutive_days
下跌	4
上涨	2

五、常见坑点

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

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

六、举一反三

1. 增加时间维度对比：按天/周/月分组，观察指标的趋势和季节性波动

2. 增加分组维度：按品类/地区/渠道拆解，发现差异化的业务洞察

3. 设置预警阈值：在WHERE中加阈值判断，自动筛选异常数据

七、知识点总结

考点	说明
SUM() OVER 累积求和	窗口聚合实现滚动累计，配合ORDER BY实现时间轴展开
LAG / LEAD	获取前/后一行数据，用于环比计算、状态变更检测
CASE WHEN 条件聚合	灵活实现分段统计、条件计数、标签化处理
GROUP BY + 聚合函数	分组聚合是数据分析的基础，配合HAVING筛选分组结果

八、建表语句和数据插入

点击展开 DDL & DML

CREATE TABLE flight_price (
    id INT PRIMARY KEY,
    flight_route VARCHAR(20),
    price_date DATE,
    avg_price DECIMAL(10,2),
    ticket_volume INT
);

INSERT INTO flight_price VALUES
(1, '北京-上海', '2025-06-01', 680, 320),
(2, '北京-上海', '2025-06-02', 720, 350),
(3, '北京-上海', '2025-06-03', 650, 280),
(4, '北京-上海', '2025-06-04', 700, 310),
(5, '北京-上海', '2025-06-05', 850, 400),
(6, '北京-上海', '2025-06-06', 820, 390),
(7, '北京-上海', '2025-06-07', 710, 300),
(8, '北京-上海', '2025-06-08', 690, 290),
(9, '北京-上海', '2025-06-09', 660, 270),
(10, '北京-上海', '2025-06-10', 750, 340);

📱关注公众号

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

💬加群交流

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