4件の売上を、初回・訂正版・古い版の順に取り込みました。SQLiteに残ったのは4件、合計10万5,000円。古い版を最後に渡しても、訂正した金額は戻りません。
僕は営業週報の自動化なら、やり直しても数字が崩れない作りを先に入れたいです。訂正版のあとで最初のファイルを取り込んでも、使える集計にします。
数字は架空データの実行結果です。実案件の削減実績ではありません。
SQLiteのUPSERTに版番号を足す
使う列は売上明細のID、円単位の金額、版番号。同じ明細ならIDを固定し、訂正時に元データの版番号を増やします。会社名をIDにすると、同じ会社への別の売上まで上書きしてしまいます。
SQLiteのUPSERTなら、初めてのIDは追加し、既存のIDは更新できます。ただ、これだけで安心すると詰まります。
今日、手元で更新条件を外して試すと、訂正版で10万5,000円になった合計が、古い版の再取込で10万円に戻りました。件数はずっと4件。あ、件数だけ見ても気づけない。
そこで「保存済みより版番号が大きいときだけ更新する」条件を加えます。
明細IDで追加か更新かを分け、版番号で上書きしてよいかを決めます。
4件を3回取り込むコード
Python 3.14.6／SQLite 3.53.4で実行済み。追加パッケージは不要です。下をweekly_sales.pyに保存します。DBはメモリ上に作ります。
import sqlite3
db = sqlite3.connect(":memory:")
db.execute("""
CREATE TABLE sales (
id TEXT PRIMARY KEY NOT NULL,
yen INTEGER NOT NULL,
revision INTEGER NOT NULL
)
""")
upsert = """
INSERT INTO sales VALUES (?, ?, ?)
ON CONFLICT(id) DO UPDATE SET
yen = excluded.yen,
revision = excluded.revision
WHERE excluded.revision > sales.revision
"""
original = [
("S01", 10000, 1), ("S02", 20000, 1),
("S03", 30000, 1), ("S04", 40000, 1),
]
corrected = [
("S01", 10000, 1), ("S02", 25000, 2),
("S03", 30000, 1), ("S04", 40000, 1),
]
for label, rows, expected in [
("初回", original, 100000),
("訂正版", corrected, 105000),
("古い版を再取込", original, 105000),
]:
with db:
db.executemany(upsert, rows)
count, total = db.execute(
"SELECT COUNT(*), SUM(yen) FROM sales"
).fetchone()
print(f"{label}: {count}件 / {total:,}円")
assert (count, total) == (4, expected)
db.close()
python3 weekly_sales.py
初回: 4件 / 100,000円
訂正版: 4件 / 105,000円
古い版を再取込: 4件 / 105,000円
excludedは、今回追加しようとした行を指します。S02の訂正版なら金額25,000円、版番号2。保存済みの版番号1より大きいので更新します。古い版の1を再び渡すと条件を満たさず、25,000円が残ります。同じ版の再取込も更新しません。
PRIMARY KEYだけでは、同じIDを追加したときにエラーになります。ON CONFLICTでその場合の更新を決め、WHEREで更新対象を絞っています。重複を飛ばすだけでは訂正額が入らないため、この組み合わせにしました。
版番号は、取り込む前から必要
今回の仕組みは、元データで版番号を管理できる場合に使えます。取込時刻を版番号の代わりにすると、あとから届いた古いファイルが勝ってしまいます。ファイル名の「最新版」も、比較には使っていません。あの文字、つい付けたくなるんですけどね。
同じ版番号で違う金額を送った場合は、先に保存した金額が残ります。金額を直したら版も増やす運用が必要です。削除や取消もこのコードの対象外。受け取った一覧にない明細は、そのまま残ります。
ここでは同じDBへの3回の取込を再現しました。終了するとDBは消えるので、実運用には保存先の用意が別途必要です。
週報を作り直すとき、明細をいったん全部消す手間が減る。ここ地味に効きます。
まずは手元の明細に、固定のIDと版番号があるか。あれば、この4件を自分の訂正パターンに置き換えられます。僕は、版を残す運用が定着すれば、集計担当に「どっちが新しい？」と聞く往復も減ると見ています。自分の週報なら、何を使って最新版を見分けるか。そこが決まると、この条件式を業務に持ち込めます。