如何在T-SQL中计算登录登出事件的时间间隔及使用时长？

嘿，我来帮你搞定这个计算电脑使用时长的问题！先明确下前提：我假设Event_Type的取值是'Login'（登录）和'Logout'（注销），且默认每个登录事件都对应一个后续的注销事件（如果需要处理未注销的异常情况，我后面也会补充方案）。

前提说明 #

• Event_Type分为'Login'（登录）和'Logout'（注销）两种类型
• 所有计算基于指定日期范围，示例中用Event_DateTime >= '2018-01-01' AND Event_DateTime < '2019-01-01'作为时间过滤条件
• 若存在登录后未注销的情况，可参考文末的异常处理方案

1. 计算单台电脑的总使用时长 #

我们可以用窗口函数LEAD把每台电脑的登录事件和对应的注销事件配对，再计算时间差的总和：

SELECT
    Computer_Id,
    SUM(DATEDIFF(SECOND, Event_DateTime, Next_Logout_Time)) AS Total_Usage_Seconds,
    -- 转换成时分秒格式更直观
    CONVERT(VARCHAR, DATEADD(SECOND, SUM(DATEDIFF(SECOND, Event_DateTime, Next_Logout_Time)), 0), 108) AS Total_Usage_HHMMSS
FROM (
    SELECT
        Computer_Id,
        Event_DateTime,
        -- 按电脑+用户分组，获取当前登录后的下一个事件（即注销时间）
        LEAD(Event_DateTime) OVER (PARTITION BY Computer_Id, User_Id ORDER BY Event_DateTime) AS Next_Logout_Time
    FROM Events
    WHERE
        Event_Type IN ('Login', 'Logout')
        AND Event_DateTime >= '2018-01-01'
        AND Event_DateTime < '2019-01-01'
) AS LoginLogoutPairs
WHERE
    Event_Type = 'Login' -- 只保留登录事件，配对对应的注销时间
GROUP BY Computer_Id
ORDER BY Total_Usage_Seconds DESC;

2. 计算指定用户在某台电脑的使用时长 #

只需在过滤条件里加上目标User_Id和Computer_Id即可：

SELECT
    User_Id,
    Computer_Id,
    SUM(DATEDIFF(SECOND, Event_DateTime, Next_Logout_Time)) AS Total_Usage_Seconds,
    CONVERT(VARCHAR, DATEADD(SECOND, SUM(DATEDIFF(SECOND, Event_DateTime, Next_Logout_Time)), 0), 108) AS Total_Usage_HHMMSS
FROM (
    SELECT
        User_Id,
        Computer_Id,
        Event_DateTime,
        LEAD(Event_DateTime) OVER (PARTITION BY Computer_Id, User_Id ORDER BY Event_DateTime) AS Next_Logout_Time
    FROM Events
    WHERE
        Event_Type IN ('Login', 'Logout')
        AND Event_DateTime >= '2018-01-01'
        AND Event_DateTime < '2019-01-01'
        AND User_Id = 'U123' -- 替换为实际用户ID
        AND Computer_Id = 'C456' -- 替换为实际电脑ID
) AS LoginLogoutPairs
WHERE
    Event_Type = 'Login'
GROUP BY User_Id, Computer_Id;

3. 计算所有电脑的总使用时长 #

去掉分组条件，直接求和所有有效登录注销对的时间差：

SELECT
    SUM(DATEDIFF(SECOND, Event_DateTime, Next_Logout_Time)) AS Total_All_Computers_Seconds,
    CONVERT(VARCHAR, DATEADD(SECOND, SUM(DATEDIFF(SECOND, Event_DateTime, Next_Logout_Time)), 0), 108) AS Total_All_Computers_HHMMSS
FROM (
    SELECT
        Event_DateTime,
        LEAD(Event_DateTime) OVER (PARTITION BY Computer_Id, User_Id ORDER BY Event_DateTime) AS Next_Logout_Time
    FROM Events
    WHERE
        Event_Type IN ('Login', 'Logout')
        AND Event_DateTime >= '2018-01-01'
        AND Event_DateTime < '2019-01-01'
) AS LoginLogoutPairs
WHERE
    Event_Type = 'Login';

可选：处理未注销的异常情况 #

如果存在用户登录后未注销（比如电脑突然断电）的情况，可以用COALESCE把未注销的登录事件的结束时间设为当前时间或指定日期的截止时间：

SELECT
    Computer_Id,
    SUM(DATEDIFF(SECOND, Event_DateTime, COALESCE(Next_Logout_Time, GETDATE()))) AS Total_Usage_Seconds
FROM (
    SELECT
        Computer_Id,
        Event_DateTime,
        LEAD(Event_DateTime) OVER (PARTITION BY Computer_Id, User_Id ORDER BY Event_DateTime) AS Next_Logout_Time
    FROM Events
    WHERE
        Event_Type IN ('Login', 'Logout')
        AND Event_DateTime >= '2018-01-01'
) AS LoginLogoutPairs
WHERE
    Event_Type = 'Login'
GROUP BY Computer_Id;

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