Views
Kurz gesagt: Eine View ist eine Abfrage, die unter einem Namen gespeichert ist und wie eine Tabelle benutzt wird, aber selbst keine Daten speichert.
Problem
Dieselbe Rechnung überall
Wie viel ist eine Bestellung wert? In orders steht keine Summe. Sie wird aus den Zeilen der Bestellung berechnet:
Diese Rechnung braucht man für die Tagesauswertung, für den Kontoauszug des Kunden, für die Liste der unbezahlten Bestellungen. Jedes Mal wird sie neu geschrieben. Kommt eines Tages eine Liefergebühr oder ein Rabatt dazu, muss man sie an zehn Stellen ändern. Und eine wird vergessen.
Die Idee
CREATE VIEW
CREATE VIEW Name AS SELECT … speichert eine Abfrage unter einem Namen. Das ist eine View. Danach wird die View wie eine Tabelle gelesen:
Die Rechnung steht einmal in der View. Die Auswertungen sagen nur noch FROM order_totals.
Die Idee
Wie eine Tabelle
In SELECT wird eine View wie jede Tabelle benutzt: mit WHERE, ORDER BY, JOIN. In diesem Feld ist order_totals schon angelegt. Hier die neuen Bestellungen mit dem Kunden und dem Wert:
Die Abfrage ist kürzer und klarer: „die Bestellung, der Kunde, der Wert“. Wie der Wert berechnet wird, ist in der View versteckt.
Probier es aus
Die großen Bestellungen
Die View order_totals ist fertig. Welche Bestellungen liegen über 100 €? Zeige die Nummer und den Wert.
Lösung
SELECT order_id, total FROM order_totals WHERE total > 100;SELECT order_id, total FROM order_totals WHERE total > 100;. Drei Bestellungen: 13 (102,10 €), 23 (104 €) und 56 (118,20 €).
Sieh es dir an
Eine View enthält keine Daten
Der Kunde der Bestellung 1 ruft an: Er will 5 Packungen Weißkäse, nicht 4. Führ die Abfrage aus und sieh dir beide Ergebnisse an:
Vorher sind es 31,60 €, nachher 39,50 €. Niemand hat die View angefasst. Die View speichert nicht die Werte, sondern nur die Abfrage. Jedes Lesen führt sie neu über die Tabellen aus, eine View kann also nicht veralten.
Deshalb kann man auch nicht in eine View schreiben:
Die Daten ändert man in den Tabellen, aus denen die View liest.
Genauer, für Neugierige
PostgreSQL hat auch CREATE MATERIALIZED VIEW: eine View, die ihr Ergebnis wie eine Tabelle speichert. Sie ist schnell zu lesen, veraltet aber und wird mit REFRESH MATERIALIZED VIEW aktualisiert. Man benutzt sie für aufwendige Auswertungen, die nicht sekundengenau sein müssen. SQLite hat so eine View nicht.
Probier es aus
Verkäufe nach Produkt
Lege eine View product_sales an: für jedes Produkt (product_id), wie viele Stück bestellt wurden (sold), ohne die Zeilen stornierter Bestellungen.
Lösung
CREATE VIEW product_sales AS
SELECT oi.product_id, SUM(oi.quantity) AS sold
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
WHERE o.status <> 'cancelled'
GROUP BY oi.product_id;CREATE VIEW product_sales AS SELECT oi.product_id, SUM(oi.quantity) AS sold FROM order_items oi JOIN orders o ON o.id = oi.order_id WHERE o.status <> 'cancelled' GROUP BY oi.product_id;. Ohne den JOIN mit orders ist der Status der Bestellung nicht sichtbar.
Die Idee
DROP VIEW
Eine View wird mit DROP VIEW entfernt. DROP TABLE funktioniert nicht:
Die Tabellen, aus denen die View liest, werden nicht angefasst. Du entfernst nur die gespeicherte Abfrage.
Genauer, für Neugierige
Um eine View in SQLite zu ändern, entfernst du sie mit DROP VIEW und legst sie mit CREATE VIEW neu an. PostgreSQL und MySQL haben CREATE OR REPLACE VIEW, SQLite nicht:
Es funktioniert auch andersherum: Eine Spalte, die eine View benutzt, lässt sich nicht mit DROP COLUMN entfernen (die vorige Lektion hat es erwähnt).
Übung
Zwei Auswertungen
1. Der Inhaber will Dankeskarten an die drei Kunden schicken, die für das meiste Geld bestellt haben.
Zeige mit der View order_totals den Namen des Kunden und wie viel er insgesamt bestellt hat (spent), ohne stornierte Bestellungen. Nur die ersten drei, vom meisten zum wenigsten.
Lösung
SELECT c.name, ROUND(SUM(t.total), 2) AS spent
FROM customers c
JOIN orders o ON o.customer_id = c.id
JOIN order_totals t ON t.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY c.id, c.name
ORDER BY spent DESC
LIMIT 3;JOIN von customers, orders und order_totals, WHERE o.status <> 'cancelled', GROUP BY der Kunde, ORDER BY spent DESC LIMIT 3. Maria Ivanova (375,70 €), Martin Kostov (216,80 €) und Nikolay Stoyanov (143,50 €).
2. Am Morgen sieht das Lager nach, welche Bestellungen warten.
Lege eine View open_orders an mit der Bestellnummer, dem Namen des Kunden und dem Wert der Bestellung, nur für neue und bezahlte Bestellungen (new, paid). Benutze order_totals.
Lösung
CREATE VIEW open_orders AS
SELECT o.id, c.name, t.total
FROM orders o
JOIN customers c ON c.id = o.customer_id
JOIN order_totals t ON t.order_id = o.id
WHERE o.status IN ('new', 'paid');CREATE VIEW open_orders AS SELECT o.id, c.name, t.total FROM orders o JOIN customers c ON c.id = o.customer_id JOIN order_totals t ON t.order_id = o.id WHERE o.status IN ('new', 'paid');. 23 Bestellungen. Eine View kann aus einer anderen View lesen.
Test