Kategorien:

Aggregatfunktionen (Allgemein), Syntax und Verwendung von Fensterfunktionen (Allgemein)

LISTAGG

Gibt die verketteten Eingabewerte zurück, getrennt durch die delimiter-Zeichenfolge:

Syntax

Aggregatfunktion

LISTAGG( [ DISTINCT ] <expr1> [, <delimiter> ] )
    [ WITHIN GROUP ( <orderby_clause> ) ]
Copy

Fensterfunktionen

LISTAGG( [ DISTINCT ] <expr1> [, <delimiter> ] )
    [ WITHIN GROUP ( <orderby_clause> ) ]
    OVER ( [ PARTITION BY <expr2> ] )
Copy

Erforderliche Argumente

expr1

Ein Ausdruck (normalerweise ein Spaltenname), der über die Werte bestimmt, die in die Liste aufgenommen werden. Der Ausdruck muss als Zeichenfolge ausgewertet werden oder als Datentyp, der in eine Zeichenfolge umgewandelt werden kann.

OVER()

Die OVER-Klausel ist erforderlich, wenn die Funktion als Fensterfunktion verwendet wird. Weitere Details dazu finden Sie unter Syntax und Verwendung von Fensterfunktionen.

Optionale Argumente

DISTINCT

Entfernt doppelte Werte aus der Liste.

delimiter

Eine Zeichenfolge oder ein Ausdruck, der eine Zeichenfolge ergibt. Normalerweise ist dieser Wert eine einstellige Zeichenfolge. Die Zeichenfolge muss in einfache Anführungszeichen gesetzt werden, wie in den folgenden Beispielen veranschaulicht.

Wenn keine delimiter angegeben ist, wird eine leere Zeichenfolge als delimiter verwendet.

delimiter muss eine Konstante sein.

WITHIN GROUP orderby_clause

ein oder mehrere Ausdrücke (typischerweise Spaltennamen) enthält, die die Reihenfolge der Werte für jede Gruppe in der Liste bestimmen.

Die Syntax WITHIN GROUP (ORDER BY) unterstützt dieselben Parameter wie die ORDER BY-Klausel in einer SELECT-Anweisung.

PARTITION BY expr2

Unterklausel der Fensterfunktion, die einen Ausdruck (normalerweise einen Spaltennamen) angibt. Dieser Ausdruck definiert Partitionen, die die Eingabezeilen gruppieren, bevor die Funktion angewendet wird. Weitere Details dazu finden Sie unter Syntax und Verwendung von Fensterfunktionen.

Rückgabewerte

Gibt eine Zeichenfolge zurück, die alle Nicht-NULL-Eingabewerte enthält, getrennt durch das delimiter.

Diese Funktion gibt weder eine Liste noch ein Array zurück. Gibt eine einzelne Zeichenfolge zurück, die alle Nicht-NULL-Eingabewerte enthält.

Nutzungshinweise

  • Wenn Sie WITHIN GROUP(ORDER BY) nicht angeben, ist die Reihenfolge der Elemente in jeder Liste unvorhersehbar. (Eine ORDER BY-Klausel außerhalb der WITHIN GROUP-Klausel wird auf die Reihenfolge der Ausgabezeilen angewendet, jedoch nicht auf die Reihenfolge der Listenelemente innerhalb einer Zeile.)

  • Wenn Sie für einen Ausdruck in WITHIN GROUP (ORDER BY) eine Zahl angeben, wird diese Zahl als numerische Konstante geparst, nicht als Ordinalposition einer Spalte in der SELECT-Liste. Geben Sie daher keine Zahlen als WITHIN GROUP (ORDER BY)-Ausdrücke an.

  • Wenn Sie DISTINCT und WITHIN GROUP angeben, müssen sich beide auf die gleiche Spalte beziehen. Beispiel:

    SELECT LISTAGG(DISTINCT O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERKEY) ...;
    Copy

    Wenn Sie unterschiedliche Spalten für DISTINCT und WITHIN GROUP angeben, tritt ein Fehler auf:

    SELECT LISTAGG(DISTINCT O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERSTATUS) ...;
    Copy
    SQL compilation error: [ORDERS.O_ORDERSTATUS] is not a valid order by expression

    Sie müssen entweder die gleiche Spalte für DISTINCT und WITHIN GROUP angeben oder DISTINCT weglassen.

  • In Bezug auf NULL oder leere Eingabewerte:

    • Wenn die Eingabe leer ist, wird eine leere Zeichenfolge zurückgegeben.

    • Wenn alle Eingabeausdrücke NULL ergeben, ist die Ausgabe eine leere Zeichenfolge.

    • Wenn einige, aber nicht alle Eingabeausdrücke NULL ergeben, enthält die Ausgabe alle Nicht-NULL-Werte, während alle NULL-Werte ausgeschlossen werden.

  • Wenn diese Funktion als Fensterfunktion aufgerufen wird, wird sie nicht unterstützt:

    • Eine ORDER BY-Klausel innerhalb der OVER-Klausel.

    • Explizite Fensterrahmen.

Sortierungsdetails

  • The collation of the result is the same as the collation of the input.

  • Die Elemente innerhalb der Liste sind nach Sortierungen geordnet, wenn die Unterklausel ORDER BY einen Ausdruck mit Sortierung angibt.

  • Das delimiter kann keine Sortierungsspezifikation verwenden.

  • Das Angeben von Sortierung in ORDER BY hat keinen Einfluss auf die Sortierung des Ergebnisses. Die folgende Anweisung enthält beispielsweise zwei ORDER BY-Klauseln, eine für LISTAGG und eine für die Abfrageergebnisse. Das Festlegen der Sortierung innerhalb der ersten Klausel hat keinen Einfluss auf die Sortierung der zweiten. Wenn Sie die Ausgabe in beiden ORDER BY-Klauseln sortieren möchten, müssen Sie Sortierung in beiden Klauseln explizit angeben.

    SELECT LISTAGG(x, ', ') WITHIN GROUP (ORDER BY last_name COLLATE 'es')
  FROM table1
  ORDER BY last_name;
    Copy

Beispiele

Diese Beispiele verwenden die Funktion LISTAGG.

Verwendung der LISTAGG-Funktion zur Verkettung von Werten in Abfrageergebnissen

In den folgenden Beispielen wird die Funktion LISTAGG verwendet, um Werte in den Ergebnissen von Abfragen zu Bestelldaten zu verketten.

Bemerkung

Diese Beispiele fragen die TPC-H-Beispieldaten ab. Bevor Sie die Abfragen ausführen, führen Sie die folgende SQL-Anweisung aus:

USE SCHEMA snowflake_sample_data.tpch_sf1;
Copy

Dieses Beispiel listet die unterschiedlichen o_orderkey-Werte für Bestellungen auf, deren o_totalprice größer als 520000 ist, und verwendet eine leere Zeichenfolge für delimiter:

SELECT LISTAGG(DISTINCT o_orderkey, ' ')
  FROM orders
  WHERE o_totalprice > 520000;
Copy
+-------------------------------------------------+
| LISTAGG(DISTINCT O_ORDERKEY, ' ')               |
|-------------------------------------------------|
| 2232932 1750466 3043270 4576548 4722021 3586919 |
+-------------------------------------------------+

Dieses Beispiel listet die unterschiedlichen Werte von o_orderstatus für Bestellungen auf, bei denen o_totalprice größer als 520000 ist, und verwendet einen vertikalen Balken für delimiter:

SELECT LISTAGG(DISTINCT o_orderstatus, '|')
  FROM orders
  WHERE o_totalprice > 520000;
Copy
+--------------------------------------+
| LISTAGG(DISTINCT O_ORDERSTATUS, '|') |
|--------------------------------------|
| O|F                                  |
+--------------------------------------+

Dieses Beispiel listet die Werte von o_orderstatus und o_clerk für jede Bestellung mit einem o_totalprice größer als 520000 gruppiert nach o_orderstatus auf. Die Abfrage verwendet ein Komma als delimiter:

SELECT o_orderstatus,
   LISTAGG(o_clerk, ', ')
     WITHIN GROUP (ORDER BY o_totalprice DESC)
  FROM orders
  WHERE o_totalprice > 520000
  GROUP BY o_orderstatus;
Copy
+---------------+---------------------------------------------------+
| O_ORDERSTATUS | LISTAGG(O_CLERK, ', ')                            |
|               |      WITHIN GROUP (ORDER BY O_TOTALPRICE DESC)    |
|---------------+---------------------------------------------------|
| O             | Clerk#000000699, Clerk#000000336, Clerk#000000245 |
| F             | Clerk#000000040, Clerk#000000230, Clerk#000000924 |
+---------------+---------------------------------------------------+

Verwendung der Sortierung mit der Funktion LISTAGG

Die folgenden Beispiele zeigen die Sortierung mit der Funktion LISTAGG. Die Beispiele verwenden die folgenden Daten:

CREATE OR REPLACE TABLE collation_demo (
  spanish_phrase VARCHAR COLLATE 'es');
Copy
INSERT INTO collation_demo (spanish_phrase) VALUES
  ('piña colada'),
  ('Pinatubo (Mount)'),
  ('pint'),
  ('Pinta');
Copy

Beachten Sie bei den verschiedenen Sortierungsspezifikationen den Unterschied in der Ausgabereihenfolge. Diese Abfrage verwendet die Sortierungsspezifikation es:

SELECT LISTAGG(spanish_phrase, '|')
    WITHIN GROUP (ORDER BY COLLATE(spanish_phrase, 'es')) AS es_collation
  FROM collation_demo;
Copy
+-----------------------------------------+
| ES_COLLATION                            |
|-----------------------------------------|
| Pinatubo (Mount)|pint|Pinta|piña colada |
+-----------------------------------------+

Diese Abfrage verwendet die Sortierungsspezifikation utf8:

SELECT LISTAGG(spanish_phrase, '|')
    WITHIN GROUP (ORDER BY COLLATE(spanish_phrase, 'utf8')) AS utf8_collation
  FROM collation_demo;
Copy
+-----------------------------------------+
| UTF8_COLLATION                          |
|-----------------------------------------|
| Pinatubo (Mount)|Pinta|pint|piña colada |
+-----------------------------------------+
Sprache: Deutsch