Oracle SQL计算相邻行时间戳差值 解决LAG()返回null问题

使用LAG()返回null的核心原因是窗口函数选型错误：

• LAG()用于提取排序窗口内当前行之前的指定行数据，仅适合计算当前行与上一条记录的差值
• 需求是计算当前行与下一条相邻记录的时间间隔，需要使用LEAD()函数提取当前行之后的相邻行时间戳。

以下写法兼容所有支持标准窗口函数的数据库引擎（MySQL 8.0+、PostgreSQL、Hive、SparkSQL、SQL Server等），不同引擎的时间格式化部分已做注释标注：

SELECT
    TIMESTAMP,
    P_ID,
    Message,
    CASE
        WHEN next_ts IS NULL THEN '00:00:00'
        -- MySQL 用法：秒级差值转时分秒格式
        ELSE SEC_TO_TIME(TIMESTAMPDIFF(SECOND, TIMESTAMP, next_ts))
        -- PostgreSQL 请替换为：to_char((next_ts - TIMESTAMP), 'HH24:MI:SS')
        -- SQL Server 请替换为：CONVERT(varchar, DATEADD(second, DATEDIFF(second, TIMESTAMP, next_ts), 0), 108)
    END AS adjacent_time_gap
FROM (
    SELECT
        TIMESTAMP,
        P_ID,
        Message,
        LEAD(TIMESTAMP) OVER (
            PARTITION BY P_ID
            ORDER BY TIMESTAMP ASC
        ) AS next_ts
    FROM biz_table
    -- 仅查询bim_123数据时放开下方注释即可
    -- WHERE P_ID = 'bim_123'
) t
ORDER BY P_ID, TIMESTAMP ASC;

• 窗口定义中PARTITION BY P_ID不可省略，否则会跨不同P_ID匹配相邻记录，计算结果完全错误
• 同P_ID分组内必须按TIMESTAMP升序排序，才能保证取到的下一行值是时间维度紧邻的后续消息
• 最后一条记录无后续消息，LEAD()返回null，通过case分支直接返回要求的00:00:00格式值即可

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