订单明细增加退款展示后，报表的销售额突然变大。单独查订单金额正确，单独查退款也正确，只有合并查询对不上。此时先检查关联后的数据粒度：一张订单同时对应多条商品明细和多条退款时，两组明细会组合成多行，最后的 SUM 会把同一金额累计多次。
本文以 PostgreSQL 17 为语义基准，用一套可复现数据定位问题，再把查询改成“各自汇总到订单粒度后关联”。金额使用整数分，避免浮点误差。本文的净额只是商品明细金额减成功退款金额的示例口径，正式财务报表还需明确优惠、运费、税费和收入确认规则。
适用场景与现象
- 订单同时关联商品明细、退款、支付流水或发货记录。
- 新增一个
LEFT JOIN后，销售额或计数变大，但 SQL 没有报错。 - 只有多次退款或多次支付的订单异常；只有一条关联记录的测试订单正常。
- 给金额套上
DISTINCT后，部分样本恢复，其他订单又出现漏算。
这类故障通常发生在“最后才按客户分组”的查询里。最终分组只能把关联结果归到一起，无法恢复已经被复制的明细。
一、建立隔离样本，先复现差异
以下 SQL 在独立 PostgreSQL 会话执行，使用临时表，不修改业务表。也可以保存成 reproduce.sql，用 psql -X -v ON_ERROR_STOP=1 -f reproduce.sql 执行；连接信息使用本机既有配置，不把密码写入命令或 SQL。
CREATE TEMP TABLE report_order (
tenant_id bigint NOT NULL,
id bigint NOT NULL,
customer_id bigint NOT NULL,
PRIMARY KEY (tenant_id, id)
);
CREATE TEMP TABLE order_item (
tenant_id bigint NOT NULL,
id bigint NOT NULL,
order_id bigint NOT NULL,
amount_cents bigint NOT NULL CHECK (amount_cents >= 0),
PRIMARY KEY (tenant_id, id),
FOREIGN KEY (tenant_id, order_id) REFERENCES report_order (tenant_id, id)
);
CREATE TEMP TABLE order_refund (
tenant_id bigint NOT NULL,
id bigint NOT NULL,
order_id bigint NOT NULL,
amount_cents bigint NOT NULL CHECK (amount_cents >= 0),
status text NOT NULL CHECK (status IN ('succeeded', 'pending', 'failed')),
PRIMARY KEY (tenant_id, id),
FOREIGN KEY (tenant_id, order_id) REFERENCES report_order (tenant_id, id)
);
INSERT INTO report_order VALUES
(1, 101, 7), (1, 102, 7), (1, 103, 8), (2, 101, 7);
INSERT INTO order_item VALUES
(1, 11, 101, 10000), (1, 12, 101, 10000),
(1, 13, 102, 5000), (2, 21, 101, 90000);
INSERT INTO order_refund VALUES
(1, 31, 101, 1000, 'succeeded'),
(1, 32, 101, 2000, 'succeeded'),
(1, 33, 101, 9000, 'pending'),
(2, 41, 101, 8000, 'succeeded');
租户 1 的订单 101 有两条商品明细，各 10000 分，两条成功退款分别为 1000 和 2000 分。订单 102 有一条 5000 分的商品明细，无退款；订单 103 没有明细，是保留零值行的边界样本。租户 2 故意使用相同订单 ID，用来检测租户隔离。
下面是错误查询：
SELECT o.customer_id,
COUNT(*) AS joined_rows,
SUM(i.amount_cents) AS gross_cents,
COALESCE(SUM(r.amount_cents), 0) AS refund_cents
FROM report_order AS o
LEFT JOIN order_item AS i
ON i.tenant_id = o.tenant_id AND i.order_id = o.id
LEFT JOIN order_refund AS r
ON r.tenant_id = o.tenant_id AND r.order_id = o.id
AND r.status = 'succeeded'
WHERE o.tenant_id = 1
GROUP BY o.customer_id
ORDER BY o.customer_id;
客户 7 的查询结果是销售额 45000 分、退款 6000 分；正确结果应为 25000 分、3000 分。订单 101 的两条明细与两条退款产生四个组合，商品和退款都重复了。订单 102 再贡献一行，所以 joined_rows 为 5，而订单数只有 2。
二、取证时去掉聚合，直接看关联行
不要一开始就修改索引或调整数据库参数。先选一张对不上账的订单，把最终 SUM 拿掉：
SELECT o.id AS order_id, i.id AS item_id, r.id AS refund_id,
i.amount_cents AS item_cents, r.amount_cents AS refund_cents
FROM report_order AS o
LEFT JOIN order_item AS i
ON i.tenant_id = o.tenant_id AND i.order_id = o.id
LEFT JOIN order_refund AS r
ON r.tenant_id = o.tenant_id AND r.order_id = o.id
AND r.status = 'succeeded'
WHERE o.tenant_id = 1 AND o.id = 101
ORDER BY i.id, r.id;
输出的 (item_id, refund_id) 为 (11,31)、(11,32)、(12,31)、(12,32)。不是数据库把数据存重了，而是关联按匹配规则返回了全部组合。
复核每张表的粒度，并在 SQL 评审中写清楚：订单表每订单一行，商品表每商品明细一行，退款表每退款记录一行。两个“一对多”分支直接接到同一个父表上时，有商品 m 条、成功退款 n 条的订单会形成 m × n 行；左关联没有匹配记录时还会保留一条空值占位行。PostgreSQL 表表达式文档说明了这些关联规则。
三、修复：先汇总到相同粒度，再做关联
分别按 (tenant_id, order_id) 汇总两个明细分支，使每个分支对于一张订单最多返回一行。最后再按客户汇总：
WITH selected_orders AS (
SELECT tenant_id, id, customer_id
FROM report_order
WHERE tenant_id = 1
), item_totals AS (
SELECT i.tenant_id, i.order_id, SUM(i.amount_cents) AS gross_cents
FROM order_item AS i
JOIN selected_orders AS o
ON o.tenant_id = i.tenant_id AND o.id = i.order_id
GROUP BY i.tenant_id, i.order_id
), refund_totals AS (
SELECT r.tenant_id, r.order_id, SUM(r.amount_cents) AS refund_cents
FROM order_refund AS r
JOIN selected_orders AS o
ON o.tenant_id = r.tenant_id AND o.id = r.order_id
WHERE r.status = 'succeeded'
GROUP BY r.tenant_id, r.order_id
)
SELECT o.customer_id,
COUNT(*) AS order_count,
SUM(COALESCE(i.gross_cents, 0)) AS gross_cents,
SUM(COALESCE(r.refund_cents, 0)) AS refund_cents,
SUM(COALESCE(i.gross_cents, 0) - COALESCE(r.refund_cents, 0)) AS net_cents
FROM selected_orders AS o
LEFT JOIN item_totals AS i
ON i.tenant_id = o.tenant_id AND i.order_id = o.id
LEFT JOIN refund_totals AS r
ON r.tenant_id = o.tenant_id AND r.order_id = o.id
GROUP BY o.customer_id
ORDER BY o.customer_id;
预期结果：
| customer_id | order_count | gross_cents | refund_cents | net_cents |
|---|---|---|---|---|
| 7 | 2 | 25000 | 3000 | 22000 |
| 8 | 1 | 0 | 0 | 0 |
selected_orders 统一定义报表订单范围。真实接口应将租户、日期等输入作为绑定参数传入，而不是拼接 SQL；租户范围必须来自服务端鉴权。大表按实际口径增加订单状态及时间条件，通常使用 [start_at, end_at) 半开区间。
两个明细汇总都只处理选中的订单。这里的 COUNT(*) 才是订单数，因为关联双方已经保证每订单最多一行。COALESCE 表达“没有成功退款按零计算”的业务口径；PostgreSQL 的 SUM 对空输入返回空值，而不是自动返回零，见聚合函数文档。若业务金额本身允许 NULL，需明确它代表未知金额还是零，不能直接把未知值吞掉。
退款状态在 refund_totals 内过滤。把 r.status = 'succeeded' 放在原始左关联查询的最外层 WHERE，会排除没有退款的订单，改变报表范围。修金额时要一并检查是否漏订单。
四、为什么 DISTINCT 经常把错修成另一个错
下面的替换不能作为金额修复方案：
SELECT SUM(DISTINCT amount_cents) AS wrong_gross_cents
FROM order_item
WHERE tenant_id = 1 AND order_id = 101;
结果只有 10000 分。两条合法商品明细金额恰好相同，DISTINCT 按值去重，无法区分业务记录身份。金额相同不等于记录重复。
COUNT(DISTINCT o.id) 可以在固定租户范围内修正订单计数，但不会修复 SUM(i.amount_cents)；跨租户统计还要考虑完整订单键。最终 SELECT DISTINCT 也无法修复已累计的错误金额。
如果业务只需要“订单是否有成功退款”，无需引入退款明细，使用存在性判断即可：
SELECT o.id
FROM report_order AS o
WHERE o.tenant_id = 1
AND EXISTS (
SELECT 1
FROM order_refund AS r
WHERE r.tenant_id = o.tenant_id
AND r.order_id = o.id
AND r.status = 'succeeded'
)
ORDER BY o.id;
该查询返回订单 101 一次。只有需要退款金额时，才采用退款汇总；查询形态应服务于指标口径。
五、上线前验证与性能检查
至少保留以下回归样本：
- 两条相同金额商品、两条成功退款：同时检查销售额与退款额，防止用金额去重。
- 有商品、无退款：订单保留，退款为零。
- 无商品、无退款：按明确口径保留零值行或显式排除，不能依赖关联偶然行为。
- 退款只有 pending 或 failed：成功退款金额为零，订单仍保留。
- 两个租户使用相同订单 ID：金额不能串到另一个租户。
- 订单范围为空：不产生客户汇总行；若 API 需要全局零值总计，由接口契约单独定义。
在相同数据快照下，把新 SQL 的商品总额与订单明细单独汇总结果对账，把退款总额与成功退款单独汇总结果对账。不要让旧查询和新查询隔几分钟分别执行后直接比较：期间新订单或退款可能改变数据。可在隔离环境使用短时间只读一致性事务完成核对，避免把事务跨人工等待或网络调用长期保持。
性能检查在 PostgreSQL 环境进行。先用 EXPLAIN 看计划，再在可控副本或测试数据上执行 EXPLAIN (ANALYZE, BUFFERS)；后者会实际运行查询。重点检查每个汇总分支处理的实际行数、关联后订单粒度是否保持，以及是否存在过大的排序或哈希聚合。PostgreSQL EXPLAIN 文档提供字段解释。
候选索引为商品表的 (tenant_id, order_id)，以及退款表的 (tenant_id, order_id) 成功状态部分索引；这是结合本查询形态的设计建议，需用真实数据分布验证。索引只能改善访问成本，无法改变关联的金额口径。CTE 也不保证强制物化，不能据此宣称修复后一定更快。
本文修复查询已使用本地 SQLite 内存数据验证上述六类结果，并复现错误查询的 45000/6000 分；这是关系查询语义验证。本次没有 PostgreSQL 实例验证，不提供 PostgreSQL 执行计划或性能收益数字。
上线时先让新查询并行计算而不替换正式报表，核对同一范围的差额及代表性订单，确认指标口径后切换。保留查询版本便于回退；如果旧结果已经影响结算或导出，需要标记受影响时间范围并重新生成，单纯切换 SQL 不会修正历史产物。
总结
报表聚合异常时，先证明关联后的每一行代表什么，再讨论 SUM。多个明细分支分别汇总到订单粒度，保留完整租户键，并用等额明细、多条退款、缺失关联和空范围测试守住口径。这样才能同时修复金额重复累计和订单漏算。
Discussion
评论