SQL实现每个客户相邻会话结束到新会话开始的时间差计算

原查询问题点 #

• 语法错误：LAG(end_time) 字段定义后缺少逗号，会直接执行报错
• 未处理空值：用户首条会话调用LAG取上一行结束时间时返回空，没有按需求替换为0
• 格式不匹配：原逻辑计算的是分钟差值，和期望的时分秒间隔格式不符

修正后的查询方案 #

方案1（适配支持SEC_TO_TIME的引擎，如Hive/Spark SQL/MySQL）

SELECT  
    user_id,
    conversation_id,
    start_time,
    end_time,
    CASE
        WHEN LAG(unix_timestamp(end_time)) OVER (PARTITION BY user_id ORDER BY start_time) IS NULL
        THEN '0'
        ELSE SEC_TO_TIME(unix_timestamp(start_time) - LAG(unix_timestamp(end_time)) OVER (PARTITION BY user_id ORDER BY start_time))
    END AS time_between_end_newstart
FROM mt_data
ORDER BY user_id, start_time

方案2（通用兼容版，无SEC_TO_TIME也可运行）

SELECT  
    user_id,
    conversation_id,
    start_time,
    end_time,
    CASE
        WHEN diff_sec IS NULL THEN '0'
        ELSE CONCAT(
            FLOOR(diff_sec / 3600), ':',
            LPAD(FLOOR((diff_sec % 3600) / 60), 2, '0'), ':',
            LPAD(diff_sec % 60, 2, '0')
        )
    END AS time_between_end_newstart
FROM (
    SELECT
        user_id,
        conversation_id,
        start_time,
        end_time,
        unix_timestamp(start_time) - LAG(unix_timestamp(end_time)) OVER (PARTITION BY user_id ORDER BY start_time) AS diff_sec
    FROM mt_data
) t
ORDER BY user_id, start_time

以上查询针对你的样本数据可直接输出期望结果，同用户会话会按start_time排序计算间隔，首条/仅一条会话自动返回0，间隔自动转为时分秒格式。

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