SQL跨行DATEDIFF计算异常及字段添加技术求助

核心问题分析 #

从你的描述和查询语句来看，结果不符合预期主要有几个关键原因：

1. 缺少分区（PARTITION BY）：你需要按ID分区，确保只计算同一个ID下前后行的时间差，否则会把不同ID的记录混在一起计算，导致结果完全偏离预期。
2. DATEDIFF参数顺序错误：DATEDIFF(单位, 开始时间, 结束时间)的逻辑是计算从开始到结束的差值，要得到前一行（更早时间）到当前行（更晚时间）的正差值，应该把LAG获取的前一行时间作为开始时间，当前行时间作为结束时间。
3. 时间单位使用错误：你用MM计算分钟差，但MM是月份的缩写，分钟应该用mi或n。
4. 需求理解偏差：你预期的是拆分后的天、小时、分钟、秒（比如6天1小时16分21秒），但直接用DATEDIFF(dd, ...)得到的是总天数，DATEDIFF(hh, ...)是总小时数，需要额外处理才能拆分出剩余的时分秒。

关于排序的疑问 #

是的，建议让OVER子句中的ORDER BY和最终查询的ORDER BY保持逻辑一致：

• OVER(PARTITION BY c.ID ORDER BY h.ROUTED_DTM DESC)：按ID分区，每个分区内按ROUTED_DTM从新到旧排序，这样LAG会精准取到同一个ID下的上一条（更早的）记录。
• 最终查询的ORDER BY c.ID, h.ROUTED_DTM DESC：确保结果展示时，同一个ID的记录按时间从新到旧排列，和LAG的计算逻辑对应，避免前后行顺序混乱。

修正后的查询语句 #

下面是调整后的查询，包含你需要的秒级差值字段，以及正确拆分天/小时/分钟/秒的计算：

SELECT 
    c.ID,
    c.PAID_DT,
    DATEDIFF(dd, 
        CASE WHEN c.ID_ADJ_FROM = '' THEN c.RECD_DT ELSE c.INPUT_DT END, 
        CASE WHEN c.PAID_DT = '1/1/1753' THEN CONVERT(DATE,GETDATE()) ELSE c.PAID_DT END
    ) + 1 AS DAYS_OLD,
    -- 获取前一行的ROUTED_DTM（同一个ID内，按时间倒序）
    LAG(h.ROUTED_DTM) OVER (PARTITION BY c.ID ORDER BY h.ROUTED_DTM DESC) AS PREV_ROUTED_DTM,
    -- 秒级差值（总秒数），放在QUEUE_ID前
    DATEDIFF(ss, LAG(h.ROUTED_DTM) OVER (PARTITION BY c.ID ORDER BY h.ROUTED_DTM DESC), h.ROUTED_DTM) AS ROUTED_SEC_DIFF,
    -- 拆分天/小时/分钟/秒
    DATEDIFF(dd, LAG(h.ROUTED_DTM) OVER (PARTITION BY c.ID ORDER BY h.ROUTED_DTM DESC), h.ROUTED_DTM) AS AGE_IN_ROLE_DAY,
    DATEDIFF(hh, DATEADD(dd, DATEDIFF(dd, LAG(h.ROUTED_DTM) OVER (PARTITION BY c.ID ORDER BY h.ROUTED_DTM DESC), h.ROUTED_DTM), LAG(h.ROUTED_DTM) OVER (PARTITION BY c.ID ORDER BY h.ROUTED_DTM DESC)), h.ROUTED_DTM) AS AGE_IN_ROLE_HR,
    DATEDIFF(mi, DATEADD(hh, DATEDIFF(hh, LAG(h.ROUTED_DTM) OVER (PARTITION BY c.ID ORDER BY h.ROUTED_DTM DESC), h.ROUTED_DTM), LAG(h.ROUTED_DTM) OVER (PARTITION BY c.ID ORDER BY h.ROUTED_DTM DESC)), h.ROUTED_DTM) % 60 AS AGE_IN_ROLE_MIN,
    DATEDIFF(ss, DATEADD(mi, DATEDIFF(mi, LAG(h.ROUTED_DTM) OVER (PARTITION BY c.ID ORDER BY h.ROUTED_DTM DESC), h.ROUTED_DTM), LAG(h.ROUTED_DTM) OVER (PARTITION BY c.ID ORDER BY h.ROUTED_DTM DESC)), h.ROUTED_DTM) % 60 AS AGE_IN_ROLE_SEC,
    h.QUEUE_ID,
    h.QUEUE_DESC,
    h.ROLE_ID,
    h.ROLE_DESC,
    h.ROUTED_DTM
