客户运营报表要求展示每位客户的已支付订单数，包括零单客户。上线后，没有下单的客户显示一单；开发把支付条件移到 WHERE 后，零单客户又全部消失。两次修改都能正常执行，异常藏在结果集合里。
本文用 SQLite 和 Python 标准库复现这两个问题。示例只有客户与订单的一对多关系，不涉及多张明细表交叉放大；目标是同时保证“客户不遗漏”和“订单计数正确”。
1. 先确定报表口径
约定如下：每个租户内一行对应一位客户；只统计 paid 订单；无订单、只有未支付订单的客户都必须显示零；其他租户的数据不得参与统计。客户编号和订单编号都只在租户内唯一。
排查时先选三位受控客户：一位没有订单，一位只有未支付订单，一位有两笔已支付订单。记录客户主键和预期计数即可，不必把姓名、电话或订单明细写进日志。
不要只核对总订单数：即使总数正确，也可能已经漏掉零单客户。还应比较返回的客户集合。
2. 最小复现：没有订单，为什么 COUNT(*) 仍为一
下面 SQL 可直接在 SQLite 客户端执行：
CREATE TABLE customer (
tenant_id INTEGER NOT NULL,
id INTEGER NOT NULL,
PRIMARY KEY (tenant_id, id)
);
CREATE TABLE purchase_order (
tenant_id INTEGER NOT NULL,
id INTEGER NOT NULL,
customer_id INTEGER NOT NULL,
status TEXT NOT NULL,
PRIMARY KEY (tenant_id, id)
);
INSERT INTO customer VALUES (1, 101), (1, 102), (1, 103);
INSERT INTO purchase_order VALUES
(1, 201, 102, 'pending'),
(1, 202, 103, 'paid'),
(1, 203, 103, 'paid');
SELECT c.id, COUNT(*) AS paid_count
FROM customer AS c
LEFT JOIN purchase_order AS o
ON o.tenant_id = c.tenant_id
AND o.customer_id = c.id
AND o.status = 'paid'
WHERE c.tenant_id = 1
GROUP BY c.tenant_id, c.id
ORDER BY c.id;
结果为 (101, 1)、(102, 1)、(103, 2)，前两行错误。
先去掉聚合，观察关联后的行：
SELECT c.id AS customer_id, o.id AS order_id, o.status
FROM customer AS c
LEFT JOIN purchase_order AS o
ON o.tenant_id = c.tenant_id
AND o.customer_id = c.id
AND o.status = 'paid'
WHERE c.tenant_id = 1
ORDER BY c.id, o.id;
101、102 各保留一行，但 order_id 与 status 都为 NULL。103 有两行实际订单。LEFT JOIN 对没有匹配的左侧记录补出右侧为 NULL 的行，COUNT(*) 仍然计算这行。
SQLite 聚合函数文档规定，COUNT(*) 统计组内所有行，COUNT(X) 只统计 X 非 NULL 的行。因此这里应计数右表中声明为非空的订单键，而不是客户键，也不是可能为空的备注字段。
3. 修复：右表过滤放 ON，计数使用非空订单键
SELECT c.id, COUNT(o.id) AS paid_count
FROM customer AS c
LEFT JOIN purchase_order AS o
ON o.tenant_id = c.tenant_id
AND o.customer_id = c.id
AND o.status = 'paid'
WHERE c.tenant_id = 1
GROUP BY c.tenant_id, c.id
ORDER BY c.id;
结果应为 (101, 0)、(102, 0)、(103, 2)。
这里三处逻辑各有职责：
ON描述哪些订单算作匹配，包括租户、客户与支付状态。WHERE限制需要展示的客户集合。COUNT(o.id)排除补空行，保留真实匹配订单。
如果把 o.status = 'paid' 移到 WHERE，补空行的比较结果不是 true，101 会被过滤；102 虽然有订单，但它的唯一订单为 pending，也会被过滤。SQLite SELECT 文档说明外连接补空与 WHERE 过滤的先后关系。
WHERE o.status = 'paid' OR o.id IS NULL 也不是通用修复。102 已匹配到 pending 行，不会再生成补空行，两项条件都不能保留它。只要业务要求展示全部客户，就应让支付状态参与匹配条件。
若业务改成“只展示至少有一笔已支付订单的客户”，则应明确改变报表口径，可以使用内连接，或者在正确聚合后使用 HAVING COUNT(o.id) > 0。不要让一个偶然的筛选位置替业务作决定。
4. 可运行回归：检查零值、集合与租户隔离
把下面完整示例保存为 test_customer_counts.py，运行 python -m unittest -v test_customer_counts。示例连接仅使用内存数据库；调用方负责连接生命周期。
import sqlite3
import unittest
REPORT_SQL = """
SELECT c.id, COUNT(o.id) AS paid_count
FROM customer AS c
LEFT JOIN purchase_order AS o
ON o.tenant_id = c.tenant_id
AND o.customer_id = c.id
AND o.status = 'paid'
WHERE c.tenant_id = ?
GROUP BY c.tenant_id, c.id
ORDER BY c.id
"""
def read_counts(
connection: sqlite3.Connection, tenant_id: int
) -> list[tuple[int, int]]:
"""按已经授权的租户查询客户计数，保留零单客户。"""
if type(tenant_id) is not int or tenant_id <= 0:
raise ValueError("租户编号必须为正整数")
return connection.execute(REPORT_SQL, (tenant_id,)).fetchall()
class CustomerCountsTests(unittest.TestCase):
"""验证报表口径与查询边界。"""
def setUp(self) -> None:
self.connection = sqlite3.connect(":memory:")
self.addCleanup(self.connection.close)
self.connection.executescript("""
CREATE TABLE customer (
tenant_id INTEGER NOT NULL,
id INTEGER NOT NULL,
PRIMARY KEY (tenant_id, id)
);
CREATE TABLE purchase_order (
tenant_id INTEGER NOT NULL,
id INTEGER NOT NULL,
customer_id INTEGER NOT NULL,
status TEXT NOT NULL,
PRIMARY KEY (tenant_id, id)
);
INSERT INTO customer VALUES
(1, 101), (1, 102), (1, 103), (2, 101);
INSERT INTO purchase_order VALUES
(1, 201, 102, 'pending'),
(1, 202, 103, 'paid'),
(1, 203, 103, 'paid'),
(1, 204, 103, 'cancelled'),
(2, 201, 101, 'paid');
""")
def test_all_customers_are_preserved(self) -> None:
self.assertEqual(
read_counts(self.connection, 1),
[(101, 0), (102, 0), (103, 2)],
)
def test_only_pending_orders_count_as_zero(self) -> None:
self.assertEqual(dict(read_counts(self.connection, 1)).get(102), 0)
def test_other_tenant_is_isolated(self) -> None:
self.assertEqual(read_counts(self.connection, 2), [(101, 1)])
def test_unknown_tenant_has_no_customers(self) -> None:
self.assertEqual(read_counts(self.connection, 3), [])
def test_empty_orders_preserve_all_customers(self) -> None:
self.connection.execute("DELETE FROM purchase_order")
self.assertEqual(
read_counts(self.connection, 1),
[(101, 0), (102, 0), (103, 0)],
)
def test_invalid_tenant_is_rejected(self) -> None:
for tenant_id in (0, -1, True, "1", None):
with self.subTest(tenant_id=tenant_id):
with self.assertRaises(ValueError):
read_counts(self.connection, tenant_id)
tenant_id 的格式检查不等于授权。正式接口应从服务端已鉴权上下文取得租户，并保持 SQL 参数绑定；不能把浏览器提交的租户编号直接当作访问权限。
回归检查还应故意恢复两种故障：把 COUNT(o.id) 改回 COUNT(*)，零订单断言必须失败；把状态条件移到 WHERE，完整客户集合断言必须失败。这能确认测试确实覆盖问题，而非仅验证 SQL 可以执行。
5. 上线前的性能与模型检查
本文模型已明确声明 id NOT NULL，因此可用作计数对象。若改用可空业务字段，即使订单存在也可能被漏算。COUNT(DISTINCT o.id) 对补空行也会排除 NULL，但不能用它掩盖模型或额外 JOIN 导致的重复；新增订单明细等关联时，要重新核对一行的业务粒度。
订单量较大时，可以先评估如下索引：
CREATE INDEX idx_purchase_order_customer_status
ON purchase_order (tenant_id, customer_id, status);
在目标库使用 EXPLAIN QUERY PLAN 加上修复后的 SELECT，检查订单关联是否使用合适的搜索路径。索引是否值得增加，要结合真实数据分布、写入代价和现有索引判断；不能根据这几行演示数据承诺耗时。生产索引创建还应评估锁等待与维护窗口。
本例没有声明外键，是为了把验证范围限制在报表查询；生产模型仍需独立落实关联完整性。数据量大时还应给客户列表设计分页，并确保分页对象是客户而不是关联后的订单行。
6. 验证范围与结论
本文回归使用 Python 标准库 SQLite 内存数据库，覆盖无订单、只有未支付订单、多笔已支付订单、其他状态、跨租户同号、空订单表、未知租户及非法租户参数。不代表已经验证生产权限系统、ORM 生成的 SQL、文件数据库并发、大数据量性能或其他数据库方言。
处理这类报表故障时，先展开关联后的原始行，再检查计数对象，最后核对筛选条件的归属。完整客户集合与正确订单数量必须一起验证，才能避免把“零变一”修成“客户消失”。
Discussion
评论