如何编写SQL计算Event A与后续非A事件的时间差

通用方案（支持窗口函数的数据库：MySQL 8+、PostgreSQL、BigQuery、SQL Server等） #

利用LEAD()窗口函数可以高效获取每个A事件之后的第一个非A事件时间，代码如下：

SELECT 
    id,
    next_non_a_time - time AS duration
FROM (
    SELECT 
        id,
        event,
        time,
        -- 按用户分组、时间排序，捕获后续第一个非A事件的时间
        LEAD(CASE WHEN event != 'A' THEN time END) OVER (
            PARTITION BY id 
            ORDER BY time
        ) AS next_non_a_time
    FROM your_table_name -- 替换为你的表名
) filtered
WHERE 
    event = 'A'
    AND next_non_a_time IS NOT NULL; -- 排除无后续非A事件的A

逻辑说明：

• 内层子查询通过LEAD()窗口函数，仅当后续事件为非A时才返回其时间，按用户分组、时间升序遍历事件
• 外层仅保留事件为A的行，且确保存在后续非A事件，最后计算时间差

兼容旧版数据库方案（无窗口函数：MySQL 5.x等） #

如果数据库不支持窗口函数，可使用关联子查询实现：

SELECT 
    t1.id,
    t2.time - t1.time AS duration
FROM your_table_name t1
JOIN your_table_name t2 
    ON t1.id = t2.id
    AND t2.time > t1.time
    AND t2.event != 'A'
WHERE 
    t1.event = 'A'
    -- 确保t2是t1之后的第一个非A事件（中间无其他事件）
    AND NOT EXISTS (
        SELECT 1 
        FROM your_table_name t3
        WHERE t3.id = t1.id
        AND t3.time > t1.time
        AND t3.time < t2.time
    )
ORDER BY t1.id, t1.time;

逻辑说明：

• 通过JOIN找到当前A事件之后的所有非A事件
• 用NOT EXISTS过滤掉中间存在其他事件的情况，确保只保留第一个非A事件
• 计算两个事件的时间差

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