Wie kann man mehrere Arrays effizient in einer einzigen SQL-Abfrage aggregieren?

Mehrere array_agg()-Aufrufe in einer einzigen Abfrage: Aggregieren verschachtelter Arrays

Im Bereich relationaler Datenbanken kommt es vor, dass mehrere Arrays vorhanden sind müssen in einer einzigen Abfrage zusammengefasst werden. Dies kann eine knifflige Aufgabe sein, insbesondere wenn Joins beteiligt sind.

Das Problem

Die Herausforderung entsteht, wenn versucht wird, Arrays aus verschiedenen Tabellen über mehrere Joins zusammenzufassen. Das resultierende Array kann unnötige Duplikate und Inkonsistenzen enthalten. Beispielsweise verfügen Sie möglicherweise über eine Tabelle mit Mitarbeitern mit mehreren Adressen (Adresse) und Arbeitstagen (Arbeitstage).

SELECT name, age, array_agg(ad.street), arrag_agg(wd.day)
FROM employees e
JOIN address ad ON e.id = ad.employeeid
JOIN workingdays wd ON e.id = wd.employeeid
GROUP BY name, age

Diese Abfrage führt zu einem Array von Straßennamen und Arbeitstagen, dupliziert jedoch die Werte mehrmals.

Die Lösung: Zuerst aggregieren

Der Schlüssel zur Lösung dieses Problems besteht darin, die Arrays vorher zu aggregieren Durchführen der Verknüpfung. Indem Sie zuerst aggregieren, verhindern Sie, dass sich die Zeilen unnötig vervielfachen.

SELECT e.id, e.name, e.age, e.streets, array_agg(wd.day) AS days
FROM (
   SELECT e.id, e.name, e.age, array_agg(ad.street) AS streets
   FROM   employees e 
   JOIN   address  ad ON ad.employeeid = e.id
   GROUP  BY e.id  -- PK covers whole row
   ) e
JOIN   workingdays wd ON wd.employeeid = e.id
GROUP  BY e.id, e.name, e.age;

Bei dieser Abfrage erfolgt die Adressaggregation in einer Unterabfrage, bevor sie mit der Tabelle „Arbeitstage“ verknüpft wird. Dadurch wird sichergestellt, dass jedem Mitarbeiter nur ein Satz von Straßennamen und Arbeitstagen zugeordnet ist.

Alternativ: Korrelierte Unterabfragen und JOIN LATERAL

Zur selektiven Filterung nach Mitarbeitern, korreliert Unterabfragen oder JOIN LATERAL (in Postgres 9.3 oder höher) können sein beschäftigt:

SELECT name, age
    , (SELECT array_agg(street) FROM address WHERE employeeid = e.id) AS streets
    , (SELECT array_agg(day) FROM workingdays WHERE employeeid = e.id) AS days
FROM   employees e
WHERE  e.namer = 'peter';  -- very selective

JOIN LATERAL kann auch in Postgres verwendet werden:

SELECT e.name, e.age, a.streets, w.days
FROM   employees e
LEFT   JOIN LATERAL (
   SELECT array_agg(street) AS streets
   FROM   address
   WHERE  employeeid = e.id
   GROUP  BY 1
   ) a ON true
LEFT   JOIN LATERAL (
   SELECT array_agg(day) AS days
   FROM   workingdays
   WHERE  employeeid = e.id
   GROUP  BY 1
   ) w ON true
WHERE  e.name = 'peter';  -- very selective

Diese Abfragen behalten alle qualifizierten Mitarbeiter im Ergebnis.

Das obige ist der detaillierte Inhalt vonWie kann man mehrere Arrays effizient in einer einzigen SQL-Abfrage aggregieren?. Für weitere Informationen folgen Sie bitte anderen verwandten Artikeln auf der PHP chinesischen Website!