SQL 机票价格波动趋势：按航线+日期计算价格变化（携程面试题）

一、题目

计算各航线每日价格较前日的环比变化(金额+变化率)，标记上涨/下跌/持平，统计连续同趋势的最大持续天数。给定 t3_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(partition by flight_route order by price_date) 获取各航线前一天价格
2. 计算环比变化金额和变化率，用 case when 标记趋势方向
3. 识别趋势是否变化：用 lag(trend) 获取前一天趋势，配合 sum(case when ...) over() 对连续相同趋势生成分组号
4. 按趋势和分组号聚合，统计每段连续天数，最后取最大值

三、逐步推导

1. 计算环比变化及趋势标记

使用 lag 窗口函数获取前一天价格，计算变化金额和变化率，并用 case when 标记涨跌方向。

执行SQL：

select
    flight_route,
    price_date,
    avg_price,
    lag(avg_price) over(partition by flight_route order by price_date) as prev_price,
    avg_price - lag(avg_price) over(partition by flight_route order by price_date) as price_change,
    round(
        (avg_price - lag(avg_price) over(partition by flight_route order by price_date))
        / lag(avg_price) over(partition by flight_route order by price_date),
        4
    ) as change_rate,
    case
        when avg_price > lag(avg_price) over(partition by flight_route order by price_date) then '上涨'
        when avg_price < lag(avg_price) over(partition by flight_route order by price_date) then '下跌'
        else '持平'
    end as trend
from t3_flight_price
order by flight_route, price_date;

结果：

+---------------+-------------+------------+-------------+---------------+--------------+--------+
| flight_route  | price_date  | avg_price  | prev_price  | price_change  | change_rate  | trend  |
+---------------+-------------+------------+-------------+---------------+--------------+--------+
| 北京-上海         | 2025-06-01  | 680        | NULL        | NULL          | NULL         | 持平     |
| 北京-上海         | 2025-06-02  | 720        | 680         | 40            | 0.0588       | 上涨     |
| 北京-上海         | 2025-06-03  | 650        | 720         | -70           | -0.0972      | 下跌     |
| 北京-上海         | 2025-06-04  | 700        | 650         | 50            | 0.0769       | 上涨     |
| 北京-上海         | 2025-06-05  | 850        | 700         | 150           | 0.2143       | 上涨     |
| 北京-上海         | 2025-06-06  | 820        | 850         | -30           | -0.0353      | 下跌     |
| 北京-上海         | 2025-06-07  | 710        | 820         | -110          | -0.1341      | 下跌     |
| 北京-上海         | 2025-06-08  | 690        | 710         | -20           | -0.0282      | 下跌     |
| 北京-上海         | 2025-06-09  | 660        | 690         | -30           | -0.0435      | 下跌     |
| 北京-上海         | 2025-06-10  | 750        | 660         | 90            | 0.1364       | 上涨     |
+---------------+-------------+------------+-------------+---------------+--------------+--------+
10 rows selected (8.199 seconds)(https://www.dwsql.com)

2. 统计连续同趋势最大持续天数

先标记趋势变化点，用 sum(case when ...) over() 累加产生分组号，再按趋势和分组号聚合计数，最后取各趋势的最大值。

执行SQL：

with price_trend as (
    select
        flight_route,
        price_date,
        avg_price,
        lag(avg_price) over(partition by flight_route order by price_date) as prev_price,
        case
            when avg_price > lag(avg_price) over(partition by flight_route order by price_date) then '上涨'
            when avg_price < lag(avg_price) over(partition by flight_route order by price_date) then '下跌'
            else '持平'
        end as trend
    from t3_flight_price
),
trend_group as (
    select
        flight_route,
        price_date,
        trend,
        sum(
            case
                when trend = lag(trend) over(partition by flight_route order by price_date) then 0
                else 1
            end
        ) over(partition by flight_route order by price_date rows between unbounded preceding and current row) as grp
    from price_trend
    where prev_price is not null
      and trend in ('上涨', '下跌')
)
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                     |
+--------+-----------------------+
2 rows selected (1.63 seconds)(https://www.dwsql.com)

四、常见坑点

坑1：lag 首日为 null 不可计算

lag 对每个分区第一行返回 null，此时环比变化金额和变化率也均为 null，趋势标记会落入 else 分支变成"持平"。在计算连续趋势时需用 where prev_price is not null 过滤掉首日，否则会误将首日归类为持平并影响连续趋势分组。

坑2：sum() over() 分组连续趋势的经典技巧

用 sum(case when trend <> lag(trend) then 1 else 0 end) over(order by date) 生成分组号，是 Hive/Spark SQL 中识别连续区间的经典写法。关键点：累加时 order by 必须配合 rows between unbounded preceding and current row，并注意 partition by flight_route 确保各航线独立计算。

坑3：除零保护

计算变化率时分母是前一天价格，虽然实际业务中价格不可能为 0，但若数据清洗不到位出现 0 值会触发除零错误。可以使用 nullif(lag(avg_price), 0) 或 case when prev_price = 0 then null else ... end 做防御性处理。

五、知识点总结

考点	说明
lag 环比计算	lag(col) over(partition by ... order by ...) 获取前一行数据，用于环比、同比计算
连续区间分组(sum + case when)	标记变化点后累加生成分组号，将连续相同状态的行归为一组，按组聚合求持续天数
row_number 排名	可为每个分区内行编号，配合自关联或子查询实现更复杂的连续序列识别

六、建表语句和数据插入

点击展开 DDL & DML

create table t3_flight_price (
    id int comment '记录id',
    flight_route string comment '航线名称',
    price_date string comment '价格日期',
    avg_price int comment '当日平均票价',
    ticket_volume int comment '出票量'
) comment '每日机票均价表';

insert into t3_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真题