Rechnen in einer Abfrage
Kurz gesagt: In SELECT kannst du mit Spalten (+, -, *, /) für jede Zeile rechnen und mit ROUND runden, aber in SQLite, PostgreSQL und SQL Server schneidet die Division zweier ganzer Zahlen den Nachkommateil ab.
Problem
Was kostet diese Zeile?
Die Buchhaltung will für jede Bestellposition wissen, was sie kostet: die Menge mal den Preis. Die Tabelle order_items hat nur die beiden Zahlen:
Bestellung 3 enthält Äpfel, 4 zu 2,10 €, und Waschpulver, 3 zu 12,50 €. 152 Positionen von Hand auszurechnen ist keine Arbeit für einen Menschen. Und die Summe in noch einer Spalte zu speichern heißt, sie nach jeder Änderung richtig zu halten.
Die Idee
Eine berechnete Spalte
Neben Spalten kann SELECT Ausdrücke enthalten: Rechnungen mit den Spalten. Die Datenbank berechnet sie für jede Zeile:
Die Operatoren sind +, -, *, / und % (der Rest einer Division). Die berechnete Spalte heißt wie der Ausdruck selbst, deshalb gibst du ihr mit AS einen kurzen Alias. Die Tabelle ändert sich nicht: Die Rechnung gibt es nur im Ergebnis.
Du kannst auch nach dem Alias sortieren. Die fünf teuersten Positionen über alle Bestellungen:
Ein Ausdruck funktioniert auch ohne Tabelle. Dann berechnet SELECT ihn einmal, wie ein Taschenrechner. Punkt vor Strich, und Klammern ändern die Reihenfolge:
Probier es aus
Der Preis mit Mehrwertsteuer
Die Preise in der Datenbank sind ohne Mehrwertsteuer. Zeige Name, Preis und den Preis mit 20 % Mehrwertsteuer in einer Spalte namens with_vat.
Lösung
SELECT name, price, price * 1.2 AS with_vat FROM products;price * 1.2 AS with_vat. Mit 20 % Aufschlag ist der Preis 120 % des alten, also das 1,2-Fache. Das Weißbrot kommt auf 1,44 €. Hier prüft die Kontrolle auch die Spaltennamen.
Sieh es dir an
Die Note als Prozentwert
Die Website will die Noten von 1 bis 5 als Prozent zeigen: 5 ist 100 %, 4 ist 80 %. Führe aus:
Das ist Ganzzahldivision. Wenn beide Zahlen ganz sind, ist auch das Ergebnis ganz:
Sobald eine der Zahlen ein Bruch ist (2.0) oder mit CAST(… AS REAL) zu einem gemacht wird, behält die Division den Nachkommateil. Für die Prozente reicht es, zuerst mit 100.0 zu multiplizieren:
Mit price passiert diese Falle nicht: Der Preis ist eine Dezimalzahl (REAL). Achte auf Spalten mit ganzen Zahlen wie rating, quantity und stock.
Genauer, für Neugierige
PostgreSQL und SQL Server teilen ganze Zahlen genauso: 5 / 2 ist 2. MySQL macht das Gegenteil: Dort ist 5 / 2 gleich 2.5000, und die Ganzzahldivision hat einen eigenen Operator, DIV. Eine Division durch null ergibt in SQLite NULL und in PostgreSQL einen Fehler.
Die Idee
ROUND: auf den Cent
Eine Aktion: 15 % Rabatt auf Milchprodukte. Der neue Preis ist 85 % des alten:
Niemand kann 0,935 € für einen Joghurt zahlen. ROUND(x, 2) rundet auf zwei Stellen nach dem Punkt, also auf den Cent. ROUND(x) rundet auf eine ganze Zahl.
Sieh dir den Weißkäse an: 6.715 wurde zu 6.71, nicht zu 6.72. Warum, siehst du weiter unten.
Genauer, für Neugierige
Dezimalzahlen werden im Computer binär gespeichert, und binär hat 0.1 keine exakte Form, so wie 1/3 in Dezimalziffern keine exakte Form hat. Deshalb:
Der Bildschirm zeigt 0.3, aber der Vergleich sagt „nein“ (0). printf mit %!.20f zeigt, was wirklich gespeichert ist. Beim Weißkäse ist es dieselbe Geschichte: 7.9 × 0.85 ist im Speicher 6.71499…, und ROUND rundet ehrlich ab.
Deshalb halten echte Systeme Geld nicht als REAL. Sie speichern es in ganzen Cent (1,20 € sind 120) oder in einem Typ für exakte Dezimalzahlen: NUMERIC(10, 2) oder DECIMAL(10, 2) in PostgreSQL, MySQL und SQL Server. SQLite hat keinen solchen Typ, also sind Cent dort der Weg:
In Cent sind 790 × 85 genau 67.150, und geteilt durch 100 ergibt das 671.5 Cent, die auf 672 runden, also 6,72 €.
Probier es aus
Die Aktion
Zeige Name, Preis und Aktionspreis jedes Produkts. Der Aktionspreis ist price * 0.85, auf den Cent gerundet, in einer Spalte namens sale_price.
Lösung
SELECT name, price, ROUND(price * 0.85, 2) AS sale_price FROM products;ROUND(price * 0.85, 2) AS sale_price. ROUND umschließt den ganzen Ausdruck: Erst wird er berechnet, dann gerundet.
Übung
Zwei Aufgaben
1. Der Versicherer fragt, was die Waren im Lager wert sind. Zeige die fünf Produkte mit dem größten Lagerwert (Preis mal Lagerbestand), das größte zuerst. Die Geschenkkarte ist keine Ware im Regal: Behalte nur die Produkte, die eine Kategorie haben.
Name, stock und stock_value, die fünf größten:
Lösung
SELECT name, stock, price * stock AS stock_value
FROM products
WHERE category_id IS NOT NULL
ORDER BY stock_value DESC
LIMIT 5;price * stock AS stock_value, WHERE category_id IS NOT NULL, ORDER BY stock_value DESC LIMIT 5. Die Kaffeebohnen kommen zuerst: 15 Packungen zu 14,90 € sind 223,50 €. Hier prüft die Kontrolle auch die Reihenfolge.
2. Waren kommen in Kisten zu 12. Zeige für jedes Produkt mit Kategorie, das auf Lager ist, wie viele volle Kisten es gibt (boxes) und wie viele Stück außerhalb davon übrig sind (loose).
Name, stock, boxes und loose:
Lösung
SELECT name, stock, stock / 12 AS boxes, stock % 12 AS loose
FROM products
WHERE stock > 0 AND category_id IS NOT NULL;stock / 12 AS boxes, stock % 12 AS loose. Hier ist die Ganzzahldivision genau das, was du willst: 40 Brote sind 3 volle Kisten und 4 lose.
Test