使用SQL查询计算同一号码Group Joined与Group Left的时长差（超12小时）

我有一张包含Number、Status、Time字段的表，其中Time字段为合法datetime格式。需要通过SQL查询计算同一Number对应的「Group Joined」与「Group Left」的时间差，且仅筛选出时长超过12小时的号码。尝试过使用DATEDIFF函数，但未得到预期结果。

示例表数据 #

核心思路 #

每个Number的「Group Joined」需要匹配对应的后续「Group Left」，避免跨次匹配（比如2132的第二次Joined不能和第一次Left关联）。下面提供两种通用方案，适配不同数据库：

方案1：自连接匹配对应状态记录 #

通过自连接，将同一Number的「Group Joined」记录和之后的第一条「Group Left」记录关联：

SELECT
    j.Number,
    j.Time AS join_time,
    l.Time AS left_time,
    -- 根据数据库类型选择写法，以下是常见示例
    -- MySQL：计算小时差
    TIMESTAMPDIFF(HOUR, j.Time, l.Time) AS duration_hours
    -- SQL Server：
    -- DATEDIFF(HOUR, j.Time, l.Time) AS duration_hours
    -- PostgreSQL：
    -- EXTRACT(EPOCH FROM (l.Time - j.Time))/3600 AS duration_hours
FROM
    your_table j
JOIN
    your_table l ON j.Number = l.Number
                AND l.Status = 'Group Left'
                AND l.Time > j.Time
                -- 确保是Joined之后的第一条Left
                AND NOT EXISTS (
                    SELECT 1
                    FROM your_table m
                    WHERE m.Number = j.Number
                      AND m.Status = 'Group Left'
                      AND m.Time > j.Time
                      AND m.Time < l.Time
                )
WHERE
    j.Status = 'Group Joined'
    -- 筛选时长超过12小时的记录
    AND TIMESTAMPDIFF(HOUR, j.Time, l.Time) > 12;

方案2：使用窗口函数LEAD获取后续Left时间 #

用LEAD窗口函数直接获取同一Number下一条记录的时间，仅当该记录是「Group Left」时计算：

SELECT
    Number,
    join_time,
    left_time,
    duration_hours
FROM (
    SELECT
        Number,
        Time AS join_time,
        -- 获取同一Number的下一条记录时间
        LEAD(Time) OVER (PARTITION BY Number ORDER BY Time) AS left_time,
        LEAD(Status) OVER (PARTITION BY Number ORDER BY Time) AS next_status,
        -- 计算小时差
        TIMESTAMPDIFF(HOUR, Time, LEAD(Time) OVER (PARTITION BY Number ORDER BY Time)) AS duration_hours
    FROM your_table
    WHERE Status = 'Group Joined'
) t
WHERE
    next_status = 'Group Left'
    AND duration_hours > 12;

注意事项 #

• DATEDIFF函数在不同数据库的参数顺序和单位定义不同，比如MySQL的DATEDIFF默认返回天数差，要计算小时差需用TIMESTAMPDIFF；SQL Server的DATEDIFF第一个参数是单位，第二个是起始时间，第三个是结束时间，别搞反顺序。
• 如果存在「Group Joined」后没有对应「Group Left」的记录（比如示例中2132的第二次Joined），上述方案会自动排除这类无匹配的数据，避免无效计算。

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