如何计算同用户相邻行的地理距离？SQL实现求助

原始表结构及数据 #

预期结果表 #

已尝试的SQL语句 #

SELECT 
      datetime(t1.created_at) period
     , t1.user_id
     , t1.action
     , t1.Latitude AS LatFrom
     , t1.Longitude AS LongFrom
     , t2.Latitude AS LatTo
     , t2.Longitude AS LongTo
FROM geolocation t1
LEFT OUTER JOIN geolocation t2 ON t2.promotor_id=t1.promotor_id AND t2.id > t1.id
WHERE 
group by 1,2,3,4,5,6,7
Order by 1

优化方案及SQL #

原SQL存在两个核心问题：

1. 关联字段错误：误用promotor_id而非需求中的user_id；
2. 关联逻辑错误：t2.id > t1.id会关联当前行之后的所有行，无法精准获取下一行数据。

正确做法是用窗口函数LEAD()，按user_id分组、period排序，直接获取同用户下一行的经纬度，再通过Haversine公式计算地理距离（单位：米）：

SELECT
    id,
    period,
    user_id,
    action,
    ROUND(latitude, 3) AS latitude,
    ROUND(longitude, 3) AS longitude,
    ROUND(LEAD(latitude) OVER (PARTITION BY user_id ORDER BY period), 3) AS to_latitude,
    ROUND(LEAD(longitude) OVER (PARTITION BY user_id ORDER BY period), 3) AS to_longitude,
    -- Haversine公式计算两点间地表距离（单位：米）
    CASE
        WHEN LEAD(latitude) OVER (PARTITION BY user_id ORDER BY period) IS NOT NULL
        THEN ROUND(
            6371000 * 2 * ASIN(
                SQRT(
                    POW(SIN((LEAD(latitude) OVER (PARTITION BY user_id ORDER BY period) - latitude) * PI()/180 / 2), 2) +
                    COS(latitude * PI()/180) * COS(LEAD(latitude) OVER (PARTITION BY user_id ORDER BY period) * PI()/180) *
                    POW(SIN((LEAD(longitude) OVER (PARTITION BY user_id ORDER BY period) - longitude) * PI()/180 / 2), 2)
                )
            )
        , 0)
        ELSE NULL
    END AS distance_meters
FROM geolocation
ORDER BY user_id, period;

说明 #

• LEAD() OVER (PARTITION BY user_id ORDER BY period)：按用户分组、时间排序，精准获取当前行的下一行经纬度数据；
• Haversine公式：基于球面几何计算两点间的地表距离，结果单位为米；
• ROUND()函数：匹配预期结果的小数位数，距离取整显示。

内容的提问来源于stack exchange，提问作者D_Q