Einfache Unterabfragen
Nicht immer stehen Tabellen so zur Verfügung, wie wir die Daten benötigen. Gelegentlich müssen wir uns Hilfsabfragen bauen. Eine Abfrage stellt, wie auch eine Tabelle, eine Menge von Datensätzen dar. Insoweit darf sie überall dort verwendet werden, wo wir auch eine Tabelle verwenden können.
Eine solche Abfrage nennen wir dann auch abgeleitete Tabelle.Sie ist in Klammern zu notieren und mit einem Aliasbezeichner zu versehen.
Nehmen Sie an, Sie wollten den Gesamtumsatz ermitteln, den Sie mit zahlenden Kunden gemacht haben. Jedoch wollen Sie nicht alle Kunden in der Analyse berücksichtigen. Sie wollen lediglich die Kunden berücksichtigen, für die mehr als 30 Verleihvorgänge registriert sind.
Nähern wir uns der Problemlösung schrittweise:
- Zuerst brauchen wir mal die IDs der Kunden, die uns interessieren. Das sind die, die in der Verleihtabelle (
rental) mehr als 30 Verleihvorgänge verzeichnet haben. - Für die in Schritt 1 ermittelten Kunden schauen wir in der Zahlungstabelle nach und addieren sämtliche Zahlungen dieser Kunden zu dem gesuchten Gesamtumsatz.
|
1 2 3 4 5 6 7 |
SELECT -- Schritt 1: Kunden mit mehr als 30 Ausleihvorgängen customer_id , count(rental_id) AS count FROM rental GROUP BY customer_id HAVING count(rental_id) > 30; |
+-----------+-----+ |customer_id|count| +-----------+-----+ |550 |32 | |51 |33 | |406 |32 | |176 |37 | |576 |34 | |309 |31 | |390 |33 | ... (134 Datensätze insgesamt)
Das Ergebnis dieser Abfrage ist eine Tabelle. Diese ist zwar nicht in der Datenbank fix gespeichert, ungeachtet dessen handelt es sich um eine Tabelle. Diese Tabelle können wir so nutzen, als wäre sie eine übliche Tabelle, die wir bis hierhin einfach nur durch ihren jeweiligen Namen als Datenquelle angegeben haben. Dazu schreiben wir die Abfrage in runden Klammern in die FROM– oder JOIN-Klausel. Wir müssen darauf achten, ihr einen Aliasbezeichner mitzugeben. So geschehen, behandelt SQL die Abfrage wie eine handelsübliche Tabelle. Wir können nun Schritt 2 der Lösung implementieren. Dazu verknüpfen wir unsere abgeleitete Tabelle mit der Zahlungstabelle über das Attribut customer_id.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
SELECT -- Schritt 2: aggregiere die passenden Umsätze sum(amount) AS total FROM ( SELECT -- Schritt 1: Kunden mit mehr als 30 Ausleihvorgängen customer_id , count(rental_id) AS count FROM rental GROUP BY customer_id HAVING count(rental_id) > 30 ) AS c JOIN payment p ON p.customer_id = c.customer_id; |
Die abgeleitete Tabelle, hier kurz als c bezeichnet, liefert die für uns relevanten Kunden-IDs, für welche wir im zweiten Schritt die geleisteten Zahlungen, als Ist-Umsatz interpretiert, aufsummieren.
+--------+ |total | +--------+ |17560.65| +--------+
Unterabfragen können auch als reine Vergleichsdatenlieferanten eingesetzt werden. In diesem Fal, in der WHERE-Klausel platziert, benötigen Sie keinen Alias-Bezeichner.
Ermitteln wir die Summe aller Umsätze je Kunde. In die Analyse sollen lediglich die Umsätze einfließen, die überdurchschnittlich sind. Wir überlegen uns wieder die erforderlichen Schritte:
- Wie groß ist der Durchschnittsumsatz?
- Summiere alle Umsätze pro Kunde, wenn der jeweils getätigte Umsatz über dem Durchschnittsumsatz liegt.
|
1 2 3 |
SELECT -- Schritt 1: Durchschnittsumsatz avg(amount) AS avg FROM payment; |
+------------------+ |avg | +------------------+ |4.2006056453822965| +------------------+
Der Durchschnittsumsatz ist eine einzige Zahl. Das ist wichtig zu beachten. Eine einzelne Zahl können wir in einer Bedingung, die zwei Werte miteinander vergleicht direkt als Vergleichswert nutzen.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
SELECT last_name , first_name , sum(p.amount) AS total FROM payment p JOIN customer c ON p.customer_id = c.customer_id WHERE amount > ( --Schritt 1: Durchschnittsumsatz SELECT avg(amount) FROM payment ) GROUP BY c.last_name , c.first_name ORDER BY last_name , first_name; |
Beachten Sie, den >-Operator nur deshalb nutzen zu können, weil die Unterabfrage nur einen einzigen Wert liefert! Sie können keinen atomaren Wert wie amount mit einer Tabelle mit mehreren Zeilen und/oder Spalten vergleichen.
+---------+----------+------+ |last_name|first_name|total | +---------+----------+------+ |Abney |Rafael |73.88 | |Adam |Nathaniel |83.88 | |Adams |Kathleen |65.89 | |Alexander|Diana |70.87 | |Allard |Gordon |134.79| ... (599 Datensätze insgesamt)
Freilich können wir Unterabfragen nicht nur als Ersatz für Tabellen oder als Vergleichslieferanten in Datensatzauswahlbedingungen nutzen. Auch in der Gruppenauswahl können wir Unterabfragen einsetzen. Und wir können das Ganze beliebig komplex gestalten.
Betrachten wir die vordergründig einfache Frage: Welchen Umsatz haben wir mit Kunden getätigt, deren Gesamtumsatz jeweils überdurchschnittlich hoch ist?
- Wie hoch sind die Gesamtumsätze je Kunde?
- Wie hoch ist der Durchschnittliche Umsatz pro Kunde?
- Summiere die Umsätze pro Kunde, wenn dieser zu den überdurchschnittlichen Kunden zählt.
Erfahrene SQL-Programmierer erstellen die Abfrage in einem Schritt. Wir nähern uns der Lösung wieder schrittweise. So sehen Sie die Entwicklung auch komplexer Abfragen.
|
1 2 3 4 5 |
SELECT -- Umsatzsummen je Kunde, Kunde selbst ist uninteressant sum(amount) AS amount FROM payment GROUP BY customer_id; |
|
1 2 3 4 5 6 7 8 9 |
SELECT -- durchschnittlicher Umsatz aller Kunden avg(amount) AS avg_amount FROM ( SELECT sum(amount) AS amount FROM payment GROUP BY customer_id ) avg_amount; |
+--------------------+ |avg_amount | +--------------------+ |102.3573288814691152| +--------------------+
Das ist nun wieder ein einzelner Wert, den wir in üblichen Vergleichen, diesmal in der Gruppenauswahlbedingung, einsetzen können.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
SELECT -- Schritt 3 c.first_name , c.last_name , sum(p.amount) AS total FROM payment p JOIN customer c ON c.customer_id = p.customer_id GROUP BY c.first_name , c.last_name HAVING sum(p.amount) > ( SELECT -- Schritt 2 avg(amount) AS avg_amount FROM ( SELECT -- Schritt 1 sum(amount) AS amount FROM payment GROUP BY customer_id ) avg_amount ) ORDER BY total DESC; |
Die eingefügten Kommentare spiegeln die Schritte wieder, die ich oben beschrieben habe.
Die obigen Unterabfragen sind so formuliert, dass sie jeweils nur einen Datensatz liefern. Unterabfragen können und dürfen aber auch mehrere Datensätze zurückgeben. Wir müssen lediglich darauf achten, die korrekten Vergleichsoperatoren zu verwenden. Beachten Sie mögliche Schachtelungen, wie wir sie oben genutzt haben.
Liefert eine Unterfrage mehr als einen Datensatz, so können wir beispielsweise mit IN prüfen ob ein Attributwert der äußeren Abfrage in der Ergebnis menge der Unterabfrage enthalten ist. Vergleiche auf größer oder kleiner können mit den Schlüsselworten ALL oder ANY qualifiziert werden. ALL bedeutet dann, dass der Vergleich für alle Datensätze der Unterabfrage zutreffen muss. ANY bedeutet, dass der Vergleich für mindestens einen Datensatz der Unterabfrage
zutreffen muss.
Mit EXISTS kann geprüft werden, ob die Unterabfrage überhaupt einen Datensatz geliefert hat. EXISTS liefert TRUE (wahr), wenn mindestens ein Datensatz vorhanden ist, ansonsten
liefert EXISTS den Wert FALSE (falsch).
Korrelierte Unterabfragen
Bisher waren alle Unterabfragen, die wir geschrieben haben,auch separat ausführbar. Nehmen Sie jedoch an, Sie wollten die Summe aller überdurchschnittlichen Zahlungen pro Kunde ermitteln. Dabei beziehen wir uns nicht auf die durchschnittliche Zahlungshöhe aller Kunden, sondern auf die durchschnittliche Zahlungshöhe je Kunde.
Um die Aufgabe zu lösen, benötigen Sie pro Kunndendatensatz, den Sie ausgeben, die Höhe der durchschnittlichen Zahlungen. Alle darüber liegenden Zahlungen summieren Sie dann, natürlich pro Kunde auf.
Um die Höhe der durchschnittliche Zahlungen pro Kunde zu ermitteln, brauchen Sie eine Unterabfrage, die für jeden Kunden individuell ausgeführt wird. Sie muss daher auf die Oberabfrage Bezug nehmen. Im Folgenden Beispiel verbindet daher die customer_id Ober- und Unterabfrage. Die customer_id der Unterabfrage muss der customer_id der Oberabfrage entsprechen, die deshalb mit p1 qualifiziert wird, um eindeutig ansprechbar zu sein.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
-- Summe aller überdurchschnittlichen Zahlungen pro Kunde SELECT c.customer_id , last_name , first_name , sum(amount) AS total FROM payment p1 JOIN customer c ON p1.customer_id = c.customer_id WHERE amount > ( SELECT avg(amount) FROM payment WHERE customer_id = p1.customer_id ) GROUP BY c.customer_id , last_name , first_name ORDER BY total DESC; |
Die Unterabfrage lässt sich nun nicht mehr separat ausführen. Wir nennen eine solche Unterabfrage korrelierte Unterabfrage.
+-----------+---------+----------+------+ |customer_id|last_name|first_name|total | +-----------+---------+----------+------+ |526 |Seal |Karl |162.76| |148 |Hunt |Eleanor |157.76| |459 |Collazo |Tommy |144.79| |144 |Shaw |Clara |141.79| |403 |Way |Mike |130.79| ... (599 Datensätze insgesamt)