把总账和渠道流水做对账

目标

核对核心银行总账与渠道系统日志，找出只存在于渠道的交易、超时后的重复扣款、记账金额不一致的交易，以及没有总账记录却被修改的余额；使用更正交易纠正这些问题，并依据总账重新生成余额。

为什么重要

银行系统分为三层：客户直接操作的渠道系统（手机银行、ATM、柜面和开放银行）、持有账户与总账的核心银行系统，以及为分析和报告而复制数据的信息系统。一笔交易从渠道发起，记入核心银行系统，再把结果返回渠道。问题在于，这一往返过程并不总是完整。渠道可能返回成功，但总账记账失败；也可能总账已经记账，只是响应延迟，渠道将其记录为超时。在后一种情况下，客户以为交易失败而再次操作，结果资金被扣了两次。

这些不一致无法从界面上看出来。发现问题只有一种方法——在日终后对两边的记录进行核对。对于渠道声称成功的每笔交易，要在总账中寻找对应记录；对于总账中的每笔交易，也要寻找对应的渠道记录；还要筛出双方都有记录但金额不同的交易。此外，账户中的余额列只是保存下来的值，并不一定是真相，因此要将它与“期初余额加上总账交易”重新计算出的余额进行核对。

更正也有规定的方法。不得修改或删除原有总账行。遗漏的交易要以更正交易补记；错误记出的交易要用方向相反的交易抵销。总账必须按顺序保留所发生的一切，这样两个月后收到客户投诉时仍能说明情况。

本实验的规则

计算余额 = open_balance + 总账中的 DEP 合计 − 总账中的 WDR 合计。差额 = stored_balance − 计算余额。
渠道中结果为 SUCCESS 的交易，必须在总账中存在相同的 channel_ref，才算匹配。FAIL 在总账中没有记录是正常情况。
重复扣款 = 结果为 TIMEOUT 且已记入总账的交易发生后 10 分钟内（含正好 10 分钟，时间差按秒计算），又出现相同账户、相同类型、相同金额且结果为 SUCCESS 的交易。超过 10 分钟后的相同交易视为客户有意进行的另一笔交易。
更正：遗漏交易的 txn_id 使用 FIX-<channel_ref>，按原类型、原金额写入。错误记账交易的 txn_id 使用 RVS-<channel_ref>，以相反类型（撤销 WDR 时使用 DEP）、相同金额写入并进行抵销。金额错误的交易应先抵销，再用 FIX-<channel_ref> 重新写入正确金额。重复扣款用第二次记入的重试交易（SUCCESS 一方）的 channel_ref 写入 RVS- 进行抵销。

步骤

将右侧示例中的生成器保存为 /root/bank/make_core.py，并运行 python3 make_core.py。将生成 core.db（12 个账户、45 行总账）和 channel_20260825.csv（53 行渠道日志）。
根据总账重新计算全部 12 个账户的余额，并写入 /root/bank/balance_check.csv，表头为 acct_no,open_balance,ledger_net,computed,stored,gap。ledger_net 等于存款合计减去取款合计；gap 等于 stored 减去 computed。
在 /root/bank/recon_summary.txt 中写入八行：channel_rows=, channel_success=, channel_fail=, channel_timeout=, ledger_rows=, matched=, only_channel=, only_ledger=。matched 是总账中存在匹配记录的 SUCCESS 交易数；only_channel 是不存在匹配记录的 SUCCESS 交易数；only_ledger 是渠道中没有 SUCCESS 匹配记录的总账交易数。
将渠道显示成功但总账中不存在的交易写入 /root/bank/only_channel.csv，表头为 channel_ref,acct_no,type,amount。
将结果为 TIMEOUT 但已记入总账的交易写入 /root/bank/timeout_posted.csv，表头为 channel_ref,acct_no,amount,txn_id；将其后发生的重复扣款写入 /root/bank/double_debit.csv，表头为 first_ref,retry_ref,acct_no,amount。
将双方存在匹配记录但金额不同的交易写入 /root/bank/amount_diff.csv，表头为 channel_ref,channel_amount,ledger_amount,gap。gap 等于渠道金额减去总账金额。
按照上述规则将更正交易写入 ledger，并用计算余额重新填充全部 12 个账户的 stored_balance。将结果写入 /root/bank/balance_after.csv，表头为 acct_no,balance；并在 /root/bank/recheck.txt 中写入五行：fix_rows=, rvs_rows=, ledger_rows_after=, gap_accounts_before=, gap_accounts_after=。
在 /root/bank/recon_report.md 中撰写报告，包含 ## Reconciliation result（核对结果）、## Transactions only in the channel（仅存在于渠道的交易）、## Timeouts and double debits（超时与重复扣款）、## Correction method（更正方法）、## Preventing recurrence（防止复发）五个章节。

参考

使用 sqlite3 -readonly core.db 以只读方式打开数据库，仅在第 7 步执行更正时才以可写方式打开。可使用 .import --csv 将渠道日志加载到 channel 表中，再通过 SQL 进行核对。
核对的基本方法是执行两次 LEFT JOIN——一次以渠道为基准，一次以总账为基准。
常见错误 1：把 FAIL 交易计入 only_channel。失败交易在总账中没有记录是正常的。
常见错误 2：在 SUCCESS 交易中寻找重复扣款的“第一笔”。第一笔正是被记录为 TIMEOUT 的那笔交易。
常见错误 3：在第 7 步通过 UPDATE 或 DELETE 修改总账行。评分程序还会检查原始行是否原封不动地保留。

