携程大数据面试SQL-用户旅行目的地偏好分析

⚠️ 待修正

一、题目背景

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

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

一、题目

携程需要为每个用户打上"首选目的地"标签，用于个性化推荐。给定 travel_order 表，记录了用户的旅行订单信息。

travel_order 用户旅行订单表：

order_id	user_id	destination	travel_date	days	amount
ORD001	U100	三亚	2024-01-15	5	6200
ORD002	U100	丽江	2024-03-20	4	3800
ORD003	U100	三亚	2024-07-10	6	7200
ORD004	U101	成都	2024-02-10	3	2500
ORD005	U101	成都	2024-05-15	4	3100
ORD006	U101	重庆	2024-08-20	3	2200
ORD007	U101	成都	2024-10-01	5	4500
ORD008	U102	三亚	2024-04-05	4	4800
ORD009	U102	厦门	2024-06-18	3	2800
ORD010	U102	桂林	2024-09-12	4	3200
ORD011	U103	西安	2024-03-08	3	2100
ORD012	U103	丽江	2024-07-25	5	5200

要求：

找到每个用户去过次数最多的目的地（即"首选目的地"）
如果有多个目的地并列第一，则取最近一次旅行的目的地
输出用户ID、首选目的地、去过的次数、最近一次前往日期、在该目的地的总消费金额

三、思路分析

本题是典型的"分组TopN"问题，考察 ROW_NUMBER() 窗口函数配合 PARTITION BY 进行组内排序的能力。关键在于多维度排序——先按次数降序，次数相同时按最近日期降序。

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

解题思路：

第一步：按 (user_id, destination) 进行聚合，统计每个用户去每个目的地的次数、最近日期和总消费
第二步：使用 ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY visit_count DESC, last_visit_date DESC) 对每个用户的目的地进行排名
第三步：取 rn = 1 的记录即为每个用户的首选目的地
注意排序逻辑：visit_count DESC 保证次数最多的排前面，last_visit_date DESC 保证并列时取最近的

四、逐步推导

步骤1：按用户和目的地聚合统计

SELECT
    user_id,
    destination,
    COUNT(*) AS visit_count,
    MAX(travel_date) AS last_visit_date,
    SUM(amount) AS total_amount
FROM travel_order
GROUP BY user_id, destination
ORDER BY user_id, visit_count DESC;

执行结果：

user_id	destination	visit_count	last_visit_date	total_amount
U100	三亚	2	2024-07-10	13400
U100	丽江	1	2024-03-20	3800
U101	成都	3	2024-10-01	10100
U101	重庆	1	2024-08-20	2200
U102	三亚	1	2024-04-05	4800
U102	厦门	1	2024-06-18	2800
U102	桂林	1	2024-09-12	3200
U103	丽江	1	2024-07-25	5200
U103	西安	1	2024-03-08	2100

步骤2：取每个用户的首选目的地

WITH dest_stats AS (
    SELECT
        user_id,
        destination,
        COUNT(*) AS visit_count,
        MAX(travel_date) AS last_visit_date,
        SUM(amount) AS total_amount
    FROM travel_order
    GROUP BY user_id, destination
),
ranked AS (
    SELECT
        user_id,
        destination,
        visit_count,
        last_visit_date,
        total_amount,
        ROW_NUMBER() OVER (
            PARTITION BY user_id
            ORDER BY visit_count DESC, last_visit_date DESC
        ) AS rn
    FROM dest_stats
)
SELECT
    user_id,
    destination AS top_destination,
    visit_count,
    last_visit_date,
    total_amount
FROM ranked
WHERE rn = 1
ORDER BY user_id;

执行结果：

user_id	top_destination	visit_count	last_visit_date	total_amount
U100	三亚	2	2024-07-10	13400
U101	成都	3	2024-10-01	10100
U102	桂林	1	2024-09-12	3200
U103	丽江	1	2024-07-25	5200

五、常见坑点

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

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

六、举一反三

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

七、知识点总结

考点	说明
ROW_NUMBER + 差值法	连续问题经典解法：日期-行号=分组标识，相同差值同一连续段
SUM() OVER 累积求和	窗口聚合实现滚动累计，配合ORDER BY实现时间轴展开
GROUP BY + 聚合函数	分组聚合是数据分析的基础，配合HAVING筛选分组结果
日期函数	DATEDIFF / DATE_ADD / unix_timestamp处理日期运算

八、建表语句和数据插入

点击展开 DDL & DML

CREATE TABLE travel_order (
    order_id VARCHAR(10) PRIMARY KEY,
    user_id VARCHAR(10),
    destination VARCHAR(20),
    travel_date DATE,
    days INT,
    amount DECIMAL(10,2)
);

INSERT INTO travel_order VALUES
('ORD001', 'U100', '三亚', '2024-01-15', 5, 6200),
('ORD002', 'U100', '丽江', '2024-03-20', 4, 3800),
('ORD003', 'U100', '三亚', '2024-07-10', 6, 7200),
('ORD004', 'U101', '成都', '2024-02-10', 3, 2500),
('ORD005', 'U101', '成都', '2024-05-15', 4, 3100),
('ORD006', 'U101', '重庆', '2024-08-20', 3, 2200),
('ORD007', 'U101', '成都', '2024-10-01', 5, 4500),
('ORD008', 'U102', '三亚', '2024-04-05', 4, 4800),
('ORD009', 'U102', '厦门', '2024-06-18', 3, 2800),
('ORD010', 'U102', '桂林', '2024-09-12', 4, 3200),
('ORD011', 'U103', '西安', '2024-03-08', 3, 2100),
('ORD012', 'U103', '丽江', '2024-07-25', 5, 5200);

📱关注公众号

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

💬加群交流

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

一、题目背景​

一、题目​

三、思路分析​

四、逐步推导​

五、常见坑点​

六、举一反三​

七、知识点总结​

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

你可能还想看