计算行间时间差：如何为最后一行使用额外时间戳

问题分析 #

你遇到的两个核心问题可以通过调整窗口函数和边界值处理来解决：

• 行偏移问题：当前用lag()取上一行时间，导致时间差对应到下一行，应该改用lead()获取下一条记录的时间，让停留时长和当前站点行匹配。
• 最后一行处理：用户最后一条记录没有后续数据，需要用COALESCE()函数，在lead()返回空值时自动替换为shift_stop，计算到班次结束的时长。

修改后的完整代码与查询 #

原数据表结构与插入数据 #

CREATE TABLE adata (
    id serial PRIMARY KEY,
    user_id INT NOT NULL,
    station_id INT NOT NULL,
    shift_stop TIMESTAMP NOT NULL,
    shift_date DATE NOT NULL,
    created_at TIMESTAMP NOT NULL,
    shift_start TIMESTAMP NOT NULL
);

insert into adata (id,user_id,station_id,shift_stop,shift_date,created_at, shift_start) values 
(1, 1, 1, '2022-01-01 15:00:00', '2022-01-01','2022-01-01 10:00:00','2022-01-01 10:00:00'),
(2, 2, 1, '2022-01-01 15:00:00', '2022-01-01','2022-01-01 10:01:00','2022-01-01 10:00:00'),
(3, 1, 2, '2022-01-01 15:00:00', '2022-01-01','2022-01-01 11:00:00','2022-01-01 10:00:00'),
(4, 2, 2, '2022-01-01 15:00:00', '2022-01-01','2022-01-01 12:00:00','2022-01-01 10:00:00'),
(5, 2, 3, '2022-01-01 15:00:00', '2022-01-01','2022-01-01 12:30:00','2022-01-01 10:00:00');

修正后的查询语句 #

SELECT
    t.user_id,
    t.station_id,
    t.created_at,
    -- 获取下一条记录时间，无后续记录则用班次结束时间
    COALESCE(lead(t.created_at) OVER (PARTITION BY t.user_id ORDER BY t.created_at), t.shift_stop) AS next_time,
    -- 计算停留时长（单位：秒），正数为实际停留时间
    EXTRACT(EPOCH FROM (COALESCE(lead(t.created_at) OVER (PARTITION BY t.user_id ORDER BY t.created_at), t.shift_stop) - t.created_at)) AS stay_seconds,
    t.id
FROM adata t
WHERE DATE(t.shift_date) = '2022-01-01'
ORDER BY t.user_id, t.created_at;

关键逻辑说明 #

• 解决行偏移：lead()窗口函数会获取当前行之后的下一条记录的created_at，让计算出的停留时长直接对应到当前站点的行，不再错位。
• 处理最后一行：COALESCE()优先取第一个非空值，当用户没有后续记录时，自动用shift_stop替换，确保最后一个站点的时长计算到班次结束。
• 若需要其他时间单位，可将stay_seconds除以60（分钟）或3600（小时）转换。

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