SQL实现：仅计算Entry到Exit的时间差，Entry行显示0

原始表结构及数据如下：

DateCreated         Changed_By_User_ID  Change_Info     ResourceID  (No column name)
2022-08-24 20:01:44.937     1876          Entry           400377    NULL
2022-08-24 20:02:40.013     1876          Exits           400377    1
2022-09-23 14:19:52.140     1876          Entry           400377    42857
2022-09-23 14:20:16.807     1876          Exits           400377    1
2022-09-23 14:34:48.617     1876          Entry           400377    14
2022-09-23 14:35:24.210     1876          Exits           400377    1

需求是：基于DateCreated和Change_Info列，计算每个用户查看每条资源记录的时长，要求所有Entry行对应值显示0，仅在Exit行计算对应的Entry到Exit的时间差。

之前尝试的代码会计算Exit到下一个Entry的时间差，不符合需求：

datediff(minute,lag(l.DateCreated,1) over (partition by ResourceID, changed_by_user_id order by l.[DateCreated] asc),l.[DateCreated])

可以通过CASE WHEN结合窗口函数实现需求，仅在当前行是Exits时计算与上一行（对应Entry）的时间差，Entry行直接返回0：

SELECT 
    DateCreated,
    Changed_By_User_ID,
    Change_Info,
    ResourceID,
    CASE 
        WHEN Change_Info = 'Exits' THEN DATEDIFF(minute, LAG(DateCreated) OVER (PARTITION BY ResourceID, Changed_By_User_ID ORDER BY DateCreated), DateCreated)
        ELSE 0
    END AS View_Duration_Minutes
FROM 
    YourTableName;

逻辑说明 #

1. PARTITION BY ResourceID, Changed_By_User_ID：确保只在同一资源、同一用户的范围内计算时间差
2. ORDER BY DateCreated：保证按时间顺序取上一行的Entry时间
3. CASE WHEN判断：仅当当前行是Exits时，计算与上一行（对应Entry）的分钟差；Entry行直接返回0

如果存在非成对的Entry/Exit（比如连续Entry或未闭合的Exit），可以进一步优化逻辑，只取当前行之前最近的Entry时间：

SELECT 
    DateCreated,
    Changed_By_User_ID,
    Change_Info,
    ResourceID,
    CASE 
        WHEN Change_Info = 'Exits' THEN DATEDIFF(minute, 
            MAX(CASE WHEN Change_Info = 'Entry' THEN DateCreated END) OVER (PARTITION BY ResourceID, Changed_By_User_ID ORDER BY DateCreated ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING), 
            DateCreated)
        ELSE 0
    END AS View_Duration_Minutes
FROM 
    YourTableName;

这个版本会在Exit行精准匹配最近的Entry时间，避免连续Entry导致的错误计算。

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