FROM table1 c
LEFT JOIN table2 h ON h.ID = c.ID
LEFT JOIN table3 q ON q.QUEUE_ID = h.QUEUE_ID
LEFT JOIN table4 r ON r.ROLE_ID = h.ROLE_ID
ORDER BY c.ID, h.ROUTED_DTM DESC;

简化代码的小技巧 #

为了避免重复写LAG(...)，可以用CROSS APPLY提前计算前一行的时间，让代码更简洁易维护：

SELECT 
    c.ID,
    c.PAID_DT,
    DATEDIFF(dd, 
        CASE WHEN c.ID_ADJ_FROM = '' THEN c.RECD_DT ELSE c.INPUT_DT END, 
        CASE WHEN c.PAID_DT = '1/1/1753' THEN CONVERT(DATE,GETDATE()) ELSE c.PAID_DT END
    ) + 1 AS DAYS_OLD,
    prev.PREV_ROUTED_DTM,
    -- 秒级差值，放在QUEUE_ID前
    DATEDIFF(ss, prev.PREV_ROUTED_DTM, h.ROUTED_DTM) AS ROUTED_SEC_DIFF,
    -- 拆分天/小时/分钟/秒
    DATEDIFF(dd, prev.PREV_ROUTED_DTM, h.ROUTED_DTM) AS AGE_IN_ROLE_DAY,
    DATEDIFF(hh, DATEADD(dd, DATEDIFF(dd, prev.PREV_ROUTED_DTM, h.ROUTED_DTM), prev.PREV_ROUTED_DTM), h.ROUTED_DTM) AS AGE_IN_ROLE_HR,
    DATEDIFF(mi, DATEADD(hh, DATEDIFF(hh, prev.PREV_ROUTED_DTM, h.ROUTED_DTM), prev.PREV_ROUTED_DTM), h.ROUTED_DTM) % 60 AS AGE_IN_ROLE_MIN,
    DATEDIFF(ss, DATEADD(mi, DATEDIFF(mi, prev.PREV_ROUTED_DTM, h.ROUTED_DTM), prev.PREV_ROUTED_DTM), h.ROUTED_DTM) % 60 AS AGE_IN_ROLE_SEC,
    h.QUEUE_ID,
    h.QUEUE_DESC,
    h.ROLE_ID,
    h.ROLE_DESC,
    h.ROUTED_DTM
FROM table1 c
LEFT JOIN table2 h ON h.ID = c.ID
LEFT JOIN table3 q ON q.QUEUE_ID = h.QUEUE_ID
LEFT JOIN table4 r ON r.ROLE_ID = h.ROLE_ID
-- 提前计算前一行的时间，避免重复代码
CROSS APPLY (
    SELECT LAG(h.ROUTED_DTM) OVER (PARTITION BY c.ID ORDER BY h.ROUTED_DTM DESC) AS PREV_ROUTED_DTM
) prev
ORDER BY c.ID, h.ROUTED_DTM DESC;

验证你的示例数据 #

对于你给出的两条记录：

• 当前行：ROUTED_DTM = '2019-05-07 15:36:13.000'
• 前一行：PREV_ROUTED_DTM = '2019-05-01 14:19:52.000'

计算结果完全匹配你的预期：

• AGE_IN_ROLE_DAY = 6
• AGE_IN_ROLE_HR = 1
• AGE_IN_ROLE_MIN = 16
• AGE_IN_ROLE_SEC = 21

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