SQLite FILTER 条件聚合：合格数量为零的组，怎样仍然留在结果里
报表要显示零，筛选却删掉了整组
按区域统计任务时，常常需要同时看到总任务数、成功数和有数值的成功记录数。如果把 status=ok 写进 WHERE，失败记录会先从输入里消失。一个区域若没有成功任务，连区域名称也不会出现在报表中，前端就分不清它是成功数为零，还是根本没有数据。
FILTER 可以只约束某个聚合函数接收的记录，让同一分组内的总数和条件数量各自计算。下面在 Python 3.12.14、SQLite 3.53.1 的内存数据库实跑。把整段存为 demo.py，执行 python demo.py，脚本会创建五条演示记录，不读写已有数据库。
先把三个统计口径写在同一行
east 有三条记录，其中两条成功，但只有一条成功记录带数值；west 有两条记录，没有任何成功记录。COUNT(*) 负责数行，COUNT(value) 只数非空 value。FILTER 中的条件也不是给整个查询加筛选，它只决定当前这个聚合接收哪些行。
代码随后执行 WHERE 版本，验证 west 消失的原因，再用 HAVING 对已经形成的分组结果做筛选。最后把条件值替换成不存在的状态，检查两个原本存在的区域能否都以零成功数返回。占位符只绑定状态值，便于复用同一条查询。
AI概念示意图：过滤杯只计入满足条件的记录，两个分组托盘仍各自保留。图片只说明概念，不是运行截图。
import sqlite3
con = sqlite3.connect(":memory:")
try:
con.execute("CREATE TABLE jobs(region TEXT, status TEXT, value INTEGER)")
con.executemany("INSERT INTO jobs VALUES (?, ?, ?)", [
("east", "ok", 10),
("east", "bad", None),
("east", "ok", None),
("west", "bad", 20),
("west", None, 5),
])
query = """
SELECT region, COUNT(*),
COUNT(*) FILTER (WHERE status = ?),
COUNT(value) FILTER (WHERE status = ?)
FROM jobs
GROUP BY region
ORDER BY region
"""
rows = con.execute(query, ("ok", "ok")).fetchall()
assert rows == [("east", 3, 2, 1), ("west", 2, 0, 0)]
print("filtered:", rows)
rows = con.execute("""
SELECT region, COUNT(*) FROM jobs
WHERE status = ? GROUP BY region ORDER BY region
""", ("ok",)).fetchall()
assert rows == [("east", 2)]
print("where:", rows)
rows = con.execute("""
SELECT region FROM jobs GROUP BY region
HAVING COUNT(*) FILTER (WHERE status = ?) > 0
ORDER BY region
""", ("ok",)).fetchall()
assert rows == [("east",)]
print("having:", rows)
rows = con.execute(query, ("missing", "missing")).fetchall()
assert rows == [("east", 3, 0, 0), ("west", 2, 0, 0)]
print("no matches:", rows)
finally:
con.close()从列位置解释实跑结果
filtered 输出 east 对应 (3, 2, 1)，west 对应 (2, 0, 0)。三个数字依次是总行数、成功行数、成功且 value 非空的行数。east 的第二条成功记录不是丢了，而是被第三列的非空计数口径排除。先写明指标定义，才能知道哪一列应该为二、哪一列应该为一。
where 只返回 east 和数量二，正是因为过滤发生在分组之前。having 也只留下 east，但它是在完整分组与条件聚合之后，根据成功数量大于零决定保留哪一组。这两个例子的最终区域相同，操作的位置却不同，不能因此认定它们在所有报表里都可互换。
no matches 中两个区域都保留了原始总数，后两列变为零。它是本例最重要的验收条件：改变成功判定，不该悄悄改变报表的区域全集。status 为 NULL 的那条记录不满足等号条件，因此也不会进入成功计数。
有分组与没有分组仍是两种状态
FILTER 能保住已由输入形成的组，却不能凭空制造从未出现的区域。若报表必须列出全部组织，应另有完整的组织清单，再明确怎样连接事实记录。该步骤属于报表数据模型，本例只验证现有两个分组在条件聚合时不会被意外移除。
COUNT(expr) 与 COUNT(*) 的区别在加 FILTER 后仍然存在；SUM 等其他函数还各有空输入返回约定，不能把此处的零直接推广过去。多项指标使用不同过滤条件时，最好把每项口径写在字段名或报表说明里，让读者知道分母是否一致。
示例用 ORDER BY 保证输出顺序便于断言，排序本身不改变统计口径。迁移到其他数据库时还要核对是否支持该语法。本次没有测试大表性能或索引方案；先用少量能手算的数据把零值、空值和分组保留条件验清楚，再讨论执行计划。
