Die Funktion listagg fügt Werte aus einer Gruppe von Zeilen zu einem String mit konfigurierbarem Separator zusammen. Listagg wird häufig zur denormalisierung 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 Separator nicht anzusehen, ob er zwei Werte trennt, oder Teil eines Wertes ist. Die sichere Nutzung von listagg für den elektronischen Datenaustausch ist daher auf Fälle beschränkt in denen ein unverwechselbarer Separator gewählt werden kann: z. B. wenn die Werte nur Zahlen, Datums- oder Zeitangaben oder Strings, die den Separator nicht enthalten können sind.
Für die Umsetzung eines elektronischen 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 meisten anderen Aggregatfunktionen entfernt 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 so viele 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] COUNTDer optionale <Fueller> (Default: drei Punkte ...) 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örter 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 beibehalten werden soll.
Das folgende Beispiel zeigt diesen Ansatz. Die Spalten 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(val, ',') WITHIN GROUP (ORDER BY o) list
FROM (SELECT g, min(o) o, val
FROM dist_listagg
GROUP BY g, val
) dt
GROUP BY gListagg 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 verwenden, 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 Datenbanken eine ähnliche Funktionalität mit proprietärer Syntax an.
- Seit 12.2
- Wenn nach den Werten sortiert wird:
listagg(distinct X,…) within group (order by X) SQLSTATE 54006SQLSTATE 72000
Hinweis in eigener Sache
Ich biete SQL Schulungen, Optimierung und Beratung an. Auch der Kauf meines Buches „SQL Performance Explained“ (ab €9,95) unterstützt meine Arbeit an dieser Webseite.
Standard-Konforme Alternativen
- Ohne
Order By-Klausel - Ohne
Order By-Klausel • Auch in 5.7.22 - Proprietäre Syntax verfügbar:
json_agg - Proprietäre Syntax verfügbar:
FOR JSON AUTO - Proprietäre Syntax verfügbar:
json_group_array(erfordert die JSON1 Erweiterung) - Proprietäre Syntax verfügbar:
FOR XML PATH - Wenn man das
recursive-Schlüsselwort weglässt
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 entfernt, 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, entsteht ein leeres Array.
Wenn die Reihenfolge der Element nicht relevant ist, kann man mit collect aucht ein multiset aufbauen, und typensicher an eine Anwendung ü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. < 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 proprietä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(255))
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 gDiese spezielle Umsetzung 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 trotzdem 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 das 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 Beispiel 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ützt. Die proprietären Funktionen verwenden jedoch eine uneinheitliche Syntax, auch wenn mehrere 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).
- Mit optionaler
Limit-Klausel:string_agg(<e>, <s> ORDER BY … LIMIT <n>)
string_agg — PostgreSQL und Google BigQuery Syntax
Die Funktion String_agg nach dem Vorbild PostgreSQL unterstützt Distinct und verwendet die Array_agg-Syntax für die Order by-Klausel:
STRING_AGG([ALL|DISTINCT] <Ausdruck>, <Separator> [ORDER BY …])Google BigQuery unterstützt darüber hinaus die optionale Limit-Klausen nach dem 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 (seit Version 2017/V14)
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 und MariaDB Syntax
Group_concat in MySQL und MariaDB unterstützt distinct und erwartet die order by-Klausel noch vor dem optionalen Separator (Default: Komma).
GROUP_CONCAT([DISTINCT] <Ausdruck>
ORDER BY …
[SEPARATOR <Separator>]
[LIMIT <n>] -- MariaDB 10.3+
)Dabei schneidet group_concat das Ergebnis ab, wenn es die Länge das einstellbare Maximum überschreitet. Elementgrenzen werden dabei nicht berücksichtigt: es kann also durchaus mitten in einem Element abgeschnitten werden.
MariaDB unterstützt diese Syntax ebenfalls und bietet darüber hinaus seit Version 10.3 die optionale Limit-Klausel in group_concat an.
group_concat — SQLite Syntax
SQLite’s group_concat unterstützt zwar distinct, nicht aber order by:
GROUP_CONCAT([DISTINCT] <expression>, <separator>)User-Defined Aggregates
Einige Datenbanken erlauben den Benutzer eigene Aggregatfunktionen zu erstellen: MySQL (nur in C/C++), Oracle Database (auch WM_CONCAT), PostgreSQL, SQL Server (über CLR).
