确保每笔订单总额正确

介绍

PAPER & PIER 在两个集市摊位销售小商品。收摊前，工作台需要核对每笔订单的商品金额、实际收款和退款。一笔订单可能包含多件商品，也可能有多笔真实付款。草率地连接这些原始列表，会让一笔实际已付清的订单看起来有问题。

你将创建三个可复用的 SQLite 视图，分别汇总每类事实。你需要掌握基本终端导航和 SQL SELECT/WHERE。这里会讲解聚合、连接、视图和缺失值。SQLite 和合成账本已准备好；全程使用 Terminal。

保留所有原始表、ID 和值。你只可以创建或替换 item_totals、payment_totals 和 refund_totals 视图，并将定义保存到 totals.sql。金额以整数分表示；这是简化的收款对账，不涉及税费、支付服务商结算或货币转换。

为每笔订单保留一个商品总额

本步骤中，你将读取一笔正常订单，观察原始数据直接连接如何使金额重复，并保存准确的商品总额视图。从 Terminal 开始。cd 用于切换目录；如果进入了错误目录，也可以用这个绝对路径返回：

cd /home/labex/project/sales-desk

sqlite3 打开指定数据库。-header 显示列名，-column 对齐各列的值，引号中的 SQL 在连接关闭前执行。引号让 SQL 中的空格和分号保留在同一个 shell 参数中。读取已准备好的订单标识：

sqlite3 -header -column ledger.db "SELECT * FROM orders ORDER BY order_id;"

共有四笔订单。A1 和 A2 是不同订单，尽管两者的顾客标签都是 Alex Kim。应按订单 ID 分组，绝不能按顾客姓名分组。读取 A1 的商品行和支付事件：

sqlite3 -header -column ledger.db "SELECT * FROM items WHERE order_id = 'A1'; SELECT * FROM payments WHERE order_id = 'A1';"

其商品金额为 300 + 500 = 800 分。付款 P1 和 P2 是两笔不同的、成功的 400 分支付事件，总额同样为 800。P3 失败，没有带来收款。succeeded 表示该事件计入本项目的金额计算；pending 和 failed 不计入。

连接会组合相关行。AS 指定简短的表别名：i 表示商品，p 表示付款。ON 指定按订单 ID 匹配。汇总前，先查看 A1 的每一对匹配：

sqlite3 -header -column ledger.db "
SELECT i.item_id, i.unit_cents, p.payment_id, p.amount_cents
FROM items AS i
JOIN payments AS p ON p.order_id = i.order_id
WHERE i.order_id = 'A1' AND p.status = 'succeeded'
ORDER BY i.item_id, p.payment_id;"

结果有四行：两件商品中的每一件都匹配两笔付款。这是真实的连接行为，并不意味着发生了四件商品或四笔付款。SUM 对输入行中的表达式求和。在这组重复输入上，两个总额都不正确：

sqlite3 -header -column ledger.db "
SELECT SUM(i.quantity * i.unit_cents) AS joined_due,
       SUM(p.amount_cents) AS joined_paid
FROM items AS i
JOIN payments AS p ON p.order_id = i.order_id
WHERE i.order_id = 'A1' AND p.status = 'succeeded';"

显示的两个总额都是 1600，是真实金额的两倍。DISTINCT 保留不同的值，而不是不同的付款标识。它无法修复这个问题：

sqlite3 -header -column ledger.db "
SELECT SUM(DISTINCT amount_cents) AS wrong_paid
FROM payments WHERE order_id = 'A1' AND status = 'succeeded';"

结果为 400，丢掉了一笔合法的同额付款。正确做法是分别汇总每个事实列表。GROUP BY order_id 为每个订单 ID 生成一行结果。quantity * unit_cents 在求和前计算每个商品行的金额。结果的粒度，即一行输出所代表的对象，是一笔订单。

视图是存储在数据库中的命名 SELECT。读取视图会针对当前源数据行执行该 SELECT，不会复制或替换商品数据。将其定义保存到 totals.sql。在 shell 中，cat 将下面按字面保留内容的 here-document 写入文件；> 替换原有内容。最后的 SQL 应独占一行。此代码块应在 shell 提示符下输入，而不是在 SQLite 交互式提示符下输入：

cat > totals.sql <<'SQL'
DROP VIEW IF EXISTS item_totals;
CREATE VIEW item_totals AS
SELECT order_id, SUM(quantity * unit_cents) AS due_cents
FROM items
GROUP BY order_id;
SQL

