请求协助SQL语句重构路线数据，生成路线位置概览

看起来你是想把同一条路线里连续停留在同一地点的多个点位合并，得到每个地点的进入时间（该段最早的时间）和离开时间（该段最晚的时间）——这在轨迹数据处理里是很常见的需求，用窗口函数就能轻松实现。

先确认下你的示例数据（假设表名为route_points）：

routeid, time, location
1, 2017-04-06 14:15:33, Netherlands
1, 2017-04-06 14:15:35, Netherlands
1, 2017-04-06 14:15:38, Netherlands
1, 2017-04-06 14:15:42, Netherlands
1, 2017-04-06 14:15:48, Belgium
1, 2017-04-06 14:15:52, Belgium
1, 2017-04-06 14:15:56, Belgium
2, 2017-04-06 14:15:44, Netherlands
...

核心思路 #

我们需要先给连续相同的location段打上唯一的分组标签，然后按分组聚合时间。具体分三步：

1. 用LAG()函数获取每个点位的前一个点位的location，判断是否发生变化；
2. 对变化点进行累加，生成连续段的分组ID；
3. 最后按routeid、location和分组ID聚合，取每个段的最早和最晚时间。

完整SQL语句 #

WITH grouped_segments AS (
    SELECT 
        routeid,
        time,
        location,
        -- 当当前location和前一个不同时，标记为新组，累加得到组ID
        SUM(CASE WHEN prev_loc != location THEN 1 ELSE 0 END) OVER (
            PARTITION BY routeid ORDER BY time
        ) AS segment_id
    FROM (
        SELECT 
            routeid,
            time,
            location,
            -- 获取同一路线中前一个点位的location
            LAG(location) OVER (
                PARTITION BY routeid ORDER BY time
            ) AS prev_loc
        FROM route_points
    ) t
)
SELECT 
    routeid,
    location,
    MIN(time) AS entry_time,
    MAX(time) AS exit_time
FROM grouped_segments
GROUP BY routeid, location, segment_id
ORDER BY routeid, entry_time;

结果示例 #

运行后你会得到这样的精简结果：

routeid, location, entry_time, exit_time
1, Netherlands, 2017-04-06 14:15:33, 2017-04-06 14:15:42
1, Belgium, 2017-04-06 14:15:48, 2017-04-06 14:15:56
2, Netherlands, 2017-04-06 14:15:44, ...

注意事项 #

• 这个写法支持PostgreSQL、MySQL 8.0+、SQL Server 2012+等支持窗口函数的数据库；
• 如果你的数据库不支持窗口函数（比如老版本MySQL），可以用变量来实现分组，但窗口函数的写法更清晰易维护；
• 确保time字段是可排序的日期时间类型，否则分组逻辑会出错。

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