LISTAGG¶

Syntax¶

Aggregatfunktion

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

Fensterfunktionen

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

Argumente¶

Benötigt:

expr1

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

OVER()

Die OVER-Klausel gibt an, dass die Funktion als Fensterfunktion verwendet wird. Weitere Details dazu finden Sie unter Fensterfunktionen.

Optional:

DISTINCT

Entfernt doppelte Werte aus der Liste.

delimiter

Eine Zeichenfolge oder ein Ausdruck, der eine Zeichenfolge ergibt. In der Praxis handelt es sich dabei meist um eine Zeichenfolge mit einem Zeichen. Die Zeichenfolge muss in einfache Anführungszeichen gesetzt werden, wie in den folgenden Beispielen veranschaulicht.

Wenn keine delimiter-Zeichenfolge angegeben ist, wird die leere Zeichenfolge für delimiter verwendet.

delimiter muss eine Konstante sein.

WITHIN GROUP orderby_clause

Klausel, die einen 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 die gleichen Parameter wie die ORDER BY-Hauptklausel in einer SELECT-Anweisung. Siehe ORDER BY.

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 Fensterfunktionen.

Rückgabewerte¶

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

Beachten Sie, dass diese Funktion keine „Liste“ und kein Array zurückgibt, sondern eine einzige Zeichenfolge zurückgegeben wird, 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.

• Bei NULL- oder leeren Eingabewerten:

  • 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.

• Bei Verwendung als Fensterfunktion unterstützt diese Funktion Folgendes nicht:

  • ORDER BY-Unterklausel in der OVER()-Klausel

  • Fensterrahmen

Sortierungsdetails¶

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

• Elemente in der Liste werden nach Sortierungen sortiert, wenn in der Unterklausel ORDER BY ein Ausdruck mit Sortierung angegeben wurde.

• 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 'sp')
      FROM table1
      ORDER BY last_name;
  

  Copy

Beispiele¶

SELECT LISTAGG(O_ORDERKEY, ' ')
    FROM orders WHERE O_TOTALPRICE > 450000;

---------------------------------------------+
          LISTAGG(O_ORDERKEY, ' ')           |
---------------------------------------------+
 41445 55937 67781 80550 95808 101700 103136 |
---------------------------------------------+

SELECT LISTAGG(DISTINCT O_ORDERSTATUS, '|')
    FROM orders WHERE O_TOTALPRICE > 450000;

--------------------------------------+
 LISTAGG(DISTINCT O_ORDERSTATUS, '|') |
--------------------------------------+
 F|O                                  |
--------------------------------------+

SELECT O_ORDERSTATUS, listagg(O_CLERK, ', ') WITHIN GROUP (ORDER BY O_TOTALPRICE DESC)
    FROM orders WHERE O_TOTALPRICE > 450000 GROUP BY O_ORDERSTATUS;

---------------+--------------------------------------------------------------------+
 O_ORDERSTATUS |  LISTAGG(O_CLERK, ', ') WITHIN GROUP (ORDER BY O_TOTALPRICE DESC)  |
---------------+--------------------------------------------------------------------+
 O             | Clerk#000000220, Clerk#000000411, Clerk#000000114                  |
 F             | Clerk#000000508, Clerk#000000136, Clerk#000000521, Clerk#000000386 |
---------------+--------------------------------------------------------------------+

Das folgende Beispiel zeigt eine Sortierung mit LISTAGG. Beachten Sie bei den verschiedenen Sortierungsspezifikationen den Unterschied in der Ausgabereihenfolge.

-- Collation
SELECT LISTAGG(spanish_phrase, '|') 
        WITHIN GROUP (ORDER BY COLLATE(spanish_phrase, 'sp'))
    FROM collation_demo
    GROUP BY english_phrase;
+---------------------------------------------------------------+
| LISTAGG(SPANISH_PHRASE, '|')                                  |
|         WITHIN GROUP (ORDER BY COLLATE(SPANISH_PHRASE, 'SP')) |
|---------------------------------------------------------------|
| piña colada|Pinatubo (Mount)|pint|Pinta                       |
+---------------------------------------------------------------+
-- Different collation.
SELECT LISTAGG(spanish_phrase, '|') 
        WITHIN GROUP (ORDER BY COLLATE(spanish_phrase, 'utf8'))
    FROM collation_demo
    GROUP BY english_phrase;
+-----------------------------------------------------------------+
| LISTAGG(SPANISH_PHRASE, '|')                                    |
|         WITHIN GROUP (ORDER BY COLLATE(SPANISH_PHRASE, 'UTF8')) |
|-----------------------------------------------------------------|
| Pinatubo (Mount)|Pinta|pint|piña colada                         |
+-----------------------------------------------------------------+

Copy