Die Bäckerei, Etappe 6: der Bericht, die Kopie und DROP
Kurz gesagt: Die letzte Etappe baut einen Bericht mit Fensterfunktionen, kopiert die Datenbank mit VACUUM INTO und entfernt alles mit DROP in der richtigen Reihenfolge.
Problem
Der Monat ist vorbei
Der Oktober ist vorbei. Die Bäckerei will einen Bericht: wie der Umsatz Tag für Tag wächst, wer der beste Kunde ist und welcher Anteil des Geldes von ihm kommt. Dann will sie eine Kopie der Datenbank, bevor eine neue Saison beginnt. Und du nimmst, um den ganzen Weg zu sehen, die Datenbank Stück für Stück auseinander, bis sie leer ist wie am Anfang von Etappe 1.
Die Felder beginnen mit der Datenbank nach Etappe 5: die Daten, der Index, die View order_totals, der Trigger take_stock und die Spalte phone.
Die Idee
Die laufende Summe
Eine Fensterfunktion berechnet einen Wert aus mehreren Zeilen, ohne sie zu einer zusammenzufassen. SUM(t.total) OVER (ORDER BY o.ordered_at) ist die Summe aller Bestellungen bis einschließlich dieser Zeile:
So siehst du, wie der Monat auf 47 € wächst, ohne die stornierte Bestellung.
Probier es aus
Der Bericht
Ein Bericht pro Kunde, ohne die stornierten Bestellungen: der Name, der ausgegebene Betrag (spent), der Anteil am gesamten Umsatz in Prozent (share, auf 1 Stelle gerundet) und der Platz in der Rangliste (place, mit RANK). Sortiere nach Platz. Nimm die Summen aus der View order_totals.
Lösung
SELECT c.name,
SUM(t.total) AS spent,
ROUND(100.0 * SUM(t.total) / SUM(SUM(t.total)) OVER (), 1) AS share,
RANK() OVER (ORDER BY SUM(t.total) DESC) AS place
FROM order_totals t
JOIN customers c ON c.id = t.customer_id
WHERE t.status <> 'cancelled'
GROUP BY c.id
ORDER BY place;ROUND(100.0 * SUM(t.total) / SUM(SUM(t.total)) OVER (), 1) und RANK() OVER (ORDER BY SUM(t.total) DESC). Das innere SUM ist die Summe des Kunden, und das äußere mit OVER () ist die Gesamtsumme aller Kunden. Mit / SUM(t.total) hat jeder einen Anteil von 100. Ohne die Bedingung an den Status kommt Boris mit seiner stornierten Bestellung hinein.
Genauer, für Neugierige
RANK gibt Gleichen denselben Platz und überspringt den nächsten (1, 1, 3). DENSE_RANK überspringt nicht (1, 1, 2), und ROW_NUMBER vergibt auch Gleichen verschiedene Nummern.
Die Idee
Die Kopie
Vor einer großen Änderung (eine neue Saison, eine Migration, ein Datenimport) machst du eine Kopie. VACUUM INTO schreibt die ganze Datenbank im laufenden Betrieb in eine neue Datei, und ATTACH öffnet die Kopie als zweite Datenbank neben der ersten:
Die sechs Bestellungen und die fünf Tabellen sind auch in der Kopie.
Führst du das Feld ein zweites Mal aus, bricht VACUUM INTO ab: Die Datei gibt es schon. In der echten Arbeit ist es genauso: Jede Kopie ist eine neue Datei mit dem Datum im Namen.
Genauer, für Neugierige
In PostgreSQL wird die Kopie von außen gemacht: pg_dump bakery > bakery-2026-10-31.sql und mit psql wiederhergestellt. Eine Kopie, die niemand wiederherzustellen versucht hat, ist keine Kopie.
Sieh es dir an
Was wovon abhängt
Die View order_totals liest aus order_items. Geht die Tabelle zuerst:
Die View bleibt, zeigt aber jetzt ins Leere. Die Indizes und Trigger einer Tabelle verschwinden mit ihr, Views nicht. Darum entfernt man die Views zuerst. PostgreSQL ist strenger: Dort bricht DROP TABLE ab, solange eine View aus der Tabelle liest.
Probier es aus
Die View, der Trigger und der Index
Entferne die View order_totals, den Trigger take_stock und den Index orders_customer.
Lösung
DROP VIEW order_totals;
DROP TRIGGER take_stock;
DROP INDEX orders_customer;DROP VIEW order_totals;, DROP TRIGGER take_stock; und DROP INDEX orders_customer;. Jede Art von Objekt hat ihr eigenes DROP. Die Prüfung zählt die Views, Trigger und Indizes, die übrig sind (ohne die internen sqlite_autoindex_… von UNIQUE und vom doppelten Schlüssel).
Sieh es dir an
Erst die Eltern
Eine Tabelle, auf die andere zeigen, kann nicht verschwinden, solange Zeilen auf sie zeigen:
DROP TABLE löscht zuerst alle Zeilen, und der Fremdschlüssel in orders lässt Kunden mit Bestellungen nicht verschwinden.
Übung
Eine leere Datenbank
Entferne die fünf Tabellen so, dass kein DROP mit einem Fehler abbricht. Am Ende darf nichts in der Datenbank übrig sein.
Lösung
DROP TABLE order_items;
DROP TABLE orders;
DROP TABLE products;
DROP TABLE customers;
DROP TABLE categories;Von den Kindern zu den Eltern, umgekehrt zu Etappe 1: order_items, orders, products, customers, categories. Mit categories zuerst bricht das DROP ab, weil die Produkte auf die Kategorien zeigen. Ohne das letzte DROP bleibt eine Tabelle übrig.
Genauer, für Neugierige
orders vor order_items geht auch, aber durch Zufall: ON DELETE CASCADE löscht die Zeilen der Bestellungen, bevor die Tabelle verschwindet. Ohne CASCADE bricht dieselbe Reihenfolge ab. Die sichere Reihenfolge ist immer von den Kindern zu den Eltern. In PostgreSQL entfernt DROP TABLE … CASCADE auch alles, was von der Tabelle abhängt: praktisch und gefährlich.
Die Idee
Der ganze Weg
Die Datenbank ist leer wie am Anfang. Unterwegs gab es:
- Etappe 1: die fünf
CREATE TABLEmit Schlüsseln,NOT NULL,UNIQUE,CHECK,DEFAULTundCASCADE. - Etappe 2:
INSERT,RETURNING, eine Transaktion mitBEGINundCOMMIT,ROLLBACKundINSERT … SELECT. - Etappe 3: die Fragen mit
JOIN,LEFT JOIN,GROUP BY,WITHundCOALESCE. - Etappe 4:
UPDATE,DELETEund Upsert, mit den Regeln, die die falschen abweisen. - Etappe 5: ein Index, eine View, ein Trigger und
ALTER TABLE. - Etappe 6: ein Bericht mit Fenstern, eine Kopie mit
VACUUM INTOundDROP.
Auf deinem Computer ist der letzte Schritt, die Datei bakery.db zu löschen. In PostgreSQL ist es DROP DATABASE bakery;, ausgeführt aus einer anderen Datenbank, während niemand mit ihr verbunden ist.
Test