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. Show
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. SyntaxListagg 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-KlauselDas 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. distinctDie 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. WarnungDer 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 kombinierenListagg 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ätListagg 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. BigQuery Db2 (LUW)bcMariaDBMySQL Oracle DBadPostgreSQL SQL ServerSQLitelistagg(…) within group (…)listagg(… on overflow …)listagg(distinct …)SQLSTATE 22001 on truncatelistagg mit grouping setslistagg… within group… filter…listagg… within group… over…
Standard-Konforme AlternativenBigQueryDb2 (LUW)g MariaDBaMySQLbOracle DBgPostgreSQLcSQL Server dfSQLiteearray_aggarray_agg und filterarray(select …) json_arrayaggxmlagg(… order by …)Mittels with recursive
ArraysWenn 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-TypenAnalog 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 …)WarnungZahlreiche 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 recursiveDer 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 ErweiterungenDie 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. BigQueryDb2 (LUW)MariaDBMySQLOracle DBPostgreSQLSQL ServerSQLitebind/konst. Ausdr. in <separator>listagg ohne <separator>listagg ohne within groupPropreritäre AlternativenEs 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). BigQuery aDb2 (LUW)MariaDBMySQLOracle DBPostgreSQLSQL ServerSQLitestring_agg(e, s ORDER BY …)string_agg(e, s) WITHIN GROUP (…)group_concat(… ORDER BY … SEPARATOR …)group_concat(e, s)
string_agg — PostgreSQL und Google BigQuery SyntaxDie 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 SyntaxGroup_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 SyntaxSQLite’s group_concat unterstützt zwar distinct, nicht aber order by: GROUP_CONCAT([DISTINCT] <expression>, <separator>)User-Defined AggregatesEinige Datenbanken erlauben den Benutzer eigene Aggregatfunktionen zu erstellen: MySQL (nur in C/C++), Oracle Database (auch WM_CONCAT), PostgreSQL, SQL Server (über CLR). Wie funktioniert group by SQL?Die Gruppierung funktioniert in SQL mit dem Schlüsselwort GROUP BY. Im SELECT können, wenn ein GROUP BY verwendet wird, nur mehr Spalten angegeben werden, nach denen in der GROUP BY-Klausel gruppiert wurde, außer, die Spalten werden innerhalb einer Aggregatfunktion verwendet.
Was bewirkt group by?Mittels GROUP BY kann man Zeilen gruppieren und mit den SQL Funktionen weitere Berechnungen durchführen. Mit „GROUP BY question_id“ werden die gleichen ID's miteinander gruppiert.
Welche SQL Befehle gibt es?SQL-Befehle werden in drei Klassen unterteilt:. DDL – Data Definition Language. Befehle zur Definition von Tabellen und anderer Datenstrukturen.. DCL – Data Control Language. Befehle zur Kontrolle der Zugriffberechtigungen.. DML – Data Manipulation Language. Befehle zur Datenmanipulation und Datenabfrage.. Was macht Sum SQL?Die SQL SUM Funktion summiert die Werte einer Tabellenspalte und liefert sie zurück. Die SQL SUM Funktion ist aus den Unternehmen nicht mehr wegzudenken, da besonders in den Abteilungen des Vertriebs- und Finanz-Controlling Summenwerte mittels der SUM Funktion erstellt werden müssen.
|
Wie nennt man die SQL Funktionen die die Werte aus mehreren Zeilen zusammen fassen und daraus ein Ergebnis berechnen?
Urheberrechte © © 2024 paraquee Inc.
