各注文の合計を正しく保つ

はじめに

PAPER & PIER は、市場の 2 つの売り場で雑貨を販売しています。閉店前に、各注文の商品金額と、実際に受け取った金額および返金額を照合する必要があります。1 つの注文には複数の商品や実際の支払いが含まれることがあります。それぞれの未集計の一覧を不用意に結合すると、正しく支払われた注文が誤って見えることがあります。

種類ごとにデータを別々に集計する、再利用可能な SQLite ビューを 3 つ作ります。基本的なターミナル操作と SQL の SELECT/WHERE の知識が必要です。集計、結合、ビュー、値の欠落はここで説明します。SQLite と架空の台帳は準備済みです。全編を通して Terminal を使います。

元のテーブル、ID、値をすべて保全してください。作成・置換できるのは item_totals、payment_totals、refund_totals の各ビューだけで、定義は totals.sql に保存します。金額は整数のセントです。これは簡略化した入金照合であり、税金、決済事業者による精算、通貨換算は扱いません。

注文ごとに商品合計を 1 つに保つ

このステップでは、通常の注文を読み取り、未集計の行の結合で金額が重複する仕組みを確かめ、正確な商品合計のビューを保存します。Terminal から始めます。cd はディレクトリを移動します。この絶対パスは、誤ったディレクトリに入った場合の復帰にも使えます。

cd /home/labex/project/sales-desk

sqlite3 は指定したデータベースを開きます。-header は列名を表示し、-column は値を整列させます。引用符内の SQL を実行した後、接続を閉じます。引用符により、SQL の空白とセミコロンを 1 つのシェル引数にまとめます。準備済みの注文の識別情報を読み取ります。

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

注文は 4 件です。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;"

4 行あります。2 つの商品が、それぞれ両方の支払いに一致します。これは実際の結合の動作です。商品や支払いが 4 件発生したという意味ではありません。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 で、実際の金額の 2 倍です。DISTINCT が区別するのは値であり、支払いの識別情報ではありません。これでは問題を解決できません。

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

結果は 400 となり、同額の正当な支払いが 1 件落ちます。代わりに、種類ごとのデータ一覧をそれぞれ単独で集計します。GROUP BY order_id は注文 ID ごとに結果を 1 行にします。quantity * unit_cents は、足し合わせる前に各商品明細の金額を計算します。出力の 1 行が何を表すかという「集計単位」は、ここでは 1 つの注文です。

ビューは、名前を付けてデータベースに保存した SELECT です。読み取ると、現在の元データの行に対してその SELECT が実行されます。商品をコピーしたり置き換えたりするものではありません。定義を totals.sql に保存します。シェルでは、cat が以下のヒアドキュメントをそのままファイルに書き込み、> が以前の内容を置き換えます。最後の SQL は単独行で貼り付けてください。このブロックはシェルのプロンプトで実行し、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 セントが、それぞれ 1 行になるはずです。A3 の数量 2 も反映されます。これで、互換性のある別の台帳でも実行できる保存済みのビュー定義という、最初の有用な成果ができました。

ファイルが見つからなければ、ls でこのディレクトリのファイルを一覧表示できます。絶対パスの cd と、totals.sql のつづりを確認してください。SQL の構文エラーでは、失敗した文が示されます。完全な作成ブロックを繰り返して入力ミスのあるファイルを置き換え、再適用します。ヒアドキュメントの後もシェルが入力待ちなら、不足している最後の SQL 行を入力します。復旧のために台帳を削除しないでください。

個別に求めた金額合計を組み合わせる

このステップでは、保存したスクリプトに成功した支払いと返金の合計を追加し、未払い注文を残したまま、注文ごとに 1 行の 3 種類のデータを組み合わせます。Terminal の /home/labex/project/sales-desk から始めます。

支払いと返金にも同じ集計ルールを適用します。ID が異なる同額のイベントも含め、実際の各イベントを数えます。グループ化する前に、各種類のデータの SELECT 内で status = 'succeeded' を絞り込みます。処理待ちの返金で受取金額を減らしてはいけません。根拠となる返金データを読み取ります。

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

A3 には、成功した別々の 50 セントの返金が 2 件と、処理待ちの 250 セントの返金があります。実際の返金合計は 100 であり、50 でも 350 でもありません。

残りの 2 つのビュー定義を追記します。>> は商品ビューを置き換えずに、既存ファイルの末尾へ追加します。誤りからの復旧でこの追記を繰り返す場合は、まず前のステップの完全なブロックで totals.sql を作り直し、その後にこのブロックを 1 回だけ追記します。

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;"

各行を通常の業務と対応させて読み取ります。

結合する前に、各種類のデータのビューは注文ごとに最大 1 行となるため、ある種類のデータが別の種類の行を増殖させることはありません。元の 4 テーブルと全レコードを保全してください。追加が必要なデータベース定義とファイルは、3 つのビューと totals.sql だけです。次の実習では、独立した新しい台帳から始め、この考え方を完全な照合クエリにまとめます。

まとめ

金額集計に潜む 2 つの落とし穴を確かめ、注文ごとに 1 行となる再利用可能なデータ集計ビューを保存し、LEFT JOIN と COALESCE で組み合わせました。同額でも別々のイベントは実際のイベントとして残り、未払いの注文も表示され続けました。