売上照合の引き継ぎ資料を作成する

はじめに

PAPER & PIER では、注文ごとに 1 行の CSV と、売り場ごとの集計を閉店時に引き継ぐ必要があります。両売り場の差額合計がゼロでも、個々の注文には支払い不足や過払いが残る場合があります。総合計 1 つだけで成功と判断せず、引き継ぎ資料にそれらの例外を残す必要があります。

再利用可能な SQL クエリを保存し、提供されたエクスポーターで、実際の結果から本物の CSV ファイルを作ります。先に「各注文の合計を正しく保つ」を完了するか、SUM/GROUP BY、LEFT JOIN、COALESCE と結合による重複の問題を理解している必要があります。共通テーブル式と状態の分類はここで説明します。全編を通して Terminal を使います。この単元には、6 件の注文を含む独立した台帳があります。

元の 4 テーブルと提供されたツールをすべて変更せずに保全してください。変更できるのは query.sql と reports/ 内のファイルだけです。データベースオブジェクトは追加しません。金額はすべて整数のセントです。これは簡略化した入金照合で、税金や決済事業者による精算は扱いません。CSV のエンコードと集計計算はエクスポーターが処理するため、Python を書く必要はありません。

再利用可能な注文別クエリを保存する

このステップでは、1 つの自己完結した SQL 文で、各注文の正味入金額、差額、状態を計算します。Terminal から始めます。

cd /home/labex/project/sales-desk

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

この独立した台帳には A1～A6 があり、顧客ラベルが Alex Kim の注文が 2 件と、カンマや引用符を含むラベルがあります。注文は ID で区別します。ラベルは保存されているとおりに保全してください。残高を求める前に、種類ごとのデータ一覧をすべて確認します。

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

これまでと同じく、支払いと返金は succeeded だけを数えます。同額でも、成功した 2 つのイベントは両方とも含めます。対象となるデータが欠けている場合はゼロとします。計算は次のとおりです。

共通テーブル式（CTE）は、1 つの文の内部だけで使える、名前付きの中間 SELECT です。WITH name AS (...) で導入し、複数の CTE はカンマで区切ります。データベースオブジェクトを作らず、他の実習のファイルにも依存せずに、先ほどのビューと同じ、種類ごとに別々に集計する考え方を実現します。

まず 3 つの CTE が、種類ごとのデータを独立に集計します。次に totals が注文から始め、欠けた合計をゼロで埋めます。balances は、名前を付けたそれらの合計から正味入金額と差額を計算します。最後の SELECT は CASE WHEN ... THEN ... ELSE ... END で差額の符号から状態を選びます。このように段階を分けることで、同じ SELECT リスト内の別の式の別名に依存せず、後続の SELECT で別名を使えます。中間の残高計算段階の SELECT * は合計列を引き継ぎ、最後の SELECT は、指定された順番でちょうど 9 列を明示します。

文全体を保存します。cat > はファイルを置き換え、引用符付きのヒアドキュメントは SQL をそのまま保持します。最後の SQL 行も含めてください。

cat > query.sql <<'SQL'
WITH
item_totals AS (
  SELECT order_id, SUM(quantity * unit_cents) AS due_cents
  FROM items GROUP BY order_id
),
payment_totals AS (
  SELECT order_id, SUM(amount_cents) AS paid_cents
  FROM payments WHERE status = 'succeeded' GROUP BY order_id
),
refund_totals AS (
  SELECT order_id, SUM(amount_cents) AS refunded_cents
  FROM refunds WHERE status = 'succeeded' GROUP BY order_id
),
totals AS (
  SELECT o.order_id, o.customer, o.stall,
         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
),
balances AS (
  SELECT *, paid_cents - refunded_cents AS net_cents,
         paid_cents - refunded_cents - due_cents AS difference_cents
  FROM totals
)
SELECT order_id, customer, stall, due_cents, paid_cents,
       refunded_cents, net_cents, difference_cents,
       CASE WHEN difference_cents = 0 THEN 'settled'
            WHEN difference_cents < 0 THEN 'underpaid'
            ELSE 'overpaid' END AS status
FROM balances
ORDER BY order_id;
SQL

query.sql に SQLite のドットコマンドやシェルコマンドを含めないでください。エクスポーターは、通常の SELECT/WITH 文を 1 つ実行します。この文は、同じスキーマを持つ別の台帳でも、異なる注文 ID や金額を含む 4 つの元テーブルから実行できる必要があります。結果行のハードコードやデータベースビューでは、そのような再利用可能なクエリにはなりません。

