PostgreSQL中如何基于前一行数据计算状态持续时长？

刚好我之前处理过类似的状态变更时长计算需求，这个问题用PostgreSQL的窗口函数就能完美解决，核心思路就是按用户分组，追踪每个用户上一次状态变更的时间，再计算时间差。

具体实现SQL #

SELECT
    ID,
    Name,
    -- 取同一用户上一行的Status_To作为当前行的Status_from，第一行无前置记录则为NULL
    LAG(Status_To) OVER (PARTITION BY Name ORDER BY Change_Time) AS Status_from,
    Status_To,
    Change_Time,
    -- 计算当前行与上一行变更时间的差值（秒），第一行返回NULL或0
    CASE
        WHEN LAG(Change_Time) OVER (PARTITION BY Name ORDER BY Change_Time) IS NOT NULL
        THEN EXTRACT(EPOCH FROM (Change_Time - LAG(Change_Time) OVER (PARTITION BY Name ORDER BY Change_Time)))
        ELSE NULL -- 若需要默认0，直接改成ELSE 0即可
    END AS Duration
FROM
    your_table -- 替换成你的实际表名
ORDER BY
    Name, Change_Time;

关键逻辑解释 #

• PARTITION BY Name：把数据按用户分组，保证我们只在同一个用户的状态变更记录里计算时间差，不会混进其他用户的数据。
• ORDER BY Change_Time：每个用户的记录按变更时间排序，这样LAG()函数就能准确拿到上一次状态变更的时间，而不是随机的前一行。
• LAG()函数：这个窗口函数专门用来获取分组内前一行的指定字段值，这里分别用它取上一行的Status_To（作为当前行的Status_from）和Change_Time（用来计算时长）。
• EXTRACT(EPOCH FROM ...)：这是PostgreSQL里把时间间隔转换成秒数的标准方法，直接得到我们需要的秒级时长。
• CASE分支：处理每个用户的第一条记录——因为第一条没有上一行，LAG()会返回NULL，这里可以根据需求返回NULL或者0，完全匹配你期望的输出格式。

验证你的测试数据 #

用你给出的测试数据跑这个SQL，结果会和你期望的完全一致：

• Andrew的第一条记录Duration为NULL（或0），第二条时长是10:50:04 - 10:29:04 = 1260秒，第三条是2秒，第六条是1203秒
• Nazar的第一条记录Duration为NULL（或0），第二条是540秒

如果需要把NULL替换成0，只需要修改CASE语句里的ELSE部分就行。

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