SQL 差旅用户出差频次：出行次数分布统计（携程面试题）
一、题目
统计上半年各用户出差次数+金额，按频次分级(高频≥5/中频3-4/低频1-2)，统计各级别人数和占比。给定 t2_business_travel 表：
+-----------+----------+----------+-----------------+--------------+--------------+---------+
| order_id | user_id | company | departure_date | return_date | destination | amount |
+-----------+----------+----------+-----------------+--------------+--------------+---------+
| BT001 | U001 | 华为 | 2025-01-10 | 2025-01-12 | 深圳 | 3200 |
| BT002 | U001 | 华为 | 2025-02-15 | 2025-02-18 | 北京 | 2800 |
| BT003 | U002 | 阿里巴巴 | 2025-01-05 | 2025-01-07 | 上海 | 1500 |
| BT004 | U003 | 腾讯 | 2025-03-01 | 2025-03-03 | 北京 | 2100 |
| BT005 | U001 | 华为 | 2025-03-20 | 2025-03-22 | 广州 | 1800 |
| BT006 | U001 | 华为 | 2025-04-10 | 2025-04-13 | 成都 | 3500 |
| BT007 | U002 | 阿里巴巴 | 2025-04-15 | 2025-04-17 | 深圳 | 2200 |
| BT008 | U001 | 华为 | 2025-05-08 | 2025-05-10 | 上海 | 2600 |
| BT009 | U003 | 腾讯 | 2025-05-20 | 2025-05-22 | 杭州 | 1900 |
| BT010 | U002 | 阿里巴巴 | 2025-06-01 | 2025-06-03 | 南京 | 1600 |
+-----------+----------+----------+-----------------+--------------+--------------+---------+
要求：
- 统计上半年(1-6月)每个用户的出差总次数、总消费金额
- 按频次分级：高频(≥5次)、中频(3-4次)、低频(1-2次)
- 统计各级别的用户数和占比
二、思路分析
本题考察分层聚合统计，核心在于先做用户级聚合，再在聚合结果之上做二次分组统计。考察 count、sum、case when 条件分组，以及子查询/CTE的嵌套使用。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐⭐ |
| 题目清晰度 | ⭐⭐⭐⭐⭐ |
| 业务常见度 | ⭐⭐⭐⭐⭐ |
解题思路：
- 第一步：按
user_id聚合，统计每个用户的出差次数、总金额 - 第二步：在第一步结果上，使用
case when按出差次数分级 - 第三步：按频次等级聚合，计算各等级的用户数及占比
- 注意：占比分母为总用户数，可使用窗口函数
sum() over()或子查询
三、逐步推导
1. 按用户聚合出差指标并分级
先统计每个用户上半年的出差次数和总金额，再使用 case when 按次数分级。
执行SQL：
select
user_id,
max(company) as company,
count(*) as travel_count,
sum(amount) as total_amount,
case
when count(*) >= 5 then '高频(≥5次)'
when count(*) >= 3 then '中频(3-4次)'
else '低频(1-2次)'
end as frequency_level
from t2_business_travel
where departure_date >= '2025-01-01'
and departure_date < '2025-07-01'
group by user_id
order by travel_count desc;
结果：
+----------+----------+---------------+---------------+------------------+
| user_id | company | travel_count | total_amount | frequency_level |
+----------+----------+---------------+---------------+------------------+
| U001 | 华为 | 5 | 13900 | 高频(≥5次) |
| U002 | 阿里巴巴 | 3 | 5300 | 中频(3-4次) |
| U003 | 腾讯 | 2 | 4000 | 低频(1-2次) |
+----------+----------+---------------+---------------+------------------+
3 rows selected (0.374 seconds)(https://www.dwsql.com)
2. 统计各级别用户数及占比
在上一步结果基础上，按频次等级聚合，使用 sum(count(*)) over() 窗口函数计算全局总用户数作为分母。
执行SQL：
with user_stats as (
-- 按用户聚合出差次数和总金额
select
user_id,
count(*) as travel_count,
sum(amount) as total_amount
from t2_business_travel
where departure_date >= '2025-01-01'
and departure_date < '2025-07-01'
group by user_id
),
user_level as (
-- 出差次数分级
select
user_id,
travel_count,
total_amount,
case
when travel_count >= 5 then '高频(≥5次)'
when travel_count >= 3 then '中频(3-4次)'
else '低频(1-2次)'
end as frequency_level
from user_stats
),
level_stats as (
-- 按等级聚合
select
frequency_level,
count(*) as user_cnt,
sum(travel_count) as total_travel_cnt,
round(avg(travel_count), 1) as avg_travel_cnt
from user_level
group by frequency_level
)
-- 窗口函数计算占比（Spark SQL要求聚合结果先落地再套窗口）
select
frequency_level,
user_cnt,
total_travel_cnt,
avg_travel_cnt,
round(user_cnt * 1.0 / sum(user_cnt) over(), 4) as user_ratio
from level_stats
order by avg_travel_cnt desc;
结果：
+------------------+-----------+-------------------+-----------------+-------------+
| frequency_level | user_cnt | total_travel_cnt | avg_travel_cnt | user_ratio |
+------------------+-----------+-------------------+-----------------+-------------+
| 高频(≥5次) | 1 | 5 | 5.0 | 0.3333 |
| 中频(3-4次) | 1 | 3 | 3.0 | 0.3333 |
| 低频(1-2次) | 1 | 2 | 2.0 | 0.3333 |
+------------------+-----------+-------------------+-----------------+-------------+
3 rows selected (0.455 seconds)(https://www.dwsql.com)
四、常见坑点
坑1：case when 条件顺序
case when 从上到下匹配，先命中先生效。必须把高阈值条件（>= 5）放在 >= 3 前面，否则 5 次的用户也会被归入中频。反过来从小到大写也可以，但关键是避免条件区间重叠导致误归类。
坑2：sum() over() 分母是全局还是分区
sum(count(*)) over() 不带 partition by 才是全局总用户数，作为每个等级的占比分母。如果误加 partition by frequency_level，分母变成组内用户数，每行占比都等于 1。
坑3：count 与 count distinct
统计用户数时，group by user_id 已经去重，直接用 count(*) 即可。若在前面没有分组的情况下统计不重复用户数，则需要 count(distinct user_id)，否则会把同一个用户的多条记录重复计数。
五、知识点总结
| 考点 | 说明 |
|---|---|
| case when 分级 | 按阈值分段分类，注意条件顺序避免区间重叠覆盖 |
| sum + round | 聚合求和后用 round(x, 4) 统一保留小数精度 |
| 子查询两层聚合 | 先按用户维度聚合，再在聚合结果上按频次等级二次聚合 |
| sum() over() 窗口占比 | 不分区时计算全局总量作为分母，配合 count(*) 求各级别占比 |
六、建表语句和数据插入
点击展开 DDL & DML
create table t2_business_travel (
order_id string comment '订单id',
user_id string comment '用户id',
company string comment '公司名称',
departure_date string comment '出发日期',
return_date string comment '返回日期',
destination string comment '目的地',
amount int comment '消费金额'
) comment '差旅订单表';
insert into t2_business_travel values
('BT001', 'U001', '华为', '2025-01-10', '2025-01-12', '深圳', 3200),
('BT002', 'U001', '华为', '2025-02-15', '2025-02-18', '北京', 2800),
('BT003', 'U002', '阿里巴巴', '2025-01-05', '2025-01-07', '上海', 1500),
('BT004', 'U003', '腾讯', '2025-03-01', '2025-03-03', '北京', 2100),
('BT005', 'U001', '华为', '2025-03-20', '2025-03-22', '广州', 1800),
('BT006', 'U001', '华为', '2025-04-10', '2025-04-13', '成都', 3500),
('BT007', 'U002', '阿里巴巴', '2025-04-15', '2025-04-17', '深圳', 2200),
('BT008', 'U001', '华为', '2025-05-08', '2025-05-10', '上海', 2600),
('BT009', 'U003', '腾讯', '2025-05-20', '2025-05-22', '杭州', 1900),
('BT010', 'U002', '阿里巴巴', '2025-06-01', '2025-06-03', '南京', 1600);
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
