Rekursionen - Carl Hanser Verlag

Rekursionen
Das Problem
Ich möchte zu einem bestimmten Beitrag in einem Online-Forum alle Antworten/Beiträge ermitteln. Die
entsprechende Beispieltabelle liegt auf einem PostgreSQL-Server und hat folgenden Aufbau:
1. CREATE TABLE beitrag (
2.
beitrag_id
SERIAL,
3.
account_id
INT NOT NULL,
4.
bezug_beitrag_id INT NOT NULL DEFAULT 1,
5.
zeitstempel
TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
6.
nachricht
TEXT ,
7.
deleted
SMALLINT NOT NULL DEFAULT 0,
8.
PRIMARY KEY(beitrag_id),
9.
FOREIGN KEY(bezug_beitrag_id) REFERENCES beitrag(beitrag_id)
13. );
Sie erkennen sicherlich, dass die Spalte bezug_beitrag_id ein Fremdschlüssel auf die eigene Tabelle ist
und markiert, auf welchen Beitrag sich die Zeile bezieht. Die Spalte account_id ist eigentlich auch ein
Fremdschlüssel auf die account-Tabelle, aber ich habe die entsprechende Spezifikation der
Übersichtlichkeit hier weg gelassen.
Der Inhalt der Tabelle ist beispielhaft wie folgt:
1. INSERT INTO
2. beitrag (beitrag_id, account_id, bezug_beitrag_id, zeitstempel, nachricht)
3. VALUES
4.
(1, 1, 1, '')
5. , (2, 2, 1, '2011-05-01 14:13:00', 'Der Lieferservice ist super.')
6. , (3, 3, 2, '2011-05-02 11:45:00', 'Das finde ich auch.')
7. , (4, 5, 2, '2011-05-01 17:01:00', 'Aber ein wenig langsam.')
8. , (5, 2, 4, '2011-05-01 17:15:00', 'Finde ich nicht.')
9. , (6, 5, 1, '2011-06-12 09:07:00', 'Angebot könnte besser sein.');
Der Beitrag mit der id=1 ist mein „Wurzelbeitrag“. So verhindere ich NULL-Werte im Fremdschlüssel und
kann dadurch auch ermitteln, welche Beiträge keine Antwort auf einen andern Beitrag sind. Die Beiträge 3,
4 und 5 sind direkte oder indirekt Antworten auf Beitrag 2 und Beitrag 6 hat keine Antworten.
Wir wollen nun alle Antworten auf Beitrag 2. Ein erster naiver Ansatz ist die Verwendung eines einfachen
INNER JOINs:
1.
2.
3.
4.
oshop=# SELECT a.beitrag_id, a.bezug_beitrag_id, a.nachricht
oshop-# FROM beitrag a INNER JOIN beitrag b ON a.bezug_beitrag_id=b.beitrag_id
oshop-# WHERE b.beitrag_id=2
oshop-# ;
5. beitrag_id | bezug_beitrag_id |
nachricht
6. ------------+------------------+------------------------7.
3 |
2 | Das finde ich auch.
8.
4 |
2 | Aber ein wenig langsam.
Das Ergebnis ist unbefriedigend. Zunächst fällt auf, dass der Beitrag 2 selbst nicht aufgezeigt wird und dann,
dass die indirekten Antworten fehlen. Das erste Problem ließe sich noch durch ein UNION lösen, aber für
das zweite Problem ist eine Rekursion nötig.
Exkurs: Was ist eine Rekursion?
Formal betrachtet ist der Rekursionsbegriff in der Informatik, besonders in der theoretischen Informatik
sehr stark ausformuliert worden (z.B. primitive Rekursion, μ-Rekursion). In der Programmierung ist damit
aber eine Funktion gemeint, die sich selbst aufruft.
Das klassische Beispiel einer Rekursion ist die Berechnung der Fakultät der Zahl n (mathematisch n!). Die
Fakultät einer Zahl ist definiert als das Produkt aller natürlichen Zahlen ohne 0 kleiner oder gleich der Zahl,
also n! = 1*2*3*...*n; für n=4: 4! = 1 * 2 * 3 * 4 = 24.
Rekursiv lässt sich das – hier als Python-Funktion – wie folgt formulieren:
1.
2.
3.
4.
5.
def fakultaet(n):
if n == 1:
return 1
else:
return n * fakultaet(n-1)
Anhand dieses Beispiels können Sie gut die Elemente einer Rekursion erkennen:
1. Rekursionsfuß: Der Rekursionsfuß ist der Teil der Rekursion, welcher die Rekursion beendet oder
begründet. Im Beispiel ist dies der grün hinterlegte Teil. Meist kann man diesen Teil daran erkennen,
dass der Übergabeparameter überprüft wird und im if- oder else-Teil einer Berechnung ohne
Selbstaufruf vorkommt und im anderen Teil eine Berechnung mit dem Selbstaufruf.
2. Berechnungsvorschrift: Die eigentliche Verarbeitung der Daten. Im Beispiel der gelb hinterlegte Teil.
3. Selbstaufruf: Die eigene Funktion wird mit verändertem Funktionsparameter aufgerufen. Im
Beispiel der rot hinterlegte Teil.
Eine wichtige Frage ist, ob die Funktion irgendwann aufhört, sich selbst aufzurufen. Tut die rekursive
Funktion dies nicht, läuft sie potentiell unendlich lange bzw. wird in der Realität irgendwann einen
Stackoverflow erzeugen. In unserem Fall ist die Frage leicht zu beantworten: Da sich der
Übergabeparameter jeweils um 1 verringert, kommt er irgendwann bei 1 an. Dann wird aber der
Rekursionsfuß ausgeführt, der keinen Selbstaufruf enthält und die Rekursion endet.
Hinweis: Die Frage, ob eine Rekursion endet, ist auch schon bei einfachen Aufgabenstellungen nicht mehr
trivial. Einige Beispiele sind bis heute sogar ungelöst. Ich verweise auf das Collatz-Problem (unbedingt lesen:
https://de.wikipedia.org/wiki/Collatz-Problem).
Zurück zum Problem
Wie können Sie aber nun die Beiträge mit Hilfe der Rekursion ermitteln? Hier ein Ansatz in Pseudo Code,
damit ich mich und Sie nicht mit Sprachdetails herumplagen müssen:
1.
2.
3.
4.
5.
6.
7.
8.
alle_beiträge(such_beitrag_id)
begin
print beitrag mit beitrag_id=such_beitrag_id;
für alle beiträge x mit der bezug_beitrag_id=such_beitrag_id
begin
alle_beiträge(x.beitrag_id);
end;
end;
Die Rekursion endet, wenn kein Beitrag mehr gefunden wird, der als bezug_beitrag_id den
such_beitrag_id enthält. Dazu muss sichergestellt sein, dass die Daten keine Zyklen aufweisen. Würde
beispielsweise der Beitrag 2 auf Beitrag 5 zeigen, terminiert die Rekursion nicht.
Wie können Sie das jetzt aber in SQL umsetzen? Werden wir eine Prozedur oder sowas ähnliches schreiben?
Nun eine Prozedur, die das Ergebnis in eine temporäre Tabelle wegschreibt, ist nicht die schlechteste
Lösung. Aber in einigen DBMSen (MySQL/MariaDB: nein, PostgreSQL: ja) gibt es die Möglichkeit, einen
SELECT rekursiv aufzurufen.
Dazu werden common table expressions verwendet. Die funktionieren ungefähr so wie temporären
Tabellen, die aus einem SELECT abgeleitet werden. Zunächst ein Beispiel mit temporären Tabellen.
1.
2.
3.
4.
5.
6.
7.
8.
9.
oshop=# CREATE TEMPORARY TABLE t_anz AS
oshop-# SELECT kunde_id, COUNT(bestellung_id) AS anzahl
oshop-#
FROM
oshop-#
bestellung RIGHT JOIN kunde USING(kunde_id)
oshop-#
GROUP BY kunde_id
oshop-#
HAVING COUNT(bestellung_id) > 0;
oshop=#
oshop=# SELECT kunde_id, iban
oshop-# FROM bankverbindung RIGHT JOIN t_anz USING(kunde_id);
10. kunde_id |
iban
11. ----------+-----------------------------------12.
1 | 100100101111111111
13.
2 |
Die temporäre Tabelle t_anz enthält alle Kunden (kunde_id, anzahl), welche überhaupt Bestellungen
aufgegeben haben. Im zweiten SELECT wird diese temporäre Tabelle für einen OUTER JOIN mit der Tabelle
bankverbindung genutzt und könnte auch noch für weitere Auswertungen in der selben Session genutzt
werden.
So sähe das Beispiel unter Verwendung einer common table expression (CTE) aus:
1.
2.
3.
4.
5.
6.
7.
8.
9.
0.
oshop=#
oshop-#
oshop(#
oshop(#
oshop(#
oshop(#
oshop(#
oshop(#
oshop-#
oshop-#
WITH cte_anz AS
(
SELECT kunde_id, COUNT(bestellung_id) AS anzahl
FROM
bestellung RIGHT JOIN kunde USING(kunde_id)
GROUP BY kunde_id
HAVING COUNT(bestellung_id) > 0
)
SELECT kunde_id, iban
FROM bankverbindung RIGHT JOIN cte_anz USING(kunde_id);
11. kunde_id |
iban
12. ----------+-----------------------------------13.
1 | 100100101111111111
14.
2 |
In der Zeile 1 wird die Definition der CTE eingeleitet. Hinter dem Namen cte_anz könnte man in Klammern
den zwei Spalten dieser Tabelle noch neue Namen vergeben. Erfolgt dies nicht, haben die Spalten die
Namen, die sich aus dem nachfolgenden SELECT ergeben (hier: kunde_id und anzahl). In den runden
Klammern wird nun der SELECT angegeben, der die Tabellen definiert. Bitte beachten Sie, dass hinter der
schließenden runden Klammer in Zeile 8 kein Semikolon steht, da die Anweisung noch nicht abgeschlossen
ist. Die Definition der CTE ist Teil der Anweisung und nicht die Anweisung selbst.
Nach der schließenden runden Klammer erfolgt nun die eigentliche Anweisung in Zeile 9, hier ein SELECT. In
dieser kann die CTE wie eine vorhandene Tabelle verwendet werden. Anders als bei temporären Tabellen
steht die CTE aber nur dieser Anweisung zur Verfügung und nicht noch weiteren nachfolgenden.
Bauen wir den obigen naiven Ansatz auf CTE um, ergibt sich folgendes:
1.
2.
3.
4.
5.
6.
oshop=#
oshop-#
oshop(#
oshop(#
oshop-#
oshop-#
WITH ant_auf AS
(
SELECT * FROM beitrag WHERE bezug_beitrag_id = 2
)
SELECT beitrag_id, bezug_beitrag_id, nachricht
FROM ant_auf;
7.
beitrag_id | bezug_beitrag_id |
nachricht
8. ------------+------------------+------------------------9.
3 |
2 | Das finde ich auch.
10.
4 |
2 | Aber ein wenig langsam.
Das ist alles nicht sehr überraschend, aber jetzt kommt der Clou: Innerhalb einer CTE kann der SELECT die
CTE wieder aufrufen; es erfolgt also ein rekursiver Aufruf! Und genau dieses Feature können Sie anwenden,
endlich :-)
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
oshop=#
oshop-#
oshop(#
oshop(#
oshop(#
oshop(#
oshop(#
oshop(#
oshop(#
oshop-#
WITH RECURSIVE ant_auf AS
(
SELECT * FROM beitrag WHERE beitrag_id = 2
UNION ALL
SELECT beitrag.*
FROM
beitrag INNER JOIN ant_auf orig
ON beitrag.bezug_beitrag_id = orig.beitrag_id
)
SELECT beitrag_id, bezug_beitrag_id, nachricht FROM ant_auf;
12. beitrag_id | bezug_beitrag_id |
nachricht
13. ------------+------------------+-----------------------------14.
2 |
1 | Der Lieferservice ist super.
15.
3 |
2 | Das finde ich auch.
16.
4 |
2 | Aber ein wenig langsam.
17.
5 |
4 | Finde ich nicht.
Der Rekursionsfuß besteht aus zwei Teilen. Einmal der SELECT auf Beitrag 2 und zum zweiten die Bedingung
des INNER JOINs. Falls diese nämlich keine passenden Primär-/Fremdschlüsselpaare mehr findet, werden
keine neun Zeilen in der CTE mehr generiert.
Doch der Reihe nach: In Zeile 1 steht das Schlüsselwort RECURSIVE. Dieses kündigt dem SQL-Interpreter
einen rekursiven Aufruf an; fehlt es, gibt's eine hässliche Fehlermeldung, da dann die Tabelle ant_auf
innerhalb der CTE unbekannt ist. In Zeile 3 wird die erste Zeile der CTE-Tabelle ant_auf erzeugt. Es ist der
Beitrag 2 aus der Tabelle beitrag. In Zeile 5 steht nun ein weiterer SELECT und der UNION in Zeile 4 sorgt
dafür, dass die Ergebnisse beider SELECTs als eine Ergebnismenge betrachtet werden. Der SELECT in Zeile 5
beinhaltet in Zeile 7 einen JOIN auf die CTE selbst, sie ruft sich somit wieder auf. Nun enthält die CTE beim
ersten Durchgang ja nur die eine Zeile vom Beitrag 2. Durch den INNER JOIN in Zeile 5 werden nun alle
Beiträge verknüpft, die im Fremdschlüssel bezug_beitrag_id eine 2 stehen haben. Dadurch enthält die
CTE jetzt 3 Beiträge, nämlich 2, 3, und 4. Nun ruft der INNER JOIN wieder ant_auf auf, so dass er die CTE
mit allen Beiträgen in der Tabelle beitrag verknüpft, die im Fremdschlüssel den Wert 2, 3 oder 4 haben; es
kommt der Beitrag 5 hinzu. Jetzt findet der INNER JOIN keine weiteren neuen Beiträge, die die Werte 2, 3, 4
oder 5 als Fremdschlüsselwert enthalten, und der rekursive Aufruf stoppt.
Zusammenfassung
In bestimmten Situationen sind Daten nicht nur einfach über eine Primär-/Fremdschlüsselpaarung
miteinander verknüpft, sondern auch indirekt. Beispiele sind Forenbeiträge, Familienbeziehungen,
Mitarbeiterorganisation, Nachbarschaftsverhältnisse etc. In solchen Fällen ist liefert ein einfacher JOIN
immer nur die direkten Antworten auf einen Forenbeitrag, die direkten Nachfahren einer Person, die
unmittelbaren Mitarbeiter eines Vorgesetzten, die direkte Nachbarschaft etc. Durch eine Rekursion können
diese Verhältnisse vollständig ermittelt werden. Die Rekursion kann durch entsprechende Prozeduren,
client-seitige Programmierung oder eben einer common table expression gelöst werden.
Dabei ist darauf zu achten, dass die Daten zyklenfrei sind oder eine Tiefenzähler verwendet wird, der die
Rekursion ab einer bestimmten Tiefe abbricht.
Quellen
[1] Adams, Ralf; SQL: Der Grundkurs für Ausbildung und Praxis. Mit Beispielen in MySQL/MariaDB;
September 2016; Hanser Verlag; ISBN 978-3-4464-5074-5
[2] https://msdn.microsoft.com/de-de/library/ms175972.aspx
[3] https://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL
[4] http://www.essentialsql.com/introduction-common-table-expressions-ctes/