按分组重置计算两行间的时间差（SQL实现求助）

核心思路是利用窗口函数LAG()实现分组内的前序时间获取，结合条件处理首条记录的时间差为0：

• 按ENCOUNTER_ID分区，确保每个合作伙伴的计算独立重置
• 按评估时间字段排序，保证时间差计算的顺序正确性
• 用COALESCE（或对应数据库的空值处理函数）将首条记录的空值转换为0，符合规则要求

假设你的评估表名为assessment_records，评估时间字段为assessment_time，以下是修正后的通用SQL代码：

SELECT
    ENCOUNTER_ID,
    assessment_time,
    -- 以秒为单位返回时间差，可根据需求调整单位（如DAY、HOUR）
    COALESCE(
        EXTRACT(EPOCH FROM (assessment_time - LAG(assessment_time) OVER (PARTITION BY ENCOUNTER_ID ORDER BY assessment_time))),
        0
    ) AS "Time Difference"
FROM
    assessment_records
ORDER BY
    ENCOUNTER_ID,
    assessment_time;

代码说明： #

• PARTITION BY ENCOUNTER_ID：严格按合作伙伴分组，每组的时间计算独立重置
• ORDER BY assessment_time：确保每组内的记录按评估时间先后排序，LAG()能准确获取前一条记录的时间
• EXTRACT(EPOCH FROM ...)：将时间间隔转换为秒数，若需要其他单位（比如天数），可直接写assessment_time - LAG(assessment_time) OVER (...)
• COALESCE(..., 0)：处理首条记录无前置时间的情况，将空值替换为0，符合规则

如果使用MySQL，时间差计算需适配语法，替换为TIMESTAMPDIFF和IFNULL：

SELECT
    ENCOUNTER_ID,
    assessment_time,
    IFNULL(
        TIMESTAMPDIFF(SECOND, LAG(assessment_time) OVER (PARTITION BY ENCOUNTER_ID ORDER BY assessment_time), assessment_time),
        0
    ) AS `Time Difference`
FROM
    assessment_records
ORDER BY
    ENCOUNTER_ID,
    assessment_time;

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