Wie man Strings in einer PostgreSQL GROUP BY Abfrage verbindet

Bei der Arbeit mit Datenbanken, insbesondere mit PostgreSQL, können Sie auf ein häufiges Szenario stoßen, in dem Sie Strings innerhalb einer Gruppe von Datensätzen verketten müssen. Dies kommt typischerweise ins Spiel, wenn Sie einen Datensatz haben, der mehrere Einträge für eine einzige Kategorie enthält (z. B. Mitarbeiter im gleichen Unternehmen) und diese Einträge in einen einzigen String zur Präsentation oder Analyse sammeln möchten.

In diesem Blogbeitrag wird erklärt, wie Sie die String-Verkettung mit einer GROUP BY-Abfrage in PostgreSQL erreichen können. Wir werden moderne Lösungen sowie Ansätze für ältere Versionen von PostgreSQL behandeln, um ein umfassendes Verständnis zu ermöglichen.

Das Problem

Nehmen wir beispielsweise die folgende Mitarbeitertabelle:

Sie möchten diese Einträge nach COMPANY_ID gruppieren, sodass die Ausgabe eine Liste von Mitarbeitern anzeigt, die mit jedem Unternehmen verbunden sind. Die gewünschte Ausgabe würde wie folgt aussehen:

Die Lösung

PostgreSQL 9.0 oder Neuer

Wenn Sie PostgreSQL Version 9.0 oder neuer verwenden, können Sie die leistungsstarke eingebaute Funktion string_agg() verwenden, um Strings innerhalb Ihrer GROUP BY-Klausel effektiv zu concatenaten.

Hier ist, wie Sie die Abfrage schreiben können:

SELECT company_id, string_agg(employee, ', ')
FROM mytable
GROUP BY company_id;

Mit Sortierung

Darüber hinaus unterstützt PostgreSQL Version 9.0 ORDER BY innerhalb von Aggregatfunktionen, sodass Sie die Reihenfolge der Mitarbeiter angeben können:

SELECT company_id, string_agg(employee, ', ' ORDER BY employee)
FROM mytable
GROUP BY company_id;

Dies stellt sicher, dass Ihre Mitarbeiternamen in einer bestimmten Reihenfolge verketten.

PostgreSQL 8.4.x

Wenn Sie weiterhin mit PostgreSQL 8.4 arbeiten, das keine Unterstützung mehr erhält, können Sie die Funktion array_agg() in Kombination mit array_to_string() verwenden, um ähnliche Ergebnisse zu erzielen. Hier ist die SQL-Abfrage, die Sie verwenden würden:

SELECT company_id, array_to_string(array_agg(employee), ', ')
FROM mytable
GROUP BY company_id;

PostgreSQL 8.3.x und Älter

Für Benutzer von PostgreSQL 8.3 und früheren Versionen gibt es keine eingebaute Funktion zur direkten Durchführung der String-Verkettung. Die folgende benutzerdefinierte Implementierung kann diese Einschränkung umgehen:

1. Erstellen Sie eine neue Aggregatfunktion mit der Funktion textcat:

CREATE AGGREGATE textcat_all(
  basetype    = text,
  sfunc       = textcat,
  stype       = text,
  initcond    = ''
);

2. Um Trennzeichen wie , zwischen verketteten Strings einzufügen, erstellen Sie eine benutzerdefinierte Funktion:

CREATE FUNCTION commacat(acc text, instr text) RETURNS text AS $$
BEGIN
  IF acc IS NULL OR acc = '' THEN
    RETURN instr;
  ELSE
    RETURN acc || ', ' || instr;
  END IF;
END;
$$ LANGUAGE plpgsql;

Dies wird die Strings entsprechend verbinden.

Wenn Sie zusätzliche Kommas für NULL oder leere Werte entfernen möchten, hier ist eine verfeinerte Version:

CREATE FUNCTION commacat_ignore_nulls(acc text, instr text) RETURNS text AS $$
BEGIN
  IF acc IS NULL OR acc = '' THEN
    RETURN instr;
  ELSIF instr IS NULL OR instr = '' THEN
    RETURN acc;
  ELSE
    RETURN acc || ', ' || instr;
  END IF;
END;
$$ LANGUAGE plpgsql;

Fazit

Das Verketten von Strings in PostgreSQL, insbesondere während GROUP BY-Operationen, ist entscheidend für die Erstellung aussagekräftiger Berichte und Datensätze. Mit der Einführung von Funktionen wie string_agg() in neueren Versionen ist diese Aufgabe unkompliziert geworden.

Wenn Sie eine ältere Version verwenden, können die hier beschriebenen benutzerdefinierten Aggregationsmethoden ähnliche Ergebnisse erzielen. Überlegen Sie immer, Ihre PostgreSQL-Version zu aktualisieren, um von neueren Funktionen und Sicherheitsverbesserungen zu profitieren.

Viel Spaß beim Abfragen!