對帳切點之間新開的錢包被判 mismatch：沒有前一個快照時，NULL 該補 0 還是跳過

範例的對帳 job 每到切點，會把前一切點的餘額快照加上區間帳務異動，再和本次快照比較。我做過的錢包服務曾經誤判切點之間新開、沒有前一快照的錢包，修法是 coalesce。下文另寫 SQL 重現這類 mismatch，原查詢沒有收錄。新錢包沒有前一快照，LEFT JOIN 拿到 NULL，相加之後仍是 NULL，這份資料的加總結果因此變成 mismatch；兩個加數都用 coalesce 補 0，就回到 ok。

下面用 PostgreSQL 16.15 比較逐錢包比對的兩種寫法：沒有處理的 NULL 會漏查新錢包的差額，補 0 就查得出來。補值要符合前一切點的餘額假設，區間帳務也要完整，缺少快照的原因還得另外確認。

對帳怎麼算

那個服務在每個切點，會為每組（營運商、幣別）留一筆結果。範例用 ok 或 mismatch 表示結果，先逐錢包算出預期餘額：

expected = 前一切點快照餘額 + (前一切點, 這次切點] 之間的帳務異動加總
actual   = 這次切點快照餘額

再按營運商、幣別，把 expected 與 actual 加總後比較。個別錢包的正負差額可能抵銷，總額相等也不能證明每個錢包都相符。

用來重現的資料有三個錢包，都屬於 op-a、幣別 TWD。切點 1 在 2026-11-09 00:00，切點 2 在 01:00。w1 早就存在，這段期間有兩筆異動；w2 早就存在，這段期間沒有異動；w3 在 00:30 才開，所以沒有切點 1 的快照。

CREATE TABLE cuts (id int PRIMARY KEY, cut_at timestamptz NOT NULL);
CREATE TABLE wallets (id text PRIMARY KEY, operator text NOT NULL, currency text NOT NULL, created_at timestamptz NOT NULL);
CREATE TABLE balance_snapshots (cut_id int REFERENCES cuts, wallet_id text REFERENCES wallets, balance numeric NOT NULL, PRIMARY KEY (cut_id, wallet_id));
CREATE TABLE ledger (wallet_id text REFERENCES wallets, amount numeric NOT NULL, created_at timestamptz NOT NULL);

INSERT INTO cuts VALUES (1, '2026-11-09 00:00+08'), (2, '2026-11-09 01:00+08');
INSERT INTO wallets VALUES
  ('w1', 'op-a', 'TWD', '2026-11-01 12:00+08'),  -- 切點前就有，這段時間有交易
  ('w2', 'op-a', 'TWD', '2026-11-01 12:00+08'),  -- 切點前就有，這段時間沒交易
  ('w3', 'op-a', 'TWD', '2026-11-09 00:30+08');  -- 兩個切點之間新開
INSERT INTO balance_snapshots VALUES
  (1, 'w1', 100), (1, 'w2', 50),
  (2, 'w1', 120), (2, 'w2', 50), (2, 'w3', 180);
INSERT INTO ledger VALUES
  ('w1', -30, '2026-11-09 00:10+08'), ('w1', 50, '2026-11-09 00:40+08'),
  ('w3', 200, '2026-11-09 00:31+08'), ('w3', -20, '2026-11-09 00:45+08');

照範例的假設，三個錢包的餘額都能對上：w1 是 100 - 30 + 50 = 120，w2 維持 50，w3 從 0 開始，200 - 20 = 180。

直接相加：帳對，結果卻是 mismatch

第一版直接用 LEFT JOIN 接上前一快照和異動加總：

-- 版本 1：前一快照與區間異動都直接相加
WITH delta AS (
  SELECT l.wallet_id, sum(l.amount) AS amount
  FROM ledger l
  WHERE l.created_at >  (SELECT cut_at FROM cuts WHERE id = 1)
    AND l.created_at <= (SELECT cut_at FROM cuts WHERE id = 2)
  GROUP BY l.wallet_id
), per_wallet AS (
  SELECT w.operator, w.currency, w.id,
         prev.balance + d.amount AS expected,
         cur.balance             AS actual
  FROM wallets w
  JOIN balance_snapshots cur       ON cur.wallet_id = w.id AND cur.cut_id = 2
  LEFT JOIN balance_snapshots prev ON prev.wallet_id = w.id AND prev.cut_id = 1
  LEFT JOIN delta d                ON d.wallet_id = w.id
)
SELECT operator, currency, sum(expected) AS expected, sum(actual) AS actual,
       CASE WHEN sum(expected) = sum(actual) THEN 'ok' ELSE 'mismatch' END AS status
FROM per_wallet GROUP BY operator, currency;

 operator | currency | expected | actual |  status  
----------+----------+----------+--------+----------
 op-a     | TWD      |      120 |    350 | mismatch
