如何通过SQL基于TimeIn/TimeOut记录计算IdleTime（空闲时间）？

我有包含TimeIn（进入时间）和TimeOut（离开时间）的原始数据：

我已经用以下SQL计算出了时长Duration，但不知道如何计算IdleTime（空闲时间）：

SELECT
    TimeIn, TimeOut, 
    DATEDIFF(MINUTE, Timein, Timeout) AS duration
FROM
    [test_idletime]

期望得到的输出结果如下：

解决方案 #

根据示例逻辑，IdleTime是当前记录的TimeIn与上一条记录的TimeOut的分钟差值，若差值为负或当前是第一条记录，则空闲时间为0。可以用窗口函数LAG()结合DATEDIFF()实现：

通用SQL版本 #

SELECT
    TimeIn,
    TimeOut,
    CONCAT(DATEDIFF(MINUTE, TimeIn, TimeOut), 'min') AS Duration,
    CONCAT(GREATEST(0, DATEDIFF(MINUTE, LAG(TimeOut) OVER (ORDER BY TimeIn), TimeIn)), 'min') AS IdleTime
FROM
    [test_idletime]
ORDER BY TimeIn;

逻辑说明 #

1. LAG(TimeOut) OVER (ORDER BY TimeIn)：按TimeIn排序，获取当前记录的上一条记录的TimeOut，第一条记录该值为NULL。
2. DATEDIFF(MINUTE, ..., TimeIn)：计算上一条TimeOut到当前TimeIn的分钟差，无前置记录时差值为NULL。
3. GREATEST(0, ...)：将负差值或NULL转为0，保证空闲时间非负。
4. CONCAT(..., 'min')：为时长和空闲时间添加单位，匹配示例格式。

兼容旧版SQL Server的版本（无GREATEST()支持） #

SELECT
    TimeIn,
    TimeOut,
    CONCAT(DATEDIFF(MINUTE, TimeIn, TimeOut), 'min') AS Duration,
    CONCAT(
        CASE
            WHEN LAG(TimeOut) OVER (ORDER BY TimeIn) IS NULL THEN 0
            WHEN DATEDIFF(MINUTE, LAG(TimeOut) OVER (ORDER BY TimeIn), TimeIn) < 0 THEN 0
            ELSE DATEDIFF(MINUTE, LAG(TimeOut) OVER (ORDER BY TimeIn), TimeIn)
        END, 'min'
    ) AS IdleTime
FROM
    [test_idletime]
ORDER BY TimeIn;

内容的提问来源于stack exchange，提问作者Emon Hossain Diza