Die Fensterfunktion LISTAGG

Die Fensterfunktion „LISTAGG“ ordnet die Zeilen der Gruppe in einer Abfrage nach dem ORDER BY-Ausdruck an. Anschließend werden die Werte zu einer einzigen Zeichenfolge verkettet.

Syntax

LISTAGG( [DISTINCT] expression [, 'delimiter' ] ) 
[ WITHIN GROUP (ORDER BY order_list) ] 
OVER ( [PARTITION BY partition_expression] )     

Argumente

DISTINCT

(Optional) Eine Klausel, die duplizierte Werte in dem angegebenen Ausdruck beseitigt, bevor die Verkettung vorgenommen wird. Leerzeichen am Ende werden ignoriert, sodass beispielsweise die Zeichenfolgen 'a' und 'a ' als duplizierte Werte behandelt werden würden. „LISTAGG“ verwendet den ersten registrierten Wert. Weitere Informationen finden Sie unter Die Bedeutung von Leerzeichen am Ende.

aggregate_expression

Ein gültiger Ausdruck (beispielsweise ein Spaltenname), der die Werte bereitstellt, die aggregiert werden sollen. NULL-Werte und leere Zeichenfolgen werden ignoriert.

delimiter

(Optional) Die Zeichenfolgenkonstante, die die verketteten Werte trennt. Der Standardwert ist „NULL“.

WITHIN GROUP (ORDER BY order_list)

(Optional) Eine Klausel, die die Sortierreihenfolge der aggregierten Werte angibt. Dies ist nur dann deterministisch, wenn ORDER BY eine spezifische Reihenfolge bereitstellt. Das Standardverhalten besteht darin, alle Zeilen zu aggregieren und einen einzelnen Wert zurückzugeben.

OVER

Eine Klausel, die die Fensterpartitionierung angibt. Die OVER-Klausel darf keine Spezifikation für Fensteranordnungen oder Fensterrahmen enthalten.

PARTITION BY partition_expression

(Optional) Legt den Datensatzbereich für die einzelnen Gruppen in der OVER-Klausel fest.

Rückgabewert

VARCHAR(MAX). Wenn der Ergebnissatz größer als die maximal zulässige Größe von VARCHAR ist (64.000 – 1 oder 65535), gibt LISTAGG den folgenden Fehler zurück:

Invalid operation: Result size exceeds LISTAGG limit

Beispiele

Im folgenden Beispiel wird die Tabelle WINSALES verwendet. Eine Beschreibung der Tabelle WINSALES finden Sie unter Beispieltabelle mit Beispielen von Fensterfunktionen.

Im folgenden Beispiel wird eine Liste von Verkäufer-IDs zurückgegeben, geordnet nach Verkäufer-ID.

select listagg(sellerid) 
within group (order by sellerid)
over() from winsales;

  listagg
------------
 11122333344
...
...
 11122333344
 11122333344
   (11 rows)   

Im folgenden Beispiel wird eine Liste von Verkäufer-IDs für Verkäufer B zurückgegeben, geordnet nach Datum.

select listagg(sellerid) 
within group (order by dateid)
over () as seller
from winsales
where buyerid = 'b' ;

  seller
---------
    3233
    3233
    3233
    3233

Im folgenden Beispiel wird eine durch Komma getrennte Liste von Verkaufsterminen für Käufer B zurückgegeben.

select listagg(dateid,',') 
within group (order by sellerid desc,salesid asc)
over () as dates
from winsales
where buyerid  = 'b';

             dates                                      
-------------------------------------------
2003-08-02,2004-04-18,2004-04-18,2004-02-12
2003-08-02,2004-04-18,2004-04-18,2004-02-12
2003-08-02,2004-04-18,2004-04-18,2004-02-12
2003-08-02,2004-04-18,2004-04-18,2004-02-12

Im folgenden Beispiel wird mit „DISTINCT“ eine Liste von einzigartigen Verkaufsterminen für Käufer B zurückgegeben.

select listagg(distinct dateid,',') 
within group (order by sellerid desc,salesid asc)
over () as dates
from winsales
where buyerid  = 'b';

           dates
--------------------------------
2003-08-02,2004-04-18,2004-02-12
2003-08-02,2004-04-18,2004-02-12
2003-08-02,2004-04-18,2004-02-12
2003-08-02,2004-04-18,2004-02-12

Im folgenden Beispiel wird eine durch Komma getrennte Liste von Verkaufs-IDs für die einzelnen Käufer-IDs zurückgegeben.

select buyerid, 
listagg(salesid,',')
within group (order by salesid)
over (partition by buyerid) as sales_id
from winsales
order by buyerid;

+---------+-------------------------+
| buyerid |        sales_id         |
+---------+-------------------------+
| a       | 10005,40001,40005       |
| a       | 10005,40001,40005       |
| a       | 10005,40001,40005       |
| b       | 20001,30001,30003,30004 |
| b       | 20001,30001,30003,30004 |
| b       | 20001,30001,30003,30004 |
| b       | 20001,30001,30003,30004 |
| c       | 10001,10006,20002,30007 |
| c       | 10001,10006,20002,30007 |
| c       | 10001,10006,20002,30007 |
| c       | 10001,10006,20002,30007 |
+---------+-------------------------+