MSSQL关联表查询活跃监视器多时段计数统计的SQL实现

实现SQL语句 #

SELECT
    m.name AS monitor_name,
    COALESCE(SUM(l.count), 0) AS alltime,
    COALESCE(SUM(CASE WHEN CAST(l.time_stamp AS DATE) = CAST(GETUTCDATE() AS DATE) THEN l.count ELSE 0 END), 0) AS today,
    COALESCE(SUM(CASE 
        WHEN CAST(l.time_stamp AS DATE) = CAST(GETUTCDATE() AS DATE) 
        AND DATEPART(HOUR, l.time_stamp) = DATEPART(HOUR, GETUTCDATE()) 
        THEN l.count ELSE 0 END), 0) AS current_hour
FROM Monitors m
LEFT JOIN Logger l ON m.monitor_id = l.monitor
WHERE m.is_active = 1
GROUP BY m.monitor_id, m.name

逻辑说明 #

• 采用LEFT JOIN关联两张表，确保没有上报过日志的活跃监视器也能正常出现在结果集中，无上报记录时对应计数返回0
• 修复了原当日统计逻辑的漏洞：原逻辑仅比对日维度数值，会把其他月份同天的日志误算入当日统计，改为直接比对日期部分结果更准确
• 用条件聚合逻辑一次性统计三个维度的计数，不需要多次子查询关联，执行效率更高
• 按monitor_id和name分组，保证每个活跃监视器对应唯一一行结果

可选调整 #

如果业务统计需要使用本地时间而非UTC时间，把语句中所有GETUTCDATE()替换为GETDATE()即可。

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