SQL如何按时间与自定义列分组统计正负值数量

你之前的写法存在两个核心错误：

• GROUP BY 仅支持基于原始表字段、或原始字段的非聚合计算表达式分组，不能把聚合生成的统计字段（你写的count_negatives、count_positives）放到分组子句中，聚合计算是在分组完成后执行的
• CASE WHEN 逻辑没有和聚合函数结合，也没有提前将collection_time字段截断到日粒度，无法按天聚合统计

以下写法匹配你给出的样例统计口径：单条记录中col_a、col_b任意一个值小于0即判定为负值记录，两个值均大于等于0判定为正值记录，以MySQL语法为例：

SELECT
  DATE(collection_time) AS collection_time,
  SUM(CASE WHEN col_a < 0 OR col_b < 0 THEN 1 ELSE 0 END) AS count_negatives,
  SUM(CASE WHEN col_a >= 0 AND col_b >= 0 THEN 1 ELSE 0 END) AS count_positives
FROM 你的表名
GROUP BY DATE(collection_time)
ORDER BY collection_time DESC;

如果使用其他数据库，仅需要替换日期截断函数即可：

• PostgreSQL：将DATE(collection_time)替换为DATE_TRUNC('day', collection_time)::DATE
• Hive/Spark SQL：将DATE(collection_time)替换为TO_DATE(collection_time)
• Oracle：将DATE(collection_time)替换为TRUNC(collection_time)

• 第一步通过日期函数把带时分秒的采集时间截断到日粒度，作为唯一分组维度
• 分组后通过CASE WHEN逐行判定记录类型，符合负值判定规则的行给负值计数+1，符合正值判定规则的行给正值计数+1，通过SUM函数累加得到最终统计值
• 排序后输出即可得到和你预期完全一致的结果

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