LAG函数需用PARTITION BY device_id ORDER BY timestamp提取同车前序GPS数据，否则跨车混序导致漂移误判；经纬度差值无地理意义，须用ST_DistanceSphere或Haversine计算真实距离。
LAG函数怎么拿到上一条GPS记录的经纬度和时间
直接用 LAG() 提取前一行的字段是基础，但车载数据常按设备ID+时间排序，漏掉 PARTITION BY device_id ORDER BY timestamp 就会跨车混序，导致漂移误判。常见错误是只写 ORDER BY timestamp，结果A车的最后一条记录和B车的第一条记录被连起来算差值。
实操建议：
- 必须显式
PARTITION BY device_id，避免不同车辆轨迹串扰 -
timestamp字段需确认是TIMESTAMP或带时区的TIMESTAMPTZ，若为字符串先用TO_TIMESTAMP()转换，否则排序错乱 - 为防空值干扰，
LAG(lat, 1, NULL)显式设默认值，别依赖隐式NULL
计算位移距离时为什么不能直接用经纬度相减
用 (lat - LAG(lat)) + (lng - LAG(lng)) 算“距离”毫无地理意义——经纬度不是平面坐标，1°纬度≈111km，1°经度随纬度变化（赤道约111km，北京约85km）。直接相减得出的数值既不能比大小，也无法判断是否漂移。
实操建议：
- 用
ST_DistanceSphere()（PostGIS）或haversine手动公式，输入单位必须是POINT(lng lat)（注意：先经度后纬度） - 若无空间扩展，至少用简化版 Haversine：
6371 * acos(cos(radians(lat))*cos(radians(lag_lat))*cos(radians(lag_lng)-radians(lng)) + sin(radians(lat))*sin(radians(lag_lat))) - 位移阈值建议设为 200 米以内（城市道路正常采样间隔下），超过即标记疑似漂移
速度骤变怎么结合LAG和时间差算瞬时速度
瞬时速度 = 位移距离 ÷ 时间差，但 LAG(timestamp) 和当前 timestamp 相减结果类型依赖数据库：PostgreSQL 返回 interval，需转秒；MySQL 返回秒数；SQLite 需用 julianday() 差值 × 86400。
常见错误现象：速度算出来是 1e+6 km/h，大概率是时间差单位没统一（比如把毫秒当秒用）或位移单位是度没转公里。
实操建议：
- 时间差统一转为秒：
EXTRACT(EPOCH FROM (timestamp - LAG(timestamp)))（PG）；TIMESTAMPDIFF(SECOND, LAG(timestamp), timestamp)（MySQL） - 速度单位用 m/s 更稳妥，后续再转 km/h；避免直接算 km/h 后再做阈值判断（易因浮点误差漏判）
- 速度突变阈值建议分场景：城区车速 20 km/h per second 视为异常；高速场景可放宽至 35 km/h per second
为什么漂移和速度骤变要分开标记、不能合并在一个CASE WHEN里
因为漂移（位置跳变）和速度骤变（加速度异常）成因不同：前者多因GPS信号丢失后重捕获、多径反射；后者可能源于传感器采样抖动、CAN总线丢帧或真实急刹/急启。合并在一个逻辑里会掩盖根因，且不利于后续分别统计故障率。
实操建议：
- 用两个独立布尔字段：
is_drift（位移 > 200m 且时间差 is_speed_spike（加速度绝对值 > 5 m/s²） - 时间差过滤很重要——若两帧间隔 5 分钟，位移 500 米是正常停车挪位，不算漂移；同样，30 秒内加速到 60 km/h 是合理起步，不算骤变
- 上线前务必用真实轨迹片段验证：挑一段已知有隧道失联又恢复的轨迹，看
is_drift是否只在恢复首点触发
真正难的是时间精度对齐——GPS模块输出的时间戳、数据库写入时间、ETL任务延迟三者可能差几十毫秒，不统一用原始 gps_timestamp 字段，所有计算都会偏移。
