百度大数据面试SQL-地图路径还原：用户位置轨迹合并
⚠️ 待修正
一、题目背景
这道题来自百度地图事业部的数据分析岗面试。百度地图每天收集数十亿条用户定位数据，数据团队需要从离散的GPS点还原完整的出行路径——同一段行程内的轨迹点被合并为一次"出行"，不同行程之间通过时间和空间距离切分。
业务场景：路径还原是LBS数据分析的基础。还原后的路径可用于通勤模式识别（家⇔公司）、交通拥堵分析、POI推荐（"你常去的咖啡店"）等场景。这道题简化为只按时间间隔切分，实际生产中还需要加上距离阈值。
二、题目
现有一张用户位置轨迹表 t8_user_trajectory，记录了用户在不同时间点的GPS位置。如果同一用户两个相邻轨迹点的时间间隔小于10分钟，则认为属于同一条出行路径。请将同一用户的出行路径进行合并，输出每条路径的起始时间、结束时间和轨迹点数。
用户位置轨迹表 t8_user_trajectory：
+----------+---------------------+----------+----------+
| user_id | loc_time | loc_lng | loc_lat |
+----------+---------------------+----------+----------+
| u01 | 2023-03-01 08:00:00 | 116.397 | 39.908 |
| u01 | 2023-03-01 08:05:00 | 116.398 | 39.909 |
| u01 | 2023-03-01 08:12:00 | 116.400 | 39.910 |
| u01 | 2023-03-01 12:00:00 | 116.450 | 39.950 |
| u01 | 2023-03-01 12:08:00 | 116.452 | 39.952 |
| u02 | 2023-03-01 09:00:00 | 121.473 | 31.230 |
| u02 | 2023-03-01 09:06:00 | 121.475 | 31.232 |
| u02 | 2023-03-01 09:30:00 | 121.480 | 31.240 |
+----------+---------------------+----------+----------+
简化为：相邻两点时间差 > 10分钟则认为路径中断，开启新路径。
三、思路分析
这是LBS轨迹数据的连续区间合并问题，Gaps and Islands的变体：
- LAG获取前一点时间：
LAG(loc_time) OVER (PARTITION BY user_id ORDER BY loc_time) - 判断路径断点：时间差 > 10分钟（600秒）→ is_new_path = 1
- 累积求和生成路径ID：
SUM(is_new_path) OVER (...)累积标记值 - 路径聚合：按 path_id GROUP BY，取 MIN/MAX时间、COUNT轨迹点数、计算时长
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️ |
四、逐步推导
步骤1：LAG获取前一个轨迹点时间
select user_id, loc_time, loc_lng, loc_lat,
lag(loc_time, 1) over (partition by user_id order by loc_time) as prev_loc_time
from t8_user_trajectory
步骤2：标记新路径起点 + 累积生成路径ID
select user_id, loc_time, loc_lng, loc_lat,
concat(user_id, '_',
sum(is_new_path) over (partition by user_id order by loc_time)) as path_id
from (
select user_id, loc_time, loc_lng, loc_lat,
case
when prev_loc_time is null then 1
when (unix_timestamp(loc_time) - unix_timestamp(prev_loc_time)) / 60 > 10 then 1
else 0
end as is_new_path
from (
select user_id, loc_time, loc_lng, loc_lat,
lag(loc_time, 1) over (partition by user_id order by loc_time) as prev_loc_time
from t8_user_trajectory
) t1
) t2
u01的08:00-08:12三个点间隔均<10分钟，属同一路径(u01_1)。12:00距08:12间隔228分钟>10分钟，开启新路径(u01_2)。
步骤3：按路径ID聚合输出路径概要
执行SQL
select user_id, path_id,
min(loc_time) as path_start_time,
max(loc_time) as path_end_time,
count(1) as point_cnt,
round((unix_timestamp(max(loc_time)) - unix_timestamp(min(loc_time))) / 60, 2) as duration_min
from (
select user_id, loc_time, loc_lng, loc_lat,
concat(user_id, '_',
sum(is_new_path) over (partition by user_id order by loc_time)) as path_id
from (
select user_id, loc_time, loc_lng, loc_lat,
case
when prev_loc_time is null then 1
when (unix_timestamp(loc_time) - unix_timestamp(prev_loc_time)) / 60 > 10 then 1
else 0
end as is_new_path
from (
select user_id, loc_time, loc_lng, loc_lat,
lag(loc_time, 1) over (partition by user_id order by loc_time) as prev_loc_time
from t8_user_trajectory
) t1
) t2
) t3
group by user_id, path_id
order by user_id, path_start_time
执行结果
+----------+---------+---------------------+---------------------+-----------+--------------+
| user_id | path_id | path_start_time | path_end_time | point_cnt | duration_min |
+----------+---------+---------------------+---------------------+-----------+--------------+
| u01 | u01_1 | 2023-03-01 08:00:00 | 2023-03-01 08:12:00 | 3 | 12.00 |
| u01 | u01_2 | 2023-03-01 12:00:00 | 2023-03-01 12:08:00 | 2 | 8.00 |
| u02 | u02_1 | 2023-03-01 09:00:00 | 2023-03-01 09:06:00 | 2 | 6.00 |
| u02 | u02_2 | 2023-03-01 09:30:00 | 2023-03-01 09:30:00 | 1 | 0.00 |
+----------+---------+---------------------+---------------------+-----------+--------------+
u01 早上通勤路径(u01_1)持续12分钟3个轨迹点，中午短途(u01_2)8分钟2个点。u02_2只有一个点（前后都断开了），时长为0——这种"孤立点"在实际中可能是GPS信号丢失后恢复的瞬间。
五、常见坑点
坑1：距离维度被简化了
本题只按时间判断路径中断。实际生产中还需要加上距离判断——两个点时间间隔<10分钟但距离>50公里（可能飞机/高铁），应该算新路径。距离计算用Haversine公式或直接用ST_Distance（如果数据库支持空间函数）。
坑2：孤立的单个轨迹点
如果某个点前后都超过了10分钟间隔，它自己单独形成一个路径，duration=0。这种"单点路径"在业务上可能被视为无效数据（GPS漂移），需要加 WHERE point_cnt >= 2 过滤。
坑3：四层子查询嵌套的可读性
虽然嵌套深，但每层职责清晰：获取前一时间→打标记→累积分组→聚合输出。可以用CTE（WITH子句）替代嵌套，提升可读性。
六、举一反三
- 加入距离阈值：在 CASE WHEN 中加入 Haversine 距离计算，同时满足时间<10分钟 AND 距离<500米才算同一路径
- 识别停留点：如果连续N个点之间的距离都小于10米（GPS误差范围），判断为用户"停留"在某地
- 驻留区域提取：在上述路径基础上，用空间聚类（DBSCAN）识别用户的常驻区域——家、公司、健身房
- 路径速度异常检测：duration / distance 算出平均速度，标记速度>120km/h的点（可能是GPS漂移/地铁）
七、知识点总结
| 考点 | 说明 |
|---|---|
| LAG + 时间差判断断点 | 获取前一轨迹点，计算间隔判断是否新路径 |
| SUM OVER 累积分组 | 对0/1断点标记累积求和，生成递增的路径ID |
| Gaps and Islands | 连续区间合并的通用解法，适用于轨迹/Session/日志 |
| 路径聚合 | MIN/MAX取首尾时间，COUNT得轨迹点数，时间差得持续时长 |
八、建表语句和数据插入
点击展开 DDL & DML
CREATE TABLE t8_user_trajectory (
user_id string COMMENT '用户ID',
loc_time string COMMENT '定位时间',
loc_lng double COMMENT '经度',
loc_lat double COMMENT '纬度'
) COMMENT '用户位置轨迹表';
INSERT INTO t8_user_trajectory VALUES
('u01', '2023-03-01 08:00:00', 116.397, 39.908),
('u01', '2023-03-01 08:05:00', 116.398, 39.909),
('u01', '2023-03-01 08:12:00', 116.400, 39.910),
('u01', '2023-03-01 12:00:00', 116.450, 39.950),
('u01', '2023-03-01 12:08:00', 116.452, 39.952),
('u02', '2023-03-01 09:00:00', 121.473, 31.230),
('u02', '2023-03-01 09:06:00', 121.475, 31.232),
('u02', '2023-03-01 09:30:00', 121.480, 31.240);
📱关注公众号
「数据仓库技术」文章同步更新，不错过每一篇干货
💬加群交流
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
