PostgreSQL枚举行count统计错误 SQLAlchemy多count查询异常

你的写法错误本质是对COUNT()聚合函数的统计逻辑理解有误：

• COUNT(任意表达式)的统计规则是：统计表达式返回结果不为NULL的总行数，它不会自动跳过「表达式返回FALSE」的行。
• 你传入的a.status == 状态值是布尔判断表达式：匹配的行返回TRUE，不匹配的行返回FALSE。在标准SQL中TRUE和FALSE都是非NULL值，因此不管行是否匹配状态条件，都会被COUNT计入统计。你查询返回的两个7，本质是WHERE a.uuid = some_uuid过滤后的总行数，和状态条件没有关系。

至于你提到拆分单个查询能得到正确结果，大概率是单查询执行时，SQLAlchemy结合你使用的数据库方言（比如支持聚合FILTER语法的PostgreSQL），自动改写了生成的SQL；但多字段聚合时没有触发这个自动改写，最终生成了错误的原生SQL，才出现了结果偏差。

有两种通用的正确写法，兼容绝大多数数据库：

方案1：COUNT + CASE表达式（兼容性最好） #

手动让不满足条件的行返回NULL，COUNT会自动跳过这些NULL值，得到正确统计结果：

from sqlalchemy import case

_stats = db.session.query(
    func.count(case((a.status==s.STATUS_IN_PROGRESS, 1))).label("in_progress_"),
    func.count(case((a.status==s.STATUS_COMPLETED, 1))).label("completed_"),
) \
.filter(a.uuid==some_uuid) \
.first()

对应生成的原生SQL逻辑为：

SELECT 
  count(CASE WHEN a.status = 'IN_PROGRESS' THEN 1 END) AS in_progress_,
  count(CASE WHEN a.status = 'COMPLETED' THEN 1 END) AS completed_
FROM a
WHERE a.uuid = '9a353554a6874ebcbf0fe88eb8223d33'

CASE语句不指定ELSE分支时，不满足条件的场景默认返回NULL，刚好符合COUNT的统计规则。

方案2：SUM聚合布尔值（写法更简洁） #

在MySQL、PostgreSQL等支持布尔值隐式转数值（TRUE=1、FALSE=0）的数据库中，可以直接用SUM替代COUNT，对布尔结果求和，得到的就是符合条件的行数：

_stats = db.session.query(
    func.coalesce(func.sum(a.status==s.STATUS_IN_PROGRESS), 0).label("in_progress_"),
    func.coalesce(func.sum(a.status==s.STATUS_COMPLETED), 0).label("completed_"),
) \
.filter(a.uuid==some_uuid) \
.first()

套coalesce是为了避免没有匹配行时SUM返回NULL，保证结果始终返回数值0。

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