(1 row)

expected 只剩 120。沿用相同的連接與區間條件，逐錢包查看：

-- 逐個錢包看 expected 是怎麼算出來的
SELECT w.id, prev.balance AS prev, d.amount AS delta,
       prev.balance + d.amount AS expected, cur.balance AS actual
FROM wallets w
JOIN balance_snapshots cur       ON cur.wallet_id = w.id AND cur.cut_id = 2
LEFT JOIN balance_snapshots prev ON prev.wallet_id = w.id AND prev.cut_id = 1
LEFT JOIN (
  SELECT wallet_id, sum(amount) AS amount FROM ledger
  WHERE created_at >  (SELECT cut_at FROM cuts WHERE id = 1)
    AND created_at <= (SELECT cut_at FROM cuts WHERE id = 2)
  GROUP BY wallet_id
) d ON d.wallet_id = w.id
ORDER BY w.id;

 id | prev | delta | expected | actual 
----+------+-------+----------+--------
 w1 |  100 |    20 |      120 |    120
 w2 |   50 |       |          |     50
 w3 |      |   180 |          |    180
(3 rows)

w3 沒有切點 1 的快照，prev 是 NULL；w2 沒有區間異動，delta 子查詢沒有它那一列，LEFT JOIN 也得到 NULL。NULL 加任何數字都還是 NULL，兩個錢包的 expected 因此都是 NULL。sum 只加非 NULL 的值，expected 只剩 w1 的 120，actual 是三個錢包的 350。

兩個 NULL 來源裡，我實際遇到的是新錢包缺少前一快照；無異動錢包的 NULL 來自範例資料。PostgreSQL 文件另外說明，部分聚合函式在沒有輸入列時會回傳 NULL，見延伸閱讀。這個例子是分組查詢沒有產生該錢包的列，再由外部連接得到 NULL。

補 0

兩個可能是 NULL 的加數，都用 coalesce 補 0：

-- 版本 2：兩個 NULL 都補 0
WITH delta AS (
  SELECT wallet_id, sum(amount) AS amount FROM ledger
  WHERE created_at >  (SELECT cut_at FROM cuts WHERE id = 1)
    AND created_at <= (SELECT cut_at FROM cuts WHERE id = 2)
  GROUP BY wallet_id
), per_wallet AS (
  SELECT w.operator, w.currency, w.id,
         coalesce(prev.balance, 0) + coalesce(d.amount, 0) AS expected,
         cur.balance AS actual
  FROM wallets w
  JOIN balance_snapshots cur       ON cur.wallet_id = w.id AND cur.cut_id = 2
  LEFT JOIN balance_snapshots prev ON prev.wallet_id = w.id AND prev.cut_id = 1
  LEFT JOIN delta d                ON d.wallet_id = w.id
)
SELECT operator, currency, sum(expected) AS expected, sum(actual) AS actual,
       CASE WHEN sum(expected) = sum(actual) THEN 'ok' ELSE 'mismatch' END AS status
FROM per_wallet GROUP BY operator, currency;

 operator | currency | expected | actual | status 
----------+----------+----------+--------+--------
 op-a     | TWD      |      350 |    350 | ok
(1 row)

缺少前一快照時補 0，前提是錢包在前一切點尚未存在，當時餘額可以視為 0，之後的餘額變化也都記在區間帳務裡。缺少快照還不足以確認這些條件，無區間異動的缺值也要確認帳務完整，才能視為沒有餘額變化。

逐錢包比對時，NULL 會讓差額漏查

未補值的 expected 若是 NULL，逐錢包用 WHERE expected <> actual 查差額，就會漏掉該列：一般比較運算子遇到 NULL 時會回傳 NULL，WHERE 只留結果為 true 的列。下面另外設一個欄位，放未補值的預期餘額，和補值結果比較。

把 w3 這次的快照改成 999，模擬一個帳對不上的新錢包，再比較兩種寫法：

-- 逐錢包比對：w3 的快照被改成 999，看兩種寫法各抓到什麼
BEGIN;
UPDATE balance_snapshots SET balance = 999 WHERE cut_id = 2 AND wallet_id = 'w3';
CREATE TEMP VIEW per_wallet AS
SELECT w.id,
       prev.balance + d.amount                             AS raw_expected,
       coalesce(prev.balance, 0) + coalesce(d.amount, 0)   AS expected,
       cur.balance                                         AS actual
FROM wallets w
JOIN balance_snapshots cur       ON cur.wallet_id = w.id AND cur.cut_id = 2
LEFT JOIN balance_snapshots prev ON prev.wallet_id = w.id AND prev.cut_id = 1
LEFT JOIN (
  SELECT wallet_id, sum(amount) AS amount FROM ledger
  WHERE created_at >  (SELECT cut_at FROM cuts WHERE id = 1)
    AND created_at <= (SELECT cut_at FROM cuts WHERE id = 2)
  GROUP BY wallet_id
) d ON d.wallet_id = w.id;
SELECT 'raw <>' AS rule, id FROM per_wallet WHERE raw_expected <> actual
UNION ALL
SELECT 'coalesce IS DISTINCT FROM', id FROM per_wallet WHERE expected IS DISTINCT FROM actual;
ROLLBACK;

           rule            | id 
