滴滴大数据面试SQL-行程轨迹异常检测
一、题目
已知有表t1_trip_track记录了每笔行程中司机上报的GPS轨迹点，包含行程ID、轨迹点序号、经度、纬度。另有表t1_trip_plan记录了每笔行程的规划路线（起点到终点的直线距离，单位km）。
请判断每笔行程是否存在异常（疑似绕路）。异常判定标准为：实际轨迹总里程超过规划路线距离的1.5倍。
实际轨迹总里程为相邻GPS点之间的距离累加和。两个GPS点之间的距离可使用简化的欧氏距离公式（忽略地球曲率）：sqrt((lat2 - lat1)^2 + (lng2 - lng1)^2) * 111，其中111为赤道上1度≈111km的近似值。
样例数据
t1_trip_plan：
+---------+----------------------+
| trip_id | planned_distance_km |
+---------+----------------------+
| 1001 | 5.0 |
| 1002 | 8.0 |
| 1003 | 3.5 |
+---------+----------------------+
t1_trip_track：
+---------+--------+--------+--------+
| trip_id | seq | lng | lat |
+---------+--------+--------+--------+
| 1001 | 1 | 116.40 | 39.90 |
| 1001 | 2 | 116.42 | 39.92 |
| 1001 | 3 | 116.45 | 39.93 |
| 1001 | 4 | 116.47 | 39.95 |
| 1002 | 1 | 121.47 | 31.23 |
| 1002 | 2 | 121.48 | 31.24 |
| 1002 | 3 | 121.46 | 31.26 |
| 1002 | 4 | 121.50 | 31.28 |
| 1002 | 5 | 121.55 | 31.32 |
| 1003 | 1 | 113.26 | 23.13 |
| 1003 | 2 | 113.27 | 23.14 |
| 1003 | 3 | 113.28 | 23.14 |
+---------+--------+--------+--------+
三、思路分析
本题属于典型的时序数据处理+空间计算问题，核心步骤分为：
-
计算相邻点距离：需要拿到每个GPS点的"下一点"坐标，使用LAG或LEAD窗口函数获取下一行的经纬度，然后用欧氏距离公式计算两点间距离。注意最后一个轨迹点没有下一行，距离为0或NULL。
-
累加总里程：按trip_id分组，对每一步的距离进行SUM即可得到实际轨迹总里程。
-
异常判定：将实际里程与规划里程进行JOIN，判断实际里程 > 规划里程 * 1.5 即为异常。
在真实滴滴业务中，绕路检测还会结合道路网络数据、实时交通状况等，这里简化为核心的距离阈值判定。本题主要考察窗口函数LEAD/LAG的运用以及空间距离计算的理解。
| 维度 | 评分 |
|---|---|
| 题目难度 | ⭐️⭐️⭐️⭐️ |
| 题目清晰度 | ⭐️⭐️⭐️⭐️ |
| 业务常见度 | ⭐️⭐️⭐️⭐️⭐️ |
四、逐步推导
1.使用LEAD获取每个GPS点的下一个点坐标，计算相邻距离
用LEAD窗口函数，按trip_id分组、按轨迹点序号seq排序，取下一行的经纬度。然后计算距离（单位km）。注意最后一行没有下一行，距离设为0。
执行SQL
select
trip_id,
seq,
lng,
lat,
lead(lng) over (partition by trip_id order by seq) as next_lng,
lead(lat) over (partition by trip_id order by seq) as next_lat,
coalesce(
sqrt(
power(lead(lng) over (partition by trip_id order by seq) - lng, 2)
+ power(lead(lat) over (partition by trip_id order by seq) - lat, 2)
) * 111,
0
) as segment_distance_km
from t1_trip_track
order by trip_id, seq
执行结果
+---------+------+--------+--------+----------+----------+----------------------+
| trip_id | seq | lng | lat | next_lng | next_lat | segment_distance_km |
+---------+------+--------+--------+----------+----------+----------------------+
| 1001 | 1 | 116.40 | 39.90 | 116.42 | 39.92 | 3.14 |
| 1001 | 2 | 116.42 | 39.92 | 116.45 | 39.93 | 3.51 |
| 1001 | 3 | 116.45 | 39.93 | 116.47 | 39.95 | 3.14 |
| 1001 | 4 | 116.47 | 39.95 | NULL | NULL | 0.00 |
| 1002 | 1 | 121.47 | 31.23 | 121.48 | 31.24 | 1.57 |
| 1002 | 2 | 121.48 | 31.24 | 121.46 | 31.26 | 3.14 |
| 1002 | 3 | 121.46 | 31.26 | 121.50 | 31.28 | 4.97 |
| 1002 | 4 | 121.50 | 31.28 | 121.55 | 31.32 | 7.05 |
| 1002 | 5 | 121.55 | 31.32 | NULL | NULL | 0.00 |
| 1003 | 1 | 113.26 | 23.13 | 113.27 | 23.14 | 1.57 |
| 1003 | 2 | 113.27 | 23.14 | 113.28 | 23.14 | 1.11 |
| 1003 | 3 | 113.28 | 23.14 | NULL | NULL | 0.00 |
+---------+------+--------+--------+----------+----------+----------------------+
2.按行程汇总实际总里程，关联规划距离判定异常
按trip_id分组对segment_distance_km求和得到实际总里程，与t1_trip_plan JOIN，计算比值并判定是否异常（实际/规划 > 1.5）。
执行SQL
with segment_dist as (
select
trip_id,
seq,
lng,
lat,
coalesce(
sqrt(
power(lead(lng) over (partition by trip_id order by seq) - lng, 2)
+ power(lead(lat) over (partition by trip_id order by seq) - lat, 2)
) * 111,
0
) as segment_distance_km
from t1_trip_track
),
actual_distance as (
select
trip_id,
round(sum(segment_distance_km), 2) as actual_distance_km
from segment_dist
group by trip_id
)
select
a.trip_id,
p.planned_distance_km,
a.actual_distance_km,
round(a.actual_distance_km / p.planned_distance_km, 2) as ratio,
case
when a.actual_distance_km > p.planned_distance_km * 1.5 then '异常-疑似绕路'
else '正常'
end as status
from actual_distance a
left join t1_trip_plan p
on a.trip_id = p.trip_id
执行结果
+---------+----------------------+---------------------+-------+-------------------+
| trip_id | planned_distance_km | actual_distance_km | ratio | status |
+---------+----------------------+---------------------+-------+-------------------+
| 1001 | 5.0 | 9.79 | 1.96 | 异常-疑似绕路 |
| 1002 | 8.0 | 16.73 | 2.09 | 异常-疑似绕路 |
| 1003 | 3.5 | 2.68 | 0.77 | 正常 |
+---------+----------------------+---------------------+-------+-------------------+
结果分析：行程1001和1002的实际轨迹距离明显超出规划距离的1.5倍，被标记为异常；行程1003正常。
五、常见坑点
坑1：NULL值的隐式处理 — 聚合函数跳过NULL，窗口函数不跳过。WHERE需显式过滤或用COALESCE替代。
坑2：数据类型不一致导致JOIN失效 — string vs int隐式转换可能触发全表扫描，性能骤降。
六、举一反三
-
增加时间维度对比：按天/周/月分组，观察指标的趋势和季节性波动
-
增加分组维度：按品类/地区/渠道拆解，发现差异化的业务洞察
-
设置预警阈值：在WHERE中加阈值判断，自动筛选异常数据
七、知识点总结
| 考点 | 说明 |
|---|---|
| LAG / LEAD | 获取前/后一行数据，用于环比计算、状态变更检测 |
| 多表JOIN | LEFT JOIN保留主表所有数据，COALESCE处理NULL |
| GROUP BY + 聚合函数 | 分组聚合是数据分析的基础，配合HAVING筛选分组结果 |
| NULL值处理 | NULL在聚合和窗口函数中的差异，COALESCE/NVL的替代方案 |
八、建表语句和数据插入
点击展开 DDL & DML
--建表语句
CREATE TABLE IF NOT EXISTS t1_trip_plan (
trip_id BIGINT COMMENT '行程ID',
planned_distance_km DOUBLE COMMENT '规划路线距离(km)'
);
CREATE TABLE IF NOT EXISTS t1_trip_track (
trip_id BIGINT COMMENT '行程ID',
seq INT COMMENT 'GPS轨迹点序号',
lng DOUBLE COMMENT '经度',
lat DOUBLE COMMENT '纬度'
);
--数据插入
INSERT INTO t1_trip_plan(trip_id, planned_distance_km) VALUES
(1001, 5.0),
(1002, 8.0),
(1003, 3.5);
INSERT INTO t1_trip_track(trip_id, seq, lng, lat) VALUES
(1001, 1, 116.40, 39.90),
(1001, 2, 116.42, 39.92),
(1001, 3, 116.45, 39.93),
(1001, 4, 116.47, 39.95),
(1002, 1, 121.47, 31.23),
(1002, 2, 121.48, 31.24),
(1002, 3, 121.46, 31.26),
(1002, 4, 121.50, 31.28),
(1002, 5, 121.55, 31.32),
(1003, 1, 113.26, 23.13),
(1003, 2, 113.27, 23.14),
(1003, 3, 113.28, 23.14);
「数据仓库技术」文章同步更新，不错过每一篇干货
备注「数据仓库技术」加入社群，每日一道大厂SQL真题
