CASE表达式结合GROUP BY按时长分组聚合结果不符合预期问题咨询

问题原因 #

• 分组字段不匹配：SELECT子句中是将[Time]转换为仅包含日期的[Transaction Date]输出，但GROUP BY子句中仍然使用原始带时分秒精度的[Time]字段，这会导致同一日期下不同时间的记录被拆分到不同分组，无法实现按天聚合的效果。
• 表名格式错误：表名my Table包含空格，需要用方括号包裹否则会触发语法报错。

修正后的代码 #

SELECT
    [Area],
    CONVERT(VARCHAR(10),[Time],111) as [Transaction Date],
    [Code],
    CASE        
        WHEN [Duration] >= 7200 THEN '5 Days or More'
        WHEN [Duration] >= 5760 AND [Duration] < 7200 THEN '4 Days'
        WHEN [Duration] >= 4320 AND [Duration] < 5760 THEN '3 Days'
        WHEN [Duration] >= 2880 AND [Duration] < 4320 THEN '2 Days'
        WHEN [Duration] > 1380 AND [Duration] < 2880 THEN '1 Days'
        ELSE '0 Days' 
    END AS [Duration Group],
    SUM ([#ofSales]) AS [Volume]
FROM 
    [my Table]
WHERE 
    [ID] NOT LIKE '8787%' 
    AND [#ofSales] >= 1
GROUP BY 
    CONVERT(VARCHAR(10),[Time],111), 
    [Area], 
    [Code],
    CASE        
        WHEN [Duration] >= 7200 THEN '5 Days or More'
        WHEN [Duration] >= 5760 AND [Duration] < 7200 THEN '4 Days'
        WHEN [Duration] >= 4320 AND [Duration] < 5760 THEN '3 Days'
        WHEN [Duration] >= 2880 AND [Duration] < 4320 THEN '2 Days'
        WHEN [Duration] > 1380 AND [Duration] < 2880 THEN '1 Days'
        ELSE '0 Days' 
    END
ORDER BY 
    [Transaction Date], [Area], [Duration Group]

优化说明 #

如果使用的是支持CTE/子查询的数据库，可以将CASE表达式提前写在子查询中，避免重复编写两次CASE逻辑，降低后续维护的修改成本。

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