SQL中GROUP BY查询如何自定义天数分桶并按类别聚合数据

你需要先对Days字段做分桶映射，再按映射后的分桶值和Category分组求和即可，以下是兼容所有主流SQL数据库的通用写法：

SELECT
  CASE 
    WHEN Days BETWEEN 0 AND 4 THEN '0-4'
    WHEN Days BETWEEN 5 AND 9 THEN '5-9'
  END AS Days,
  Category,
  SUM(Values) AS Values
FROM
  你的实际表名
GROUP BY
  CASE 
    WHEN Days BETWEEN 0 AND 4 THEN '0-4'
    WHEN Days BETWEEN 5 AND 9 THEN '5-9'
  END,
  Category
ORDER BY
  Days, Category

如果你的数据库支持GROUP BY引用SELECT子句定义的别名（如MySQL、PostgreSQL 12及以上版本），可以简化为：

SELECT
  CASE 
    WHEN Days >= 0 AND Days <=4 THEN '0-4'
    ELSE '5-9'
  END AS Days,
  Category,
  SUM(Values) AS Values
FROM
  你的实际表名
GROUP BY
  Days, Category
ORDER BY
  Days, Category

如果存在Days小于0或者大于9的异常值，你可以在CASE WHEN中新增ELSE分支自定义异常值的分桶标签，避免统计遗漏。

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