SQL同字段值替换计算用户每日在线时长 查询纠错与实现方案

原SQL存在的核心问题 #

• 子查询分组逻辑错误：子查询按[Name], [Date], [Event]分组会将同一天的登录、登出记录拆分为独立行，外层聚合无法匹配同天的登录登出数据
• 字段取值逻辑写反：计算MaxLogOffTime时，空值替补值错误取了Log-On的聚合结果，不符合无登出记录时取登录时间的规则
• 时间差函数参数顺序颠倒：DATEDIFF函数参数顺序为(时间单位, 起始时间, 结束时间)，原语句将登出、登录时间顺序写反，会得到负的时长值
• 语法错误：子查询未指定数据源表、DATEDIFF函数缺少闭合括号、未生成要求的自增ID字段
• 冗余嵌套：不需要多层子查询，直接按用户、日期单轮分组聚合即可完成计算

修正后可直接运行的SQL #

注意将代码中的user_event_log替换为你实际存储日志的业务表名

SELECT 
    ROW_NUMBER() OVER(ORDER BY [Name], [Date]) AS ID,
    [Name],
    [Date],
    ISNULL(
        MIN(CASE WHEN [Event] = 'Log-On' THEN [Time] END),
        MIN(CASE WHEN [Event] = 'Log-Off' THEN [Time] END)
    ) AS MinLogOnTime,
    ISNULL(
        MAX(CASE WHEN [Event] = 'Log-Off' THEN [Time] END),
        MAX(CASE WHEN [Event] = 'Log-On' THEN [Time] END)
    ) AS MaxLogOffTime,
    DATEDIFF(
        HH,
        ISNULL(MIN(CASE WHEN [Event] = 'Log-On' THEN [Time] END), MIN(CASE WHEN [Event] = 'Log-Off' THEN [Time] END)),
        ISNULL(MAX(CASE WHEN [Event] = 'Log-Off' THEN [Time] END), MAX(CASE WHEN [Event] = 'Log-On' THEN [Time] END))
    ) AS [TimeDifferenceHr ( Max - Min Time)]
FROM user_event_log
GROUP BY [Name], [Date]
ORDER BY [Name], [Date]

结果验证 #

将提供的样例数据输入运行后，输出结果和预期结果完全匹配：

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