生成核心银行系统副本和渠道日志

将右侧示例中的生成器保存为 /root/bank/make_core.py，并运行 python3 make_core.py。将生成 core.db（12 个账户、45 行总账）和 channel_20260825.csv（53 行渠道日志）。

请将实验说明中的生成器保存为 /root/bank/make_core.py，并使用 python3 运行。如果手工修改，后续步骤中的所有数字都会出现偏差。

依据总账重建余额并进行核对

根据总账重新计算全部 12 个账户的余额，并写入 /root/bank/balance_check.csv，表头为 acct_no,open_balance,ledger_net,computed,stored,gap。ledger_net 等于存款合计减去取款合计；gap 等于 stored 减去 computed。

以 account 为基准对 ledger 执行 LEFT JOIN，并按账户计算 SUM(CASE type WHEN 'DEP' THEN amount ELSE -amount END)。使用 COALESCE 将没有交易的账户设为 0。有一个账户的 gap 不为 0。

加载渠道日志并从双方进行统计

在 /root/bank/recon_summary.txt 中写入八行：channel_rows=, channel_success=, channel_fail=, channel_timeout=, ledger_rows=, matched=, only_channel=, only_ledger=。matched 是总账中存在匹配记录的 SUCCESS 交易数；only_channel 是不存在匹配记录的 SUCCESS 交易数；only_ledger 是渠道中没有 SUCCESS 匹配记录的总账交易数。

使用 sqlite3 的 .import --csv 创建 channel 表（为便于重复运行，请先执行 DROP TABLE IF EXISTS），再按 channel_ref 对 ledger 向两个方向各执行一次 LEFT JOIN。应满足 matched + only_channel = channel_success 以及 matched + only_ledger = ledger_rows。

提取只存在于渠道的交易

将渠道显示成功但总账中不存在的交易写入 /root/bank/only_channel.csv，表头为 channel_ref,acct_no,type,amount。

使用第 3 步的 only_channel 查询，将 COUNT 替换为引用号、账户、类型和金额，并按 channel_ref 排序。不要包含 FAIL。

查找超时后的重复扣款

将结果为 TIMEOUT 但已记入总账的交易写入 /root/bank/timeout_posted.csv，表头为 channel_ref,acct_no,amount,txn_id；将其后发生的重复扣款写入 /root/bank/double_debit.csv，表头为 first_ref,retry_ref,acct_no,amount。

第一个文件来自 channel(result='TIMEOUT') 与 ledger 的 INNER JOIN。第二个文件在该结果上再次连接 channel，查找 acct_no、type、amount 相同，ts 在其后 10 分钟内（含 10 分钟）且结果为 SUCCESS 的交易。julianday 差值 × 1440 是浮点数，正好 10 分钟会算成 10.0000000745 而漏掉边界——请用 strftime('%s', ts) 相减得到以秒为单位的整数，再与 600 比较。

查找记账金额不一致的交易

将双方存在匹配记录但金额不同的交易写入 /root/bank/amount_diff.csv，表头为 channel_ref,channel_amount,ledger_amount,gap。gap 等于渠道金额减去总账金额。

按 channel_ref 对 channel 和 ledger 执行 INNER JOIN，只保留金额不同的行。通过 .import 创建的表中 amount 类型为 TEXT——与 ledger.amount 这样的 INTEGER 列比较时，SQLite 会自动按数字处理，但与字面量比较大小或排序时会按字符串处理，所以请用 CAST(... AS INTEGER) 明确它是数字。

写入更正交易并重建余额

按照上述规则将更正交易写入 ledger，并用计算余额重新填充全部 12 个账户的 stored_balance。将结果写入 /root/bank/balance_after.csv，表头为 acct_no,balance；并在 /root/bank/recheck.txt 中写入五行：fix_rows=, rvs_rows=, ledger_rows_after=, gap_accounts_before=, gap_accounts_after=。

使用 INSERT ... SELECT，把第 4、5、6 步的查询结果直接写入总账；在 txn_id 前添加 FIX-/RVS-，并让 RVS 反转交易类型。使用 INSERT OR IGNORE，确保运行两次也安全。最后执行 UPDATE account SET stored_balance = open_balance + (总账合计)。gap_accounts_before 从第 2 步的文件中统计。

撰写核对报告

在 /root/bank/recon_report.md 中撰写报告，包含 ## Reconciliation result（核对结果）、## Transactions only in the channel（仅存在于渠道的交易）、## Timeouts and double debits（超时与重复扣款）、## Correction method（更正方法）、## Preventing recurrence（防止复发）五个章节。

把 task 中的五个章节标题（反引号中的英文标题，或括号中意思相同的中文标题；韩文、日文标题也可以）分别写成以 ## 开头的一行。在核对结果中写入第 3 步的数字；在仅存在于渠道的交易中写入 CH0301 等引用号；在超时章节中写入全部第一笔交易的引用号；在更正方法中写入 FIX-/RVS-；在防止复发中写入幂等键（idempotency key）。