Abfragen hierarchischer Daten mit Postgres

Hierarchische Daten sind weit verbreitet und einfach zu speichern, aber ihre Abfrage kann herausfordernd sein. Dieser Beitrag wird Sie durch den Prozess der Navigation in hierarchischen Daten in Postgres führen. Es ist erwähnenswert, dass diese Anweisungen auch auf andere Datenbanksysteme anwendbar sind, die Common Table Expressions unterstützen, wie sie in SQL:1999 eingeführt wurden.

Beispieldatensatz

Der Datensatz, den wir verwenden werden, ist eine gute Einführung für diejenigen, die mit hierarchischen Daten, auch bekannt als Eltern-Kind-Beziehungen, nicht vertraut sind. Das Beispiel sollte selbsterklärend sein.

WITH dataset(id, parent_id, name) AS (  VALUES   (1::int ,NULL::int, 'Earth')  ,(2, 1, 'Europe')  ,(3, 1, 'Asia')  ,(4, 2, 'Germany')  ,(5, 2, 'France')  ,(6, 3, 'China')  ,(7, 3, 'India')  ,(8, 4, 'Berlin')  ,(9, 4, 'Munich')  ,(10, 5, 'Paris')  ,(11, 5, 'Lyon')  ,(12, 6, 'Beijing')  ,(13, 6, 'Shanghai')  ,(14, 7, 'Mumbai')  ,(15, 7, 'Delhi')  ,(16, 2, 'Belgium')  ,(17, 16, 'Brussels'))SELECT * FROM dataset;

 id | parent_id |   name   ----+-----------+----------  1 |           | Earth  2 |         1 | Europe  3 |         1 | Asia  4 |         2 | Germany  5 |         2 | France  6 |         3 | China  7 |         3 | India  8 |         4 | Berlin  9 |         4 | Munich 10 |         5 | Paris 11 |         5 | Lyon 12 |         6 | Beijing 13 |         6 | Shanghai 14 |         7 | Mumbai 15 |         7 | Delhi 16 |         2 | Belgium 17 |        16 | Brussels

Entwickeln Sie die Abfrage

Beginnen wir mit einer Frage: „Auf welchem Kontinent liegt Brüssel?“ Anstatt das SQL sofort bereitzustellen, begleite mich bei der Entwicklung einer Strategie, die als Grundlage für die Beantwortung komplexerer Fragen dienen kann.

Schritt 1: Identifizieren Sie den Startknoten

WITH dataset(id, parent_id, name) AS (  VALUES-- ommitting values for brevity), start_node AS (  SELECT id, parent_id, name FROM dataset  WHERE name = 'Brussels')SELECT * FROM start_node;

id | parent_id |   name   ----+-----------+---------- 17 |        16 | Brussels

Schritt 2: Beziehen Sie das unmittelbare Elternteil (oder Kinder, wenn Sie in die entgegengesetzte Richtung navigieren)

WITH dataset(id, parent_id, name) AS (  VALUES-- ommitting values for brevity), start_node AS (  SELECT id, parent_id, name FROM dataset  WHERE name = 'Brussels')  ,ancestor AS (    SELECT * FROM start_node    UNION    SELECT dataset.* FROM dataset, start_node    WHERE start_node.parent_id = dataset.id    )

id | parent_id |   name   ----+-----------+---------- 16 |         2 | Belgium 17 |        16 | Brussels

Schritt 3: Mach die Abfrage rekursiv

Postgres ermöglicht die Erstellung rekursiver Abfragen, die auf ihren eigenen Ergebnissen ausgeführt werden. In diesem Szenario besteht unser Ziel darin, die gemeinsame Tabellenexpressionsvorlage für Vorfahren so anzupassen, dass sie sich selbst referenziert. Diese Anpassung wird die Abfrage veranlassen, das im Schritt 2 beschriebene Verfahren zu wiederholen, bis keine weiteren Zeilen zurückgegeben werden.

WITH RECURSIVE dataset(id, parent_id, name) AS (  VALUES-- ommitting values for brevity), start_node AS (  SELECT id, parent_id, name FROM dataset  WHERE name = 'Brussels')  , ancestor AS (    SELECT * FROM start_node    UNION    SELECT dataset.* FROM dataset, ancestor    WHERE ancestor.parent_id = dataset.id    )SELECT * FROM ancestor;

id | parent_id |   name   ----+-----------+---------- 17 |        16 | Brussels 16 |         2 | Belgium  2 |         1 | Europe  1 |           | Earth

Stellen Sie sicher, dass Sie diesen Schritt verstehen, bevor Sie fortfahren. Es kann etwas verwirrend sein!

Schritt 4: Fügen Sie einen Tiefe-Zähler hinzu

Der Tiefe-Zähler beginnt bei null und erhöht sich mit jedem hinzugefügten Vorfahren im Output.

...ancestor AS (    SELECT *, 0 AS depth FROM start_node    UNION    SELECT dataset.*, depth+1 FROM dataset, ancestor    WHERE ancestor.parent_id = dataset.id    )...

 id | parent_id |   name   | depth ----+-----------+----------+---------- 17 |        16 | Brussels |        0 16 |         2 | Belgium  |        1  2 |         1 | Europe   |        2  1 |           | Earth

Mit etwas mehr Code können Sie einen Filter festlegen, um den Kontinent basierend auf der zweitgrößten Tiefe zurückzugeben:

WITH RECURSIVE dataset(id, parent_id, name) AS (  VALUES-- ommitting values for brevity), start_node AS (  SELECT id, parent_id, name FROM dataset  WHERE name = 'Brussels')  , ancestor AS (    SELECT *, 0 AS depth FROM start_node    UNION    SELECT dataset.*, depth+1 FROM dataset, ancestor    WHERE ancestor.parent_id = dataset.id    )SELECT name as continent FROM ancestorWHERE depth = (SELECT MAX(depth)-1 FROM ancestor);

 continent ----------- Europe