DROP VIEW IF EXISTS 只在视图已有定义时删除该定义，不改变源表或数据。CREATE VIEW 保存新定义。< 将保存的文件作为 SQLite 的输入：

sqlite3 ledger.db < totals.sql

成功时预期没有输出。读取实际视图：

sqlite3 -header -column ledger.db "SELECT * FROM item_totals ORDER BY order_id;"

预期 A1 = 800、A2 = 250、A3 = 500、A4 = 200 分，每笔订单各占一行。A3 的数量 2 也已计入。现在你有了第一个实用成果：可在另一个兼容账本上运行的视图定义文件。

如果找不到文件，ls 可以列出本目录中的文件。检查绝对路径 cd 以及 totals.sql 的拼写。SQL 语法错误会指出出错语句；重新执行完整创建代码块，替换输错的文件，然后再次应用。如果 here-document 之后 shell 仍在等待，输入缺失的最后一行 SQL。不要通过删除账本来恢复。

组合独立的金额总额

本步骤中，你将把成功付款和退款的总额加入已保存的脚本，再组合三个每笔订单一行的事实，同时保留未付款订单。从 Terminal 开始，工作目录为 /home/labex/project/sales-desk。

同一聚合规则也适用于付款和退款：每个真实事件都要计入，包括 ID 不同但金额相同的事件。在各类事实的 SELECT 内部、分组之前，按 status = 'succeeded' 筛选。待处理退款不能减少已收金额。读取退款依据：

sqlite3 -header -column ledger.db "SELECT * FROM refunds ORDER BY refund_id;"

A3 有两笔独立且成功的 50 分退款，以及一笔待处理的 250 分退款。实际退款总额为 100，而非 50 或 350。

追加另外两个视图定义。>> 在现有文件末尾添加内容，不会替换商品视图。如果为修复错误而需要重新追加，先用上一步的完整代码块重建 totals.sql，再将此代码块追加一次：

cat >> totals.sql <<'SQL'
DROP VIEW IF EXISTS payment_totals;
CREATE VIEW payment_totals AS
SELECT order_id, SUM(amount_cents) AS paid_cents
FROM payments
WHERE status = 'succeeded'
GROUP BY order_id;

DROP VIEW IF EXISTS refund_totals;
CREATE VIEW refund_totals AS
SELECT order_id, SUM(amount_cents) AS refunded_cents
FROM refunds
WHERE status = 'succeeded'
GROUP BY order_id;
SQL

sqlite3 ledger.db < totals.sql

保存的脚本可以重复应用：每个定义都会被删除，再从未改变的源事实重新创建。读取金额视图：

sqlite3 -header -column ledger.db "SELECT * FROM payment_totals ORDER BY order_id; SELECT * FROM refund_totals ORDER BY order_id;"

付款总额中有 A1 = 800、A3 = 600、A4 = 250。退款总额中只有 A3 = 100。A2 在两者中都不存在，因为没有符合条件的付款或退款。某类事实总额中缺少一笔订单，不能导致该订单从对账中消失。

即使没有匹配的右侧行，LEFT JOIN 也会保留左侧的每一行。从 orders 开始，再按订单 ID 连接各个已聚合的视图。缺少匹配时得到 SQL NULL，表示没有值，而不是零。COALESCE(value, 0) 仅在该总额为 NULL 时用零替代。事件状态条件已经放在金额视图内部；如果再针对缺失的连接行添加 WHERE 条件，就可能丢弃未付款订单。

sqlite3 -header -column ledger.db "
SELECT o.order_id, o.customer,
       COALESCE(i.due_cents, 0) AS due_cents,
       COALESCE(p.paid_cents, 0) AS paid_cents,
       COALESCE(r.refunded_cents, 0) AS refunded_cents
FROM orders AS o
LEFT JOIN item_totals AS i ON i.order_id = o.order_id
LEFT JOIN payment_totals AS p ON p.order_id = o.order_id
LEFT JOIN refund_totals AS r ON r.order_id = o.order_id
ORDER BY o.order_id;"

结合正常业务解读每一行：

连接前，每个事实视图对每笔订单至多有一行，因此一类事实不会使另一类事实重复。保留原来的四个表及其全部记录；只需添加三个视图和 totals.sql 这个文件。下一个实验将从独立的新账本开始，把这种推理转化为完整对账查询。

总结

你揭示了两种金额汇总陷阱，保存了可复用的、每笔订单一行的事实视图，并使用 LEFT JOIN 和 COALESCE 组合它们。金额相同的独立事件仍分别保留，未付款订单也始终可见。