< でこのファイルを SQLite CLI に渡し、最初の結果を読みやすく表示します。

sqlite3 -header -column ledger.db < query.sql

合計だけではなく、実際の各行を読んでください。期待される残高は次のとおりです。

A2 は支払いがなくても表示されます。A3 では成功した 50 セントの返金が両方とも残ります。A6 の失敗した返金は正味入金額を減らしません。A1 と A2 は顧客ラベルが同じでも別の注文のままです。セントを表す 5 列は、負の差額も含めて実際の整数です。通貨形式の文字列に変換しないでください。

構文エラーでは、報告された位置を読み、修正した完全なブロックで query.sql を置き換えてから再実行します。パスを間違えたら、上記の絶対パスの cd で戻り、ls で query.sql を探します。台帳を空にしたり、強制的に一致させるために金額を変えたり、結果を保存するテーブルを追加したりしないでください。

実際の CSV ファイルを作成して読み取る

このステップでは、保存したクエリをエクスポートし、CSV の引用符が必要なラベルも含め、実際の引き継ぎファイルを読み取ります。Terminal の /home/labex/project/sales-desk から始めます。

./export は現在のディレクトリにある提供済みの実行ファイルです。query.sql を読み取り、その文を変更せずに読み取り専用の接続で ledger.db に対して実行し、実際に返された行を reports/orders.csv に書き込みます。reports/summary.csv は、その返された行だけから作ります。誤ったクエリを修復したり、欠けた注文を補ったりはしません。両ファイルの保存先は準備済みの reports ディレクトリです。

./export

メッセージには、クエリ結果が 6 行であることと、両出力ファイルの名前が示されます。この後のリーダーは、売り場ごとの集計を 2 件表示します。エクスポートの成功はファイルが書き込まれたことを意味し、計算が正しいことを意味しません。クエリがない、または不正な場合、エクスポーターは失敗を報告します。以前の成功したエクスポートの古いファイルが残っていることもあります。最新のものとして扱う前に、保存したクエリを修正し、./export を再実行してください。

./review は提供済みの CSV リーダーです。データベースを参照せずに保存済みファイルを読み、解析した各行をラベル付きのレコードとして表示します。出力の欠落や改変は、そのまま欠落・改変として現れます。

./review

6 件の注文レコードは前のステップの表と一致し、顧客と売り場のラベルは元の文字列と完全に同じになるはずです。集計の列は次のとおりです。

North, Pier には注文が 3 件あり、商品金額 1650、正味入金額 1650、差額 0 で、各状態が 1 件ずつです。South も 3 件で、商品金額 1100、正味入金額 1100、差額 0、各状態が 1 件ずつです。全体の差額はゼロでも、両売り場を合わせると 4 件の注文で支払いが一致していません。過払いと支払い不足は合計では相殺されますが、別の人の注文の支払いを済ませたことにはなりません。

CSV はカンマ区切りの値を意味しますが、ラベル内のカンマは 1 つのフィールドの一部です。標準の CSV は、そのようなフィールドを二重引用符で囲み、フィールド内の引用符を 2 つの引用符で表します。cat はファイルの生のバイトをテキストとして表示します。その表現を、./review が正しく解析したラベルと比べてください。

cat reports/orders.csv

cat reports/summary.csv

注文ファイルでは、顧客ラベル Paper, "Pine" は "Paper, ""Pine"""、North, Pier は "North, Pier" としてエンコードされます。追加された引用符は CSV の構文であり、ラベルの変更ではありません。行をすべてのカンマで分割すると、これらのレコードは壊れます。提供された書き込み・読み取りツールが、この形式を処理します。

CSV が見つからなければ、query.sql を修正してから ./export を再実行し、両ファイルをもう一度読み取ります。注文の欠落を隠すために集計を手で編集したり、エクスポーターやリーダーを置き換えたり、元のレコードを変えたりしないでください。引き継ぐのは、再利用可能な query.sql と、実際の 2 つの CSV ファイルです。ファイルには、すべての元の注文について 1 行、指定どおりの注文の 9 列、上記の集計の 8 列が含まれる必要があります。行の順序が業務上の成果なのではありません。重要なのは識別情報、値、ラベルと、それらの関係です。

まとめ

再利用可能な CTE クエリを作り、正味入金額から商品金額を引いて各注文を分類し、実際の CSV ファイルを 2 つ用意しました。正しく引用符で囲まれたラベルを読み取り、売り場の合計が一致することと、個々の注文の支払いが一致することを区別しました。