Die Bäckerei, Etappe 3: die Fragen
Kurz gesagt: Etappe 3 beantwortet die Fragen der Bäckerei mit JOIN, GROUP BY, LEFT JOIN, WITH und COALESCE: die Speisekarte, die Rechnungen, die Bestseller und der Umsatz pro Stadt.
Problem
Was sie wissen wollen
Die Bäckerei hat Daten. Jetzt will sie Antworten: was in die Speisekarte kommt, wie viel jeder Kunde bezahlt hat, wovon mehr gebacken werden soll, wem man einen Rabatt schickt, welche Bestellungen groß sind und woher das Geld kommt.
Jede Frage ist eine Abfrage an die Datenbank aus Etappe 2. Nichts wird geschrieben: Die Datenbank ist nach jeder Abfrage dieselbe.
Die Idee
Die günstigen Sachen
Die einfachste Frage geht an eine Tabelle: die Produkte unter 2 €, vom günstigsten aufwärts.
Probier es aus
Die Speisekarte
Erstelle die Speisekarte: die Kategorie (Spalte category), den Namen und den Preis jedes Produkts. Sortiere nach Kategorie in der Reihenfolge ihrer Nummern (Brot, Teilchen, Getränke) und innerhalb der Kategorie vom günstigsten Produkt an.
Lösung
SELECT c.name AS category, p.name, p.price
FROM products p
JOIN categories c ON c.id = p.category_id
ORDER BY c.id, p.price;JOIN categories c ON c.id = p.category_id und ORDER BY c.id, p.price. Mit ORDER BY c.name kommen die Kategorien in alphabetischer Reihenfolge, nicht in der Reihenfolge der Speisekarte. Mit p.price DESC steht in der Kategorie das teuerste zuerst.
Die Idee
Die Rechnung
Jede Zeile der Bestellung kostet quantity * unit_price. Desislavas Bestellung 5:
Die Summe der Bestellung ist die Gesamtsumme dieser Zeilen: SUM(quantity * unit_price) mit GROUP BY der Bestellung.
Probier es aus
Die Rechnungen aller
Für jede Bestellung, die nicht storniert ist: die Nummer, der Name des Kunden, das Datum und die Summe (Spalte total, auf 2 Stellen gerundet). Sortiere nach Nummer.
Lösung
SELECT o.id, c.name, o.ordered_at, ROUND(SUM(i.quantity * i.unit_price), 2) AS total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_items i ON i.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY o.id
ORDER BY o.id;Drei Tabellen: orders, customers und order_items, WHERE o.status <> 'cancelled', GROUP BY o.id und ROUND(SUM(i.quantity * i.unit_price), 2). Ohne die Bedingung kommt auch die stornierte Bestellung 4 über 15,60 € heraus. Mit SUM(i.unit_price) zählen die drei Croissants wie eines.
Genauer, für Neugierige
GROUP BY o.id, während SELECT c.name und o.ordered_at hat, die nicht in der Gruppe stehen. SQLite erlaubt das. PostgreSQL akzeptiert o.ordered_at, weil der Primärschlüssel von orders in der Gruppe steht, aber nicht c.name, das aus einer anderen Tabelle kommt. Dort schreibst du GROUP BY o.id, c.name, und dann läuft die Abfrage überall.
Probier es aus
Wovon mehr gebacken werden soll
Die drei meistverkauften Produkte nach Stückzahl, ohne die stornierten Bestellungen: der Name und die Anzahl (Spalte pieces), vom meistverkauften an. Bei gleicher Stückzahl nach Namen.
Lösung
SELECT p.name, SUM(i.quantity) AS pieces
FROM order_items i
JOIN orders o ON o.id = i.order_id
JOIN products p ON p.id = i.product_id
WHERE o.status <> 'cancelled'
GROUP BY p.id
ORDER BY pieces DESC, p.name
LIMIT 3;SUM(i.quantity), GROUP BY p.id, ORDER BY pieces DESC, p.name und LIMIT 3. Mit COUNT(*) werden die Zeilen gezählt, nicht die Stücke: Desislavas sechs Croissants werden zu einem. Mit der stornierten Bestellung kommt die Baklava mit 6 Stück nach oben, die niemand abgeholt hat.
Die Idee
Wer noch nicht bestellt hat
LEFT JOIN behält jeden Kunden, auch ohne Bestellung. Dort sind die Spalten der Bestellung NULL, und WHERE o.id IS NULL lässt genau diese übrig:
Probier es aus
Ein Rabatt für die Neuen
Die Bäckerei schickt allen einen Rabatt, die keine echte Bestellung haben: keine Bestellungen oder nur stornierte. Zeige ihre Namen in alphabetischer Reihenfolge.
Lösung
SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status <> 'cancelled'
WHERE o.id IS NULL
ORDER BY c.name;Die Bedingung an den Status steht in ON: LEFT JOIN orders o ON o.customer_id = c.id AND o.status <> 'cancelled'. Dann wird Boris’ stornierte Bestellung nicht verbunden, und er bleibt mit NULL. Steht die Bedingung in WHERE, fallen die Zeilen mit NULL heraus (NULL <> 'cancelled' ist nicht wahr), und niemand bleibt übrig.
Probier es aus
Die großen Bestellungen
Welche Bestellungen (ohne die stornierten) liegen über der durchschnittlichen Summe pro Bestellung? Die Nummer und die Summe (total, auf 2 Stellen gerundet), nach Nummer. Bilde die Summen mit WITH totals AS (…).
Lösung
WITH totals AS (
SELECT o.id, SUM(i.quantity * i.unit_price) AS total
FROM orders o
JOIN order_items i ON i.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY o.id
)
SELECT id, ROUND(total, 2) AS total
FROM totals
WHERE total > (SELECT AVG(total) FROM totals)
ORDER BY id;WITH totals AS (SELECT o.id, SUM(…) AS total … WHERE o.status <> 'cancelled' GROUP BY o.id), dann WHERE total > (SELECT AVG(total) FROM totals). Der Durchschnitt ist 9,40 €. Mit der stornierten Bestellung wird er 10,43 €, und andere Bestellungen kommen heraus. AVG(quantity * unit_price) aus order_items ist der Durchschnitt pro Zeile (5,22 €), nicht pro Bestellung.
Übung
Woher das Geld kommt
Der Umsatz pro Stadt aus den Bestellungen, die nicht storniert sind, für alle Städte der Kunden. Eine Stadt ohne Umsatz hat 0, und die Kunden ohne Stadt stehen unter 'ohne Stadt'. Spalten city und revenue (auf 2 Stellen gerundet), vom größten Umsatz an.
Lösung
SELECT COALESCE(c.city, 'ohne Stadt') AS city,
ROUND(COALESCE(SUM(i.quantity * i.unit_price), 0), 2) AS revenue
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id AND o.status <> 'cancelled'
LEFT JOIN order_items i ON i.order_id = o.id
GROUP BY c.city
ORDER BY revenue DESC;Zwei LEFT JOINs ab customers, der Status in ON, GROUP BY c.city, COALESCE(c.city, 'ohne Stadt') und COALESCE(SUM(…), 0). Mit einem gewöhnlichen JOIN verschwindet die Stadt ohne Bestellungen. Ohne die Bedingung in ON bekommt Sofia auch Boris’ stornierte Bestellung.
Genauer, für Neugierige
SUM über eine Gruppe mit nur NULL ergibt NULL, nicht 0. Darum steht dort COALESCE(SUM(…), 0). Die Summe einer Bestellung ist eine Dezimalzahl, deshalb ROUND: 0,1 + 0,2 in REAL ist nicht genau 0,3.
Die Idee
Was als Nächstes kommt
Alle diese Abfragen lesen nur. Die nächste Etappe ändert die Daten: Preise, Status, Lagerbestand, gelöschte Bestellungen.
Test