Jede Bestellsumme korrekt halten

Einführung

PAPER & PIER verkauft kleine Waren an zwei Marktständen. Vor Geschäftsschluss muss die Verkaufsstelle den Warenwert jeder Bestellung mit dem tatsächlich erhaltenen und erstatteten Geld abgleichen. Eine Bestellung kann mehrere Artikel und mehrere echte Zahlungen umfassen. Wer diese Rohdatenlisten unbedacht verknüpft, kann eine korrekt bezahlte Bestellung fälschlich als fehlerhaft erscheinen lassen.

Du erstellst drei wiederverwendbare SQLite-Views, die jede Art von Fakten separat aufsummieren. Du benötigst grundlegende Kenntnisse der Navigation im Terminal und von SELECT/WHERE in SQL. Aggregationen, Joins, Views und fehlende Werte werden hier erklärt. SQLite und ein synthetisches Geschäftsbuch sind vorbereitet; verwende durchgehend Terminal.

Bewahre alle ursprünglichen Tabellen, IDs und Werte. Du darfst nur die Views item_totals, payment_totals und refund_totals erstellen oder ersetzen und ihre Definitionen in totals.sql speichern. Beträge sind ganzzahlige Centwerte; dies ist ein vereinfachter Abgleich von Einnahmen, ohne Steuern, Zahlungsdienstleister-Abrechnung oder Währungsumrechnung.

Eine Artikelsumme pro Bestellung beibehalten

In diesem Schritt liest du eine normale Bestellung, beobachtest die Verdopplung von Geldbeträgen durch einen Join der Rohdaten und speicherst eine korrekte View der Artikelsummen. Beginne in Terminal. cd wechselt das Verzeichnis; dieser absolute Pfad hilft dir auch, wenn du in ein falsches Verzeichnis gewechselt bist:

cd /home/labex/project/sales-desk

sqlite3 öffnet die benannte Datenbank. -header gibt Spaltennamen aus, -column richtet Werte aus, und das SQL in Anführungszeichen wird ausgeführt, bevor sich die Verbindung schließt. Die Anführungszeichen halten die Leerzeichen und Semikolons des SQL in einem Shell-Argument zusammen. Lies die vorbereiteten Bestellidentitäten:

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

Es gibt vier Bestellungen. A1 und A2 sind unterschiedliche Bestellungen, obwohl beide Kundenbezeichnungen Alex Kim lauten. Gruppiere nach der Bestell-ID, niemals nach dem Kundennamen. Lies die Artikelzeilen und Zahlungsereignisse von A1:

sqlite3 -header -column ledger.db "SELECT * FROM items WHERE order_id = 'A1'; SELECT * FROM payments WHERE order_id = 'A1';"

Die Artikel kosten 300 + 500 = 800 Cent. Die Zahlungen P1 und P2 sind zwei unterschiedliche erfolgreiche Ereignisse über je 400 Cent, zusammen ebenfalls 800. P3 ist fehlgeschlagen und hat kein Geld beigetragen. succeeded bedeutet, dass das Ereignis in der Berechnung dieses Projekts zählt; pending und failed zählen nicht.

Ein Join kombiniert zusammengehörige Zeilen. AS weist einen kurzen Tabellenalias zu: i steht für Artikel und p für Zahlungen. ON legt übereinstimmende Bestell-IDs fest. Sieh dir vor jeder Summierung jedes passende Paar für A1 an:

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

Es gibt vier Zeilen: Jeder der beiden Artikel passt zu beiden Zahlungen. Dies ist das tatsächliche Verhalten eines Joins. Es bedeutet nicht, dass vier Artikel oder vier Zahlungen vorliegen. SUM addiert den Ausdruck über seine Eingabezeilen. Bei dieser vervielfachten Eingabe sind beide Summen falsch:

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

Beide angezeigten Summen sind 1600, das Doppelte des tatsächlichen Betrags. DISTINCT behält unterschiedliche Werte, nicht unterschiedliche Zahlungsidentitäten. Es kann dieses Problem nicht beheben:

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

Das Ergebnis ist 400; eine zulässige Zahlung mit gleichem Betrag wird verworfen. Summiere stattdessen jede Faktenliste für sich. GROUP BY order_id erzeugt eine Ergebniszeile für jede Bestell-ID. quantity * unit_cents berechnet den Wert jedes Artikels vor der Addition. Die resultierende Granularität, also das, was eine Ausgabezeile darstellt, ist eine Bestellung.

Eine View ist ein benanntes SELECT, das in der Datenbank gespeichert ist. Beim Lesen wird dieses SELECT gegen die aktuellen Quellzeilen ausgeführt; die Artikel werden weder kopiert noch ersetzt. Speichere ihre Definition in totals.sql. In der Shell schreibt cat das folgende wörtliche Here-Dokument in die Datei; > ersetzt ihren alten Inhalt. Füge das abschließende SQL allein in einer Zeile ein. Dieser Block gehört an die Shell-Eingabeaufforderung, nicht in eine interaktive SQLite-Eingabeaufforderung:

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 entfernt nur eine frühere Definition dieser View, falls vorhanden. Quelltabellen und Daten bleiben intakt. CREATE VIEW speichert die neue Definition. < liefert die gespeicherte Datei als Eingabe an SQLite:

