如何在单条SQL中实现按日期分组的多条件计数

这个需求完全可以实现，不需要拆分两条独立查询，通过条件聚合逻辑即可单次扫描表同时输出三类统计结果，执行效率比分别跑两条查询更高。

实现逻辑 #

• 原有基础查询已经实现了按天聚合统计总行数的能力，只需要在同一组聚合逻辑里，额外对满足jsonb字段筛选规则的行单独做计数，即可得到第三列online_count。
• jsonb字段的取值路径data->'payment'->>'type'是PostgreSQL的标准语法，和你单独写的条件统计查询里的过滤逻辑完全一致，不需要额外做类型转换。

推荐SQL写法 #

PostgreSQL 9.4及以上的主流版本，直接用原生支持的FILTER子句实现条件聚合，代码简洁可读性强：

SELECT
  created_at::timestamp::date AS date,
  count(id) AS all,
  count(id) FILTER (WHERE data->'payment'->>'type' = 'card-online') AS online_count
FROM myTable
WHERE created_at > current_date - interval '130' day
GROUP BY created_at::timestamp::date
ORDER BY date;

如果遇到低版本PostgreSQL不支持FILTER子句的场景，可以用CASE WHEN实现完全等价的逻辑，兼容所有版本：

SELECT
  created_at::timestamp::date AS date,
  count(id) AS all,
  count(CASE WHEN data->'payment'->>'type' = 'card-online' THEN id ELSE NULL END) AS online_count
FROM myTable
WHERE created_at > current_date - interval '130' day
GROUP BY created_at::timestamp::date
ORDER BY date;

注意事项 #

• all是PostgreSQL的保留关键字，作为列别名如果触发语法报错，可以用双引号把别名包裹为"all"，或者直接换成total这类非关键字名称，规避语法冲突。
• 两种写法的统计范围和你原有基础查询完全一致：仅统计最近130天内的行，按天分组后，all列对应当天的总数据量，online_count列对应当天总数据中满足card-online支付类型的行数，不会混入时间范围外的统计数据。

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