如何补全lds_user_log未登出记录并统计CSR周度登录时长

嘿，我来帮你搞定这个问题！针对你提到的部分CSR直接关闭浏览器不登出，导致登出事件缺失的情况，我们可以通过生成补全的登出记录来优化统计逻辑，同时完美适配你需要的周度报表格式。

思路拆解 #

核心目标是：给每个用户当天有登录但无登出的情况，自动生成一条当日17:00的登出记录，这样就能准确计算每日登录时长，避免跨天统计的误差。

完整实现脚本 #

第一步：补全缺失的登出记录并计算每日时长

-- 清理临时表（如果存在）
IF OBJECT_ID('tempdb..#tmpDailyLogins') IS NOT NULL DROP TABLE #tmpDailyLogins;

WITH UserDailyEvents AS (
    -- 筛选周度数据，标记用户当日是否有登出事件
    SELECT 
        [user],
        user_group,
        CONVERT(DATE, event_date) AS event_day,
        event,
        event_date,
        -- 1=当日有登出，0=无登出
        MAX(CASE WHEN event = 'LOGOUT' THEN 1 ELSE 0 END) OVER (PARTITION BY [user], CONVERT(DATE, event_date)) AS has_logout
    FROM [LEADS].[dbo].[lds_User_log]
    -- 周度报表：取@startdate开始的一周数据，可根据需求调整
    WHERE event_date >= @startdate AND event_date < DATEADD(DAY, 7, @startdate)
),
MissingLogouts AS (
    -- 为当日无登出的用户生成17:00的登出记录
    SELECT DISTINCT
        [user],
        user_group,
        event_day,
        'LOGOUT' AS event,
        -- 拼接当日日期+17:00作为登出时间
        DATEADD(MINUTE, 0, CONVERT(DATETIME, event_day) + CAST('17:00:00' AS TIME)) AS event_date
    FROM UserDailyEvents
    WHERE has_logout = 0
),
FullEvents AS (
    -- 合并原始事件和补全的登出记录
    SELECT [user], user_group, event, event_date FROM UserDailyEvents
    UNION ALL
    SELECT [user], user_group, event, event_date FROM MissingLogouts
)
-- 计算每个用户每日的首次登录、末次登出及时长
SELECT 
    [user],
    user_group,
    event_day,
    -- 当日首次登录时间
    MIN(CASE WHEN event = 'LOGIN' THEN event_date END) AS first_login,
    -- 当日末次登出时间（含补全的17:00）
    MAX(CASE WHEN event = 'LOGOUT' THEN event_date END) AS last_logout,
    -- 当日登录总时长（分钟）
    DATEDIFF(MINUTE, 
        MIN(CASE WHEN event = 'LOGIN' THEN event_date END), 
        MAX(CASE WHEN event = 'LOGOUT' THEN event_date END)
    ) AS daily_minutes
INTO #tmpDailyLogins
FROM FullEvents
GROUP BY [user], user_group, event_day;

第二步：生成周度报表格式

SELECT 
    [user],
    user_group,
    -- 周一的登录信息：首次登录 | 时长
    ISNULL(CONVERT(VARCHAR(19), MAX(CASE WHEN DATENAME(WEEKDAY, event_day) = 'Monday' THEN first_login END), 120) + ' | ' + 
           CAST(MAX(CASE WHEN DATENAME(WEEKDAY, event_day) = 'Monday' THEN daily_minutes END) AS VARCHAR), '无登录') AS [MON - 1st LOGIN | LAST LOGOUT],
    -- 周二
    ISNULL(CONVERT(VARCHAR(19), MAX(CASE WHEN DATENAME(WEEKDAY, event_day) = 'Tuesday' THEN first_login END), 120) + ' | ' + 
           CAST(MAX(CASE WHEN DATENAME(WEEKDAY, event_day) = 'Tuesday' THEN daily_minutes END) AS VARCHAR), '无登录') AS [TUE - 1st LOGIN | LAST LOGOUT],
    -- 周三
    ISNULL(CONVERT(VARCHAR(19), MAX(CASE WHEN DATENAME(WEEKDAY, event_day) = 'Wednesday' THEN first_login END), 120) + ' | ' + 
           CAST(MAX(CASE WHEN DATENAME(WEEKDAY, event_day) = 'Wednesday' THEN daily_minutes END) AS VARCHAR), '无登录') AS [WED - 1st LOGIN | LAST LOGOUT],
    -- 周四
    ISNULL(CONVERT(VARCHAR(19), MAX(CASE WHEN DATENAME(WEEKDAY, event_day) = 'Thursday' THEN first_login END), 120) + ' | ' + 
           CAST(MAX(CASE WHEN DATENAME(WEEKDAY, event_day) = 'Thursday' THEN daily_minutes END) AS VARCHAR), '无登录') AS [THU - 1st LOGIN | LAST LOGOUT],
    -- 周五
    ISNULL(CONVERT(VARCHAR(19), MAX(CASE WHEN DATENAME(WEEKDAY, event_day) = 'Friday' THEN first_login END), 120) + ' | ' + 
           CAST(MAX(CASE WHEN DATENAME(WEEKDAY, event_day) = 'Friday' THEN daily_minutes END) AS VARCHAR), '无登录') AS [FRI - 1st LOGIN | LAST LOGOUT]
FROM #tmpDailyLogins
GROUP BY [user], user_group;

关键说明 #

1. 日期语言适配：如果你的SQL Server是中文环境，DATENAME(WEEKDAY, event_day)返回的是“星期一”“星期二”，需要把CASE里的'Monday'改成对应的中文。
2. 周末处理：如果需要统计周末，只需在报表部分添加周六、周日的CASE分支即可。
3. 空值优化：用ISNULL把无登录的日期显示为“无登录”，避免出现NULL。
4. 跨天问题解决：每个用户每天都有明确的登出时间（实际登出或17:00），不会把次日登录的时长误算到前一天。

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