Von Oben Nach Unten

Jetzt lassen Sie uns eine umgekehrte Navigation in Betracht ziehen, zum Beispiel das Zurückgeben von Städten in Asien. Wenn wir in Schritt 3 der obigen Anweisungen überspringen, würde es wie folgt erscheinen. Achten Sie darauf, wie sich die Richtung der Navigation ändert, indem Sie die Join-Bedingungen umkehren. Die Bedingung ancestor.parent_id = dataset.id navigiert nach oben, während ancestor.id = dataset.parent_id nach unten navigiert.

WITH RECURSIVE dataset(id, parent_id, name) AS (  VALUES-- ommitting values for brevity), start_node AS (  SELECT id, parent_id, name FROM dataset  WHERE name = 'Asia')  , ancestor AS (    SELECT *, 0 AS depth FROM start_node    UNION    SELECT dataset.*, depth+1 FROM dataset, ancestor    WHERE ancestor.id = dataset.parent_id    )SELECT name as cities_in_asia FROM ancestor WHERE depth = 2;

 cities_in_asia ---------------- Beijing

Aggregation

Zum Beispiel die Berechnung der Summe der Bevölkerungen in europäischen Städten:

WITH  RECURSIVE dataset(id, parent_id, name, population) AS (    VALUES     (1::int ,NULL::int, 'Earth', NULL::int)    ,(2, 1, 'Europe', NULL)    ,(3, 1, 'Asia', NULL)    ,(4, 2, 'Germany', NULL)    ,(5, 2, 'France', NULL)    ,(6, 3, 'China', NULL)    ,(7, 3, 'India', NULL)    ,(8, 4, 'Berlin', 3600000)    ,(9, 4, 'Munich', 1500000)    ,(10, 5, 'Paris', 2200000)    ,(11, 5, 'Lyon', 500000)    ,(12, 6, 'Beijing', 21000000)    ,(13, 6, 'Shanghai', 24000000)    ,(14, 7, 'Mumbai', 20000000)    ,(15, 7, 'Delhi', 30000000)    ,(16, 2, 'Belgium', 11000000)    ,(17, 16, 'Brussels', 2000000)    ), start_node AS (    SELECT id, parent_id, name, population FROM dataset    WHERE name = 'Europe')    , ancestor AS (        SELECT * FROM start_node        UNION        SELECT dataset.* FROM dataset, ancestor        WHERE ancestor.id = dataset.parent_id      )SELECT SUM(population) AS europe_population FROM ancestor;

 europe_population -------------------          20800000

Hinweise zur Leistung

Rekursionstiefe

Begrenzen Sie die Rekursionstiefe, um potenziell teure Abfragen zu vermeiden. Ihr Verständnis der Daten ist entscheidend für diesen Prozess. Aus dem obigen Beispiel wissen wir, dass die maximale Tiefe 3 beträgt. Durch die Begrenzung der Rekursion können Sie sich vor versteckten Datenqualitätsproblemen schützen:

ancestor AS (
SELECT *, 0 AS depth FROM start_node
UNION
SELECT dataset.*, depth+1 FROM dataset, ancestor
WHERE (ancestor.parent_id = dataset.id)
AND depth <= 3
)

Indizes

Wenn Sie mit persistenten Tabellen arbeiten, besteht der allgemeine Ansatz darin, Indizes für die Joinkonditionen zu erstellen, beispielsweise durch das Hinzufügen eines Index auf ‚id‘ und ‚parent_id‘. Bei kleinen Datensätzen könnte dies jedoch keinen signifikanten Unterschied machen.

Relationale vs. Graphdatenbanken

Postgres, ein Relational Database Management System (RDBMS), ist im Vergleich zu Graphdatenbanken wie Neo4j nicht die beste Wahl für die Navigation in komplexen Hierarchien. Dennoch ist die hierarchische Datenstruktur, mit der wir oft arbeiten, nur ein kleiner Teil eines größeren Bereichs. Dieser Bereich besteht hauptsächlich aus einfacheren Datenstrukturen, die gut für relationale Datenbanken geeignet sind. Daher ist es wahrscheinlich, dass Sie, es sei denn, der gesamte Bereich begünstigt graphbasierte Strukturen, die Nutzung dessen, was das RDBMS bietet, optimieren müssen.

Zusammenfassung

Denken Sie an diese Schritte, wenn Sie das nächste Mal auf einen hierarchischen Datensatz in Postgres stoßen:

1. Beginnen Sie damit, Ihren Ausgangspunkt zu identifizieren. Das bedeutet, den genauen Knoten zu bestimmen, von dem aus Sie Ihren Datenabrufprozess beginnen werden.

2. Bestimmen Sie dann das unmittelbare Eltern- oder Kindknoten, abhängig von der Richtung, in die Sie navigieren möchten. Dies ist entscheidend, um den Weg Ihrer Navigation zu gestalten.

3. Sobald Sie Ihre Richtung bestimmt haben, passen Sie Ihre Abfrage an, um sie rekursiv zu machen. Dadurch kann Ihre Abfrage wiederholt werden, bis keine weiteren Zeilen zurückgegeben werden.

4. Integrieren Sie einen Tiefe-Zähler in Ihre Abfrage. Dies wird festgehalten, wie viele Schichten Sie in die Datenbankstruktur navigiert haben, was klare Einblicke in die Komplexität Ihrer Daten bietet.

5. Schließlich, falls nötig, können Sie die Ergebnisse filtern oder aggregieren, um das Endprodukt Ihrer Abfrage anzupassen.