listagg — Zeilen in String-Listen wandeln


Die Funktion listagg fügt Werte aus einer Gruppe von Zeilen zu einem String mit konfigurierbarem Separator zusammen. Listagg wird häufig zur denomralisierung genutzt – z. B. um einen String aus komma-separierten Werten (CSV) mit Daten aus mehreren Zeilen zu befüllen.

Listagg führt kein escaping durch: im Ergebnis ist es einem Seperator nicht anzusehen, ob er zwei Werte trennt, oder Teil eines Wertes ist. Die sichere Nutzung von listagg für den elektromischen Datenaustausch ist daher auf Fälle beschränkt in denen ein uverwechselbarer Seperator gewählt werden kann: z. B. wenn die Werte nur Zahlen, Datums- oder Zeitangaben oder Strings, die den Separator nicht enhalten können sind.

Für die Umsetzung eines elekrtonischen Datenaustausches bieten SQL-Arrays und Dokumente (JSON, XML) den Vorteil der Typensicherheit oder zumindest das ordentliche escaping.

Syntax

Listagg zählt zu den Sortierte-Menge-Funktionen (ordered set functions) welche die within group-Klausel benötigen um die Sortierung zu deklarieren. Die minimale Syntax ist:

LISTAGG(<Ausdruck>, <Separator>) WITHIN GROUP(ORDER BY …)

Der <Ausdruck> darf keine Window- oder Aggegatfunktionen und keine Unterabfragen beinhalten.0. Laut Standard muss der <Separator> ein Literal sein – kein Ausdruck oder Bind-Parameter1. In der Praxis werden Bind-Parameter jedoch unterstützt.

Wie die miesten anderen Aggregatfunktionen erntfernt listagg null-Werte vor der Aggregierung2. Wenn kein nicht-null-Wert überbleibt, ist das Ergebnis von listagg null. Bei Bedarf kann man null-Werte vor der Aggregierung mit coalesce ersetzen.

Die on overflow-Klausel

Das Ergebnis von listagg ist entweder vom Typ varchar oder clob mit einer systemspezifischen Längenbeschränkung.3 In der Praxis wird varchar verwendet.4.

Das Verhalten von listagg, wenn das Ergebnis zu lange wird, kann mit der optionalen on overflow-Klausel gesteuert werden:

LISTAGG(<Ausdruck>, <Separator> ON OVERFLOW …)

Das Default ist on overflow error. Der Standard erfordert in diesem Fall eine Exception mit dem SQLSTATE 220015 – in der Praxis wird diese Anforderung nicht erfüllt.

Mit on overflow truncate wird der Überlauf verhindert, indem nur soviele Werte aggregiert werden, wie im Ergebnis platz haben. Weiters kann man bei on overflow truncate angeben, wie das Ergebnis bei einem Überlauf abgeschlossen werden soll:

ON OVERFLOW TRUNCATE [<Fueller>] WITH[OUT] COUNT

Der optionale <Fueller> (Default: drei Punke ...) wird bei einem Überlauf als letztes Element verwendet.

Bei Verwendung von with count wird bei einem Überlauf die Anzahl der abgeschnittenen Werte in Klammern gesetzt und hinten an das Ergebnis gehängt.

Der SQL-Standard erfordert keine Warnung, wenn Werte wegen eines drohenden Überlaufes abgeschnitten werden.6. Um zu wissen, ob das Ergebnis vollständig ist, kann man das Ende des Ergebnis-Strings interpretieren7 oder berechnen, wie lange das vollständige Ergebnis sein müsste8 und mit der tatsächlichen Länge vergleichen.

distinct

Die Funktion listagg akzeptiert die Schlüsselwörte all und distinct:

LISTAGG( [ALL|DISTINCT] <expression>, <separator> …) …

Wenn weder noch angegeben wird, gilt all als Default. Durch distinct werden Duplikate vor der Aggregierung entfernt. Beachte dass die Erkennung von Duplikaten unter berücksichtigung der aktiven Collation stattfindet.

Warnung

Der Standard legt nicht fest welche Vorkommen bei Duplikaten entfernt werden.

