SQL 用户旅行目的地偏好：出行目的地TOP N统计（携程面试题）
一、题目
携程需要为每个用户打上"首选目的地"标签，用于个性化推荐。给定 t4_travel_order 表，记录了用户的旅行订单信息。
t4_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：按用户和目的地聚合统计，计算访问次数、最近访问日期和总消费金额 执行SQL
select
user_id,
destination,
count(*) as visit_count,
max(travel_date) as last_visit_date,
sum(amount) as total_amount
from t4_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 |
+----------+--------------+--------------+------------------+---------------+
9 rows selected (9.337 seconds)(https://www.dwsql.com)
2.排名取首选目的地
按次数降序、最近日期降序排名，取每个用户排名第一的目的地。
执行SQL
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 t4_travel_order
group by user_id, destination
)
-- 按次数+最近日期排名，取每个用户的第一名
select
user_id,
destination as top_destination,
visit_count,
last_visit_date,
total_amount
from (
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
) 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 |
+----------+------------------+--------------+------------------+---------------+
4 rows selected (0.773 seconds)（https://www.dwsql.com）
四、常见坑点
坑1：row_number() 多列排序易写错顺序 — order by visit_count desc, last_visit_date desc 中，第一排序键按次数降序保证次数最多的排第一，第二排序键按最近日期降序保证并列时取最近一次。如果把 last_visit_date desc 放在前面，会变成按日期优先排序，逻辑完全错误。
坑2：count(*) vs count(column) 的区别 — count(*) 统计所有行（包括NULL），而 count(column) 只统计该列非NULL的行。在统计访问次数时，应使用 count(*) 确保每笔订单都被计入。如果某列存在NULL值，count(column) 会漏算数据。
坑3：多个用户可能多次访问同一目的地，需注意去重场景 — 如果一个用户在同一天对同一目的地有多条订单记录，group by user_id, destination 会正确合并这些记录。但如果需求是"统计用户去过多少个不同目的地"而非"每个目的地去了几次"，则需要在聚合前去重。
五、知识点总结
| 考点 | 说明 |
|---|---|
| row_number() + partition by | 窗口函数实现组内排序和排名，partition by 定义分组，order by 定义排序规则 |
| group by + 聚合函数 | count(*)、max()、sum() 等配合 group by 实现分组统计，是数据分析的基础操作 |
| 子查询 / CTE 分层 | 使用 with ... as 将复杂查询拆分为多个逻辑层，先聚合再排名最后筛选，结构清晰易维护 |
六、建表语句和数据插入
点击展开 DDL & DML
-- 用户旅行订单表
create table t4_travel_order (
order_id string comment '订单ID',
user_id string comment '用户ID',
destination string comment '目的地',
travel_date string comment '出行日期',
days int comment '出行天数',
amount int comment '订单金额'
) comment '用户旅行订单表';
insert into t4_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真题