sqlite3 ledger.db < totals.sql

Bei Erfolg wird keine Ausgabe erwartet. Lies die tatsächliche View:

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

Erwartet werden A1 = 800, A2 = 250, A3 = 500 und A4 = 200 Cent, jeweils in einer Zeile. Die Menge von zwei bei A3 ist berücksichtigt. Du hast nun ein nützliches erstes Arbeitsergebnis: eine gespeicherte View-Definition, die auf einem weiteren kompatiblen Geschäftsbuch ausgeführt werden kann.

Fehlt eine Datei, listet ls die Dateien dieses Verzeichnisses auf. Prüfe das absolute cd und die Schreibweise totals.sql. Ein SQL-Syntaxfehler benennt die fehlerhafte Anweisung; wiederhole den vollständigen Erstellungsblock, um eine Datei mit Tippfehlern zu ersetzen, und wende ihn erneut an. Wartet die Shell nach dem Here-Dokument weiterhin, gib die fehlende abschließende Zeile SQL ein. Lösche zur Fehlerbehebung nicht das Geschäftsbuch.

Unabhängige Geldsummen zusammenführen

In diesem Schritt ergänzt du das gespeicherte Skript um Summen erfolgreicher Zahlungen und Erstattungen. Anschließend kombinierst du die drei Fakten mit jeweils einer Zeile pro Bestellung und behältst dabei unbezahlte Bestellungen bei. Beginne in Terminal unter /home/labex/project/sales-desk.

Für Zahlungen und Erstattungen gilt dieselbe Aggregationsregel: Jedes echte Ereignis zählt, auch gleiche Beträge mit unterschiedlichen IDs. Filtere status = 'succeeded' innerhalb des SELECT der jeweiligen Fakten vor der Gruppierung. Eine ausstehende Erstattung darf das erhaltene Geld nicht verringern. Lies die Erstattungsbelege:

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

A3 hat zwei separate erfolgreiche Erstattungen über je 50 Cent und eine ausstehende Erstattung über 250 Cent. Die tatsächliche Erstattungssumme ist 100, nicht 50 und nicht 350.

Hänge die beiden anderen View-Definitionen an. >> ergänzt das Ende einer vorhandenen Datei, statt die Artikel-View zu ersetzen. Wenn du dieses Anhängen zur Fehlerbehebung wiederholst, erstelle zuerst totals.sql mit dem vollständigen Block des vorherigen Schritts neu und hänge diesen Block anschließend einmal an:

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

Das gespeicherte Skript kann erneut angewendet werden: Jede Definition wird entfernt und aus unveränderten Quellfakten neu erstellt. Lies die Geld-Views:

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

Die Zahlungen enthalten A1 = 800, A3 = 600 und A4 = 250. Die Erstattungen enthalten nur A3 = 100. A2 fehlt in beiden, weil keine berücksichtigungsfähige Zahlung oder Erstattung existiert. Das Fehlen in einer Faktensumme darf die Bestellung nicht aus dem Abgleich entfernen.

LEFT JOIN behält jede Zeile seiner linken Seite bei, auch wenn rechts keine passende Zeile existiert. Beginne mit orders und verknüpfe dann jede bereits aggregierte View anhand der Bestell-ID. Eine fehlende Übereinstimmung liefert SQL NULL, also keinen Wert, statt null. COALESCE(value, 0) setzt nur dann null ein, wenn diese Summe NULL ist. Bedingungen für den Ereignisstatus gehören bereits in die Geld-Views; eine zusätzliche WHERE-Bedingung auf einer fehlenden verknüpften Zeile könnte die unbezahlte Bestellung verwerfen.

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

Vergleiche jede Zeile mit dem normalen Geschäftsvorgang:

Jede Fakten-View hat vor den Joins höchstens eine Zeile pro Bestellung, sodass eine Faktenart keine andere vervielfachen kann. Bewahre die ursprünglichen vier Tabellen und alle ihre Datensätze; die drei Views und totals.sql sind die einzigen Datenbankdefinitionen und die einzige Datei, die du hinzufügen musst. Das nächste Lab beginnt mit seinem eigenen frischen Geschäftsbuch und überführt diese Überlegungen in eine vollständige Abgleichsabfrage.

Zusammenfassung

Du hast zwei Fallstricke bei Geldsummen aufgedeckt, wiederverwendbare Fakten-Views mit einer Zeile pro Bestellung gespeichert und sie mit LEFT JOIN und COALESCE kombiniert. Separate Ereignisse mit gleichem Betrag blieben echte Ereignisse, und unbezahlte Bestellungen blieben sichtbar.