---------------------------+----
 coalesce IS DISTINCT FROM | w3
(1 row)

沒補 0 的 <> 查不到 w3 快照多出的 819 元。若後續對帳只看各自區間的異動，又沒有重跑或其他核對，新錢包的第一段帳，例如首筆入金，就可能漏查。

補 0 後再用 IS DISTINCT FROM 比較，就查得到 w3。補 0 之後兩個加數都不是 NULL，本次快照也不允許 NULL，<> 和 IS DISTINCT FROM 的結果相同；保留 IS DISTINCT FROM，日後 actual 若允許 NULL，仍能比較（範例未示範）。JOIN 會排除缺少本次快照的錢包，換比較方式無法找回這些列；範例沒有測試本次快照缺漏。

前一快照漏了，和切點後才開，是兩回事

補 0 的前提是錢包在前一切點尚未存在。早已存在、卻缺快照的錢包，補 0 等於假設當時餘額是 0。刪掉 w2 的切點 1 快照再跑：

-- 前一快照漏了 w2（錢包早就存在），再跑一次補 0 的版本，並標出前一快照的來源
BEGIN;
DELETE FROM balance_snapshots WHERE cut_id = 1 AND wallet_id = 'w2';
SELECT w.id,
       CASE
         WHEN prev.wallet_id IS NOT NULL THEN 'has_prev'
         WHEN w.created_at > c1.cut_at   THEN 'opened_after_cut'
         ELSE 'snapshot_missing'
       END AS prev_source,
       coalesce(prev.balance, 0) + coalesce(d.amount, 0) AS expected,
       cur.balance AS actual
FROM wallets w
CROSS JOIN (SELECT cut_at FROM cuts WHERE id = 1) c1
JOIN balance_snapshots cur       ON cur.wallet_id = w.id AND cur.cut_id = 2
LEFT JOIN balance_snapshots prev ON prev.wallet_id = w.id AND prev.cut_id = 1
LEFT JOIN (
  SELECT wallet_id, sum(amount) AS amount FROM ledger
  WHERE created_at >  (SELECT cut_at FROM cuts WHERE id = 1)
    AND created_at <= (SELECT cut_at FROM cuts WHERE id = 2)
  GROUP BY wallet_id
) d ON d.wallet_id = w.id
ORDER BY w.id;
ROLLBACK;

 id |   prev_source    | expected | actual 
----+------------------+----------+--------
 w1 | has_prev         |      120 |    120
 w2 | snapshot_missing |        0 |     50
 w3 | opened_after_cut |      180 |    180
(3 rows)

只補 0 時，w2 仍有差額；按表中金額推算，沿用前面的加總查詢會得到 mismatch。若補值後金額剛好相等，缺快照也可能沒有差額，仍要追查 snapshot_missing。範例刻意刪掉 w2 的快照，實際系統就要查明缺漏原因。用 created_at 和前一切點比較，可以區分切點後才開的錢包，以及早已存在、卻缺快照的錢包；前者仍要符合補值前提，後者應另外標示，方便查核。

這需要 created_at 可信，而且能和切點時間直接比較。缺少前一快照時，只有建立時間晚於前一切點，才歸為切點後開戶；時間相同，則歸為快照缺漏。服務之間的時鐘落差，可能讓切點附近建立的錢包分錯邊。

查詢只加上來源標記，缺值仍全部補 0，標記之後的處理沒有示範。

範例沒有測試個別錢包差額抵銷、切點邊界與時鐘落差。無異動錢包這個 NULL 來源是範例資料帶出來的，本文沒有核對原本的 job 是否也有；逐錢包查詢漏掉的列，要靠系統裡另外的核對才補得回來。

補充筆記

範例以前一快照加上區間異動來對帳，新錢包缺少前一快照，LEFT JOIN 得到 NULL，相加之後 expected 仍是 NULL。
sum 只加非 NULL 的值，expected 因此少算，餘額相符，仍會判成 mismatch；無區間異動的錢包也會產生 NULL。
缺少前一快照時補 0，要確認錢包在前一切點不存在、餘額可以視為 0，後續變化也都記在區間帳務裡；範例補值後為 ok。
逐錢包用 <> 比對時，NULL 會讓有差額的新錢包漏查；補 0 再用 IS DISTINCT FROM，就查得到。
範例裡早已存在、卻缺快照的錢包，補 0 後仍有差額；若 created_at 可靠且能和切點比較，就可以另外標出快照缺漏，再查原因。