Wenn die order by-Klausel verschiedene Vorkommen desselben Wertes an verschiedene Positionen des Ergebnisses setzt, ist undefiniert an welcher Stelle dieser Wert im Ergebnis verbleibt.9

Das distinct-Verhalten kann auch manuell erreicht werden, indem man die Duplikate schon vor der Aggregierung entfernt – z. B. in einer Unterabfrage. Das funktioniert auch in Datenbanken, die distinct bei listagg nicht unterstützen und ermöglicht es festzulegen welches Duplikat bebehalten werden soll.

Das folgende Beispiel zeigt diesen Ansatz. Die Splaten g und o stehen für die group by und order by Spalten. Das Beispiel nutzt min(o) um das erste Vorkommen eines Duplikates beizubehalten.

SELECT g
     , LISTAGG(value, ',') WITHIN GROUP (ORDER BY o) list
  FROM (SELECT g, min(o) o, value
          FROM dist_listagg
         GROUP BY g, value
       ) dt
 GROUP BY g

Listagg mit filter und over kombinieren

Listagg kann den filter und over-Klausel kombiniert werden:

LISTAGG(…) WITHIN GROUP(…) [FILTER(WHERE …)] [OVER(…)]

Die filter-Klausel entfernt Zeilen vor der Aggregierung. Denselben Effekt kann man mit case erzielen.

In der over-Klausel ist es nicht erlaubt eine order by-Klausel zu vwerenden, 10 da die verpflichtende within group-Klausel ohnehin eine order by-Klausel erzwingt. Es ist daher nicht möglich, die zu aggregierenden Zeilen in der over-Klausel näher einzuschränken: es werden immer alle Zeilen der Partition aggregiert.

Kompatibilität

Listagg wurde mit SQL:2016 als optionale Funktion T625 eingeführt. Obwohl listagg noch nicht auf breiter Basis verfügbar ist, bieten die meisten Datenkanken eine ähnliche Funktionalität mit proprietärer Sytnax an.

Hinweis in eigener Sache

Wenn Dir dieser Artikel gefällt, könnte mein Buch SQL Performance Explained oder mein Training auch etwas für Dich sein.

Standard-Konforme Alternativen

Arrays

Wenn die Abfrage nicht unbedingt einen String mit Trennzeichen liefern muss, kann man auch ein Array verwenden. Das Array kann man entweder mit der array_agg-Funktion oder mit einer Unterabfrage erzeugen.

ARRAY_AGG(<Ausdruck> ORDER BY …)
ARRAY(<Abfrage>)

In der zweiten Form kann die <Abfrage> auch distinct und fetch first enthalten um Duplikate zu entfernen und die Arraylänge zu beschränken.

In keinem Fall wird ein implizites cast durchgeführt: die Arrayelement haben denselben Typen wie der <Ausdruck>. Die Anwendung kann die Werte also Typensicher übernehmen und gegebenenfalls formatieren.

Array_agg entfernt keine null-Werte wie es andere Aggregatfunktionen (auch listagg) tun.11. Durch die Typensicherheit von Arrays können null-Werte unverwechselbar an die Anwendung übergeben werden.

Mit der filter-Klausel können null-Werte vor der Aggregierung entfernt werden.12. Wenn die filter-Klausel alle Zeilen enfternt, liefert array_agg kein leeres Array, sondern null als Ergebnis.

Bei der Syntax mit der Unterabfrage können null-Werte in der where-Klausel gefiltert werden. Wenn das Ergebnis der Unterabfrage leer ist, ensteht ein leeres Array.

Wenn die Reihenfolge der Element nicht relevant ist, kann man mit collect aucht ein multiset aufbauen und typensicher an eine Anwedung übergeben.

Dokument-Typen

Analog zu array_agg definiert der SQL Standard Aggregatfunktionen die ein JSON oder XML-Fragment erzeugen: json_arrayagg und xmlagg. Der Hauptvorteil gegenüber listagg ist, dass diese Funktionen die entsprechenden Escape-Sequenzen verwenden.

JSON_ARRAYAGG(<Ausdruck> ORDER BY … [NULL ON NULL])
XMLAGG(XMLELEMENT(NAME <Elementname>, <Ausdruck>) ORDER BY …)

Warnung

Zahlreiche Artikel zeigen, wie man ein solches Dokument mit SQL Stringfunktionen in eine komma-separierte Liste überführt. Dabei wird häufig außer acht gelassen, dass das serialisierte JSON oder XML-Dokument Escape-Sequenzen beinhalten könnte, die zurückübersetzt werden müssen (z. B. &lt; in XML oder \" in JSON).

Mittels with recursive

Beachte

Obwohl es grundsätzlich möglich ist, die listagg Funktionalität mittels with recursive umzusetzen, sind Arrays, Dokumente oder die unten gezeigten propritären Alternativen zu listagg oft die bessere Wahl.

Der folgende Spezialfall kann alleine durch with recursive und intermediate SQL-92 umgesetzt werden:

LISTAGG(DISTINCT <Ausdr1>, <Sep> …) WITHIN GROUP(ORDER BY <Ausdr1>)

Beachte das distinct und, dass <Ausdr1> in beiden Fällen derselbe Ausdruck ist.

Das folgende Beispiel verwendet g als group by-Schlüssel, val als <Ausdr1> und ', ' als <Sep>:

WITH RECURSIVE
list_agg(g, val, list)
AS (
    SELECT g, min(val), CAST(null AS VARCHAR)
      FROM listagg_demo
     GROUP BY g
 UNION ALL
    SELECT prev.g
         , (SELECT min(val)
              FROM listagg_demo this
             WHERE this.g   = prev.g
               AND this.val > prev.val
           ) val
         , COALESCE(list || ', ', '') || val
      FROM list_agg prev
     WHERE prev.val IS NOT NULL
)
SELECT g, list
  FROM list_agg
 WHERE val IS NULL
 ORDER BY g

Diese spezielle Umseztung verwendet die “loose index scan”-Technik, die im PostgreSQL Wiki erklärt wird. Auch mit einem Index auf (g, val) bleibt die Performance dieser Technik für diese Anwendung sehr gering. Das distinct-Verhalten ist ein Nebeneffekt dieser Technik.

Die korrekte behandlung von null ist bei dieser Umsetzung ein wichtiger Spezialfall: obwohl null bei Aggregierungen generell ignoriert wird, muss eine Gruppe die nur null-Werte beinhaltet trozdem im Ergebnis aufscheinen. Daher darf null nicht entfernt werden, wenn es in dieser Gruppe keine nicht-null-Werte gibt. Die obere Umsetzung verwendet daher min(val) im nicht-rekursiven Zweig um dieses Verhalten zu erreichen.13

Eine leistungsfähigere Umsetzung, die all-Verhalten und beliebige order by-Klauseln unterstützt, ist mit with recursive in Kombination mit Window-Funtionen möglich. Aaron Bertrand's Artikel “Grouped Concatenation in SQL Server” zeigt ein Besipiel für diesen Ansatz.

In jedem Fall kann ein beliebiges on overflow verhalten umgesetzt werden.14

Proprietäre Erweiterungen

Die einzig nützliche und weit verbreitete Erweiterung ist, dass der Separator über einen Bind-Parameter übergeben werden kann.

Laut Standard ist sowohl der <Separator>, als auch die within group-Klausel zwingend erforderlich. Dennoch sind diese Teile bei manchen Datenbanken optional. Das weglassen führt teils zu Systemspezifischen verhalten oder gar undefiniertem Verhalten.

Propreritäre Alternativen

Es gibt zwei gängige proprietäre Alternativen zu listagg: group_concat und string_agg. Jede dieser Funktionen wird von mindestens zwei Datenbanken unterstüzt. Die proprietären Funktionen verwenden jedoch eine uneinheitliche Sytnax, auch wenn mehere Datenbanken denselben Funktionsnamen verwenden.

Die gute Nachricht ist, dass das Verhalten der proprietären Funktionen dem Default-Verhalten von listagg entspricht: null-Werte werden entfernt, Duplikate aber nicht (all-Semantik).

string_agg — PostgreSQL Syntax

PostgreSQL's string_agg unterstützt distinct und verwendet die array_agg-Syntax für die order by-Klausel:

STRING_AGG([ALL|DISTINCT] <Ausdruck>, <Separator> [ORDER BY …])

PostgreSQL bietet auch eine proprietäre Funktion an, um ein Array in einen String zu konvertieren: array_to_string.

string_agg — SQL Server Syntax (vNext/14.0)

SQL Server's string_agg unterstützt kein distinct und verwendet die within group für die order by-Klausel:

STRING_AGG(<Ausdruck>, <Separator>) [WITHIN GROUP (ORDER BY …)]

Aaron Bertrand's Artikel “Grouped Concatenation in SQL Server” zeigt viele andere Wege listagg in älteren SQL Server Versionen umzusetzen—auch die sehr beliebte FOR XML PATH-Methode.

group_concat — MySQL Syntax

MySQL's group_concat unterstützt distinct und erwartet die order by-Klausel noch vor dem optionalen Separator (default: Komma)

GROUP_CONCAT([DISTINCT] <Ausdruck>
             ORDER BY …
             [SEPARATOR <Separator>]
            )

MySQL's group_concat schneidet das Ergebnis ab, wenn es die Länge das einstellbare Maximum überschreitet. Dabei wird keine Rücksicht auf Elementgrenzen gelegt: es kann also durchaus mitten in einem Element abgeschnitten werden.

group_concat — SQLite Syntax

SQLite's group_concat unterstützt werder distinct noch order by:

GROUP_CONCAT(<expression>, <separator>)

User-Defined Aggregates

Einige Datenbanken erlauben den Benuzer eigene Aggregatfunktionen zu erstellen: MySQL (nur in C/C++), Oracle Database (auch WM_CONCAT), PostgreSQL, SQL Server (über CLR).

Über den Autor

Foto von Markus Winand

Markus Winand lehrt effizientes SQL – inhouse und online. Er minimiert die Entwicklungszeit durch modernes SQL und optimiert die Laufzeit durch schlaue Indizierung – dazu hat er auch das Buch SQL Performance Explained veröffentlicht.

Kaufen Sie sein Buch bei Amazon

Titelbild von „SQL Performance Explained“: Eichhörnchen läuft durchs Grass

Die Essenz: SQL-Tuning auf 200 Seiten

Bei Amazon kaufen
(Taschenbuch)

Taschenbuch und PDF auch auf Markus' Webseite erhältlich.

Holen Sie sich Markus

…für ein Training ins Büro.

Sein beliebtes Training stimmt Entwickler auf SQL Performance ein.

Erfahren Sie mehr»

Fußnoten

0

SQL:2016-2: §10.9, Syntax Rule 11a.

1

SQL:2016-2: §10.9 <listagg separator> ::= <character string literal>. <dynamic parameter specification> ist nicht erlaubt.

2

SQL:2016-2: §10.9, General Rule 11a.

3

SQL:2016-2: §10.9, Syntax Rule 11d.

4

DB2 LUW uses varchar, Oracle varchar2.

5

SQL:2016-2: §10.9, General Rule 11gii and SQL:2016-2, §24.1

6

Omission in SQL:2016-2: §10.9, Syntax Rule 11giii. Warning — string data, right truncation (SQLSTATE 01004) would be an appropriate condition.

7

Ein uverwechselbarer <Fueller> macht das einfacher.

8

SUM(CHAR_LENGTH(<Ausdruck>)) + (COUNT(<Ausdruck>)-1) * CHAR_LENGTH(<Separator>)

9

Eine Lücke in SQL:2016-2: §10.9, General Rule 11bi and 11c

10

SQL:2016-2: §6.10, Syntax Rule 12

11

SQL:2016-2: §10.9, General Rule 12gii, explicitly in note 510

13

Alternativ kann man order by val nulls last mit fetch first 1 row only verwenden. Das wird jedoch von weniger Datenbanken unterstützt. Wenn man weiß, dass val nie null ist, kann die nulls last-Klausel wegelassen werden.

14

Die systemspezifische Maximallänge von varchar muss dabei händisch berüsichtigt werden.

„modern SQL“ von Markus Winand ist unter einer Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License lizenziert.
Impressum | Kontakt | KEINE GEWÄHR | Handelsmarken | Datenschutz | CC-BY-NC-ND 3.0 Lizenz