SQL实现同表相同did相邻行匹配生成带lat2、lon2列视图的问询

数据老陈

2026-10-8

同did相邻轨迹点合并视图SQL实现

前提说明

假设你的原始数据表名为track_points，以下方案基于SQL窗口函数实现，兼容MySQL 8.0+、PostgreSQL、Oracle等主流数据库，对百万级数据友好，配合索引可高效执行。

实现逻辑

用PARTITION BY did按设备标识分组，仅处理同一did下的记录
用ORDER BY num按序号排序，保证取到的是相邻的下一条记录
自动过滤did不一致、无下一条终点的无效记录

代码实现

通用窗口函数写法（推荐）

-- 创建合并后的轨迹线段视图
CREATE VIEW track_segments AS
SELECT
  t.num,
  t.did,
  t.lat1,
  t.lon1,
  t.next_lat AS lat2,
  t.next_lon AS lon2
FROM (
  SELECT
    num,
    did,
    lat1,
    lon1,
    -- 取同did下按num排序的下一行经纬度作为终点
    LEAD(lat1, 1) OVER (PARTITION BY did ORDER BY num) AS next_lat,
    LEAD(lon1, 1) OVER (PARTITION BY did ORDER BY num) AS next_lon
  FROM track_points -- 替换为你实际的原始表名
) t
-- 过滤没有终点的最后一条记录
WHERE t.next_lat IS NOT NULL;

低版本MySQL兼容写法（无窗口函数支持时使用）

-- 需提前为did、num字段创建联合索引优化性能
CREATE VIEW track_segments AS
SELECT
  t1.num,
  t1.did,
  t1.lat1,
  t1.lon1,
  t2.lat1 AS lat2,
  t2.lon1 AS lon2
FROM track_points t1
INNER JOIN track_points t2
  ON t1.did = t2.did 
  AND t2.num = (SELECT MIN(num) FROM track_points WHERE did = t1.did AND num > t1.num);