SQL 用户轨迹还原：GPS位置点合并为连续路径（百度面试题）

一、题目

现有一张用户位置轨迹表 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的变体：

1. LAG获取前一点时间：LAG(loc_time) OVER (PARTITION BY user_id ORDER BY loc_time)
2. 判断路径断点：时间差 > 10分钟（600秒）→ is_new_path = 1
3. 累积求和生成路径ID：SUM(is_new_path) OVER (...) 累积标记值
4. 路径聚合：按 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

执行结果

+----------+----------------------+----------+----------+----------------------+
| user_id  |       loc_time       | loc_lng  | loc_lat  |    prev_loc_time     |
+----------+----------------------+----------+----------+----------------------+
| u01      | 2023-03-01 08:00:00  | 116.397  | 39.908   | NULL                 |
| u01      | 2023-03-01 08:05:00  | 116.398  | 39.909   | 2023-03-01 08:00:00  |
| u01      | 2023-03-01 08:12:00  | 116.4    | 39.91    | 2023-03-01 08:05:00  |
| u01      | 2023-03-01 12:00:00  | 116.45   | 39.95    | 2023-03-01 08:12:00  |
| u01      | 2023-03-01 12:08:00  | 116.452  | 39.952   | 2023-03-01 12:00:00  |
| u02      | 2023-03-01 09:00:00  | 121.473  | 31.23    | NULL                 |
| u02      | 2023-03-01 09:06:00  | 121.475  | 31.232   | 2023-03-01 09:00:00  |
| u02      | 2023-03-01 09:30:00  | 121.48   | 31.24    | 2023-03-01 09:06:00  |
+----------+----------------------+----------+----------+----------------------+
8 rows selected (1.831 seconds)(https://www.dwsql.com)

步骤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

执行结果

+----------+----------------------+----------+----------+----------+
| user_id  |       loc_time       | loc_lng  | loc_lat  | path_id  |
+----------+----------------------+----------+----------+----------+
| u01      | 2023-03-01 08:00:00  | 116.397  | 39.908   | u01_1    |
| u01      | 2023-03-01 08:05:00  | 116.398  | 39.909   | u01_1    |
| u01      | 2023-03-01 08:12:00  | 116.4    | 39.91    | u01_1    |
| u01      | 2023-03-01 12:00:00  | 116.45   | 39.95    | u01_2    |
| u01      | 2023-03-01 12:08:00  | 116.452  | 39.952   | u01_2    |
| u02      | 2023-03-01 09:00:00  | 121.473  | 31.23    | u02_1    |
| u02      | 2023-03-01 09:06:00  | 121.475  | 31.232   | u02_1    |
| u02      | 2023-03-01 09:30:00  | 121.48   | 31.24    | u02_2    |
+----------+----------------------+----------+----------+----------+
8 rows selected (1.055 seconds)(https://www.dwsql.com)

步骤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.0          |
| u01      | u01_2    | 2023-03-01 12:00:00  | 2023-03-01 12:08:00  | 2          | 8.0           |
| u02      | u02_1    | 2023-03-01 09:00:00  | 2023-03-01 09:06:00  | 2          | 6.0           |
| u02      | u02_2    | 2023-03-01 09:30:00  | 2023-03-01 09:30:00  | 1          | 0.0           |
+----------+----------+----------------------+----------------------+------------+---------------+
4 rows selected (1.056 seconds)(https://www.dwsql.com)

四、常见坑点

坑1：距离维度被简化了

本题只按时间判断路径中断。实际生产中还需要加上距离判断——两个点时间间隔<10分钟但距离>50公里（可能飞机/高铁），应该算新路径。距离计算用Haversine公式或直接用ST_Distance（如果数据库支持空间函数）。

坑2：孤立的单个轨迹点

如果某个点前后都超过了10分钟间隔，它自己单独形成一个路径，duration=0。这种"单点路径"在业务上可能被视为无效数据（GPS漂移），需要加 WHERE point_cnt >= 2 过滤。

坑3：四层子查询嵌套的可读性

虽然嵌套深，但每层职责清晰：获取前一时间→打标记→累积分组→聚合输出。可以用CTE（WITH子句）替代嵌套，提升可读性。

五、知识点总结

考点	说明
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真题