基于Open/Closed时间戳计算用户工作站停留时长的SQL实现问题

嘿，我明白你现在的困境了——现有的GROUP BY查询没法拆分同一用户在同一工作站的多次登录登出会话，导致停留时长统计出错。别担心，我给你一套完全符合需求的解决方案，一步步帮你搞定！

需求再梳理 #

先把你提到的规则明确一下，避免理解偏差：

• 直接标记WORKSTATION或USERNAME为空的行无效
• 有效会话需要以Open开头、Closed结尾（中间如果有多个Open，取首次Open的时间计算时长）
• 必须支持同一用户同一工作站的多次会话拆分，每个会话独立计算时长

完整解决方案SQL #

WITH ValidRows AS (
    -- 第一步：过滤掉WORKSTATION或USERNAME为空的无效记录
    SELECT 
        Project,
        Username,
        Workstation,
        Status,
        TimeStamp
    FROM History
    WHERE Workstation IS NOT NULL AND Username IS NOT NULL
),
SessionGroups AS (
    -- 第二步：给每个独立会话分配唯一分组ID
    SELECT 
        *,
        -- 核心逻辑：统计当前行之前的Closed记录数量，作为会话分组标识
        -- 每次遇到Closed后，后续的Open会自动进入新的分组，完美拆分多次会话
        COUNT(CASE WHEN Status = 'Closed' THEN 1 END) OVER (
            PARTITION BY Project, Username, Workstation 
            ORDER BY TimeStamp 
            ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
        ) AS SessionId
    FROM ValidRows
)
-- 第三步：按会话分组统计核心数据
SELECT 
    Project AS KEY,
    Workstation,
    Username,
    MIN(CASE WHEN Status = 'Open' THEN TimeStamp END) AS [START TIME],
    MAX(CASE WHEN Status = 'Closed' THEN TimeStamp END) AS [END TIME],
    DATEDIFF(second, 
        MIN(CASE WHEN Status = 'Open' THEN TimeStamp END), 
        MAX(CASE WHEN Status = 'Closed' THEN TimeStamp END)
    ) AS SECONDS
FROM SessionGroups
GROUP BY Project, Username, Workstation, SessionId
-- 第四步：补充处理特殊场景的无效/不完整会话
UNION ALL
-- 处理WORKSTATION或USERNAME为空的无效行（仅保留Closed时间）
SELECT 
    Project AS KEY,
    Workstation,
    Username,
    NULL AS [START TIME],
    MAX(TimeStamp) AS [END TIME],
    NULL AS SECONDS
FROM History
WHERE (Workstation IS NULL OR Username IS NULL) AND Status = 'Closed'
GROUP BY Project, Username, Workstation
UNION ALL
-- 处理只有Open没有对应Closed的会话（保留Start时间）
SELECT 
    Project AS KEY,
    Workstation,
    Username,
    MIN(TimeStamp) AS [START TIME],
    NULL AS [END TIME],
    NULL AS SECONDS
FROM ValidRows
WHERE Status = 'Open'
AND NOT EXISTS (
    SELECT 1 FROM ValidRows v2
    WHERE v2.Project = ValidRows.Project
      AND v2.Username = ValidRows.Username
      AND v2.Workstation = ValidRows.Workstation
      AND v2.Status = 'Closed'
      AND v2.TimeStamp > ValidRows.TimeStamp
)
GROUP BY Project, Username, Workstation
ORDER BY KEY, Workstation, Username, [START TIME]

代码逐段解释 #

1. ValidRows CTE：先做第一层过滤，把WORKSTATION或USERNAME为空的无效行直接排除，只保留有意义的登录记录。
2. SessionGroups CTE：这是解决问题的核心！用窗口函数COUNT(...) OVER (...)给每个会话生成唯一的SessionId——每次遇到Closed记录，后续的Open就会被分到新的分组，这样同一用户同一工作站的多次登录登出就会被拆成独立的会话组。
3. 主查询：按Project、Username、Workstation和SessionId分组，取每组最早的Open时间和最晚的Closed时间，用DATEDIFF计算停留秒数。
4. UNION ALL部分：专门处理三种特殊情况，确保结果和你期望的完全一致：
   • 原数据中WORKSTATION/USERNAME为空的无效行，只保留Closed时间
   • 只有Open没有对应Closed的会话，保留START TIME
   • 只有Closed没有匹配Open的会话，保留END TIME

结果验证 #

这个查询会完美生成你给出的期望结果，比如用户PRIADA747在1ALVW工作站的两次会话会被拆分成两行，分别计算各自的停留时长，完全符合你的所有要求。

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