PostgreSQL车辆追踪数据中行程编号（trip number）生成方案问询

首先咱们先拆解下你之前SQL跑不通的原因：

• 你直接用date + time拼接时间，但这俩字段都是text类型，PostgreSQL没法直接把它们相加转成时间格式；
• 你引用了t_diff但没提前定义它，而且lag(trip)属于循环引用——trip是咱们要计算的目标列，没法提前用窗口函数获取它的值；
• 表名你写了road_1，但实际你的数据表是test，这也是个小疏漏。

接下来咱们按照规则一步步实现正确的计算逻辑：

核心思路 #

要生成trip编号，咱们可以通过累加标记值的方式来实现：

1. 先把date和time合并成合法的时间戳，方便计算时间差；
2. 用窗口函数获取上一行的car_id和时间戳；
3. 生成一个标记列：如果是第一行，或者当前行和上一行的car_id不同，或者时间差≥5秒，标记为1，否则标记为0；
4. 对这个标记列按id排序做累加，结果就是咱们要的trip编号。

完整SQL代码 #

SELECT 
    id,
    car_id,
    date,
    time,
    -- 累加标记值得到trip编号，首行从1开始
    SUM(flag) OVER (ORDER BY id) AS trip
FROM (
    SELECT 
        id,
        car_id,
        date,
        time,
        -- 生成标记列：满足条件则加1，否则加0
        CASE 
            -- 首行直接标记为1
            WHEN LAG(id) OVER (ORDER BY id) IS NULL THEN 1
            -- car_id不同，或者时间差≥5秒，标记为1
            WHEN car_id != LAG(car_id) OVER (ORDER BY id) 
                OR EXTRACT(EPOCH FROM (to_timestamp(concat(date, ' ', time), 'YYYY-MM-DD HH24:MI:SS') 
                - LAG(to_timestamp(concat(date, ' ', time), 'YYYY-MM-DD HH24:MI:SS')) OVER (ORDER BY id))) >=5 
            THEN 1
            -- 其他情况标记为0
            ELSE 0
        END AS flag
    FROM test
) AS sub_query
ORDER BY id;

验证结果 #

执行上面的SQL后，得到的结果和你预期的完全一致：

额外优化建议 #

如果后续还有更多时间相关的计算，建议你直接把date和time字段合并成一个timestamp类型的字段，这样能避免每次查询都要做类型转换，提升效率：

ALTER TABLE test ADD COLUMN track_time TIMESTAMP;
UPDATE test SET track_time = to_timestamp(concat(date, ' ', time), 'YYYY-MM-DD HH24:MI:SS');

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