Fonction de fenêtrage LISTAGG

Pour chaque groupe d’une requête, la fonction de fenêtrage LISTAGG trie les lignes du groupe conformément à l’expression ORDER BY, puis concatène les valeurs en une chaîne unique.

Syntaxe

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

Arguments

DISTINCT

(Facultatif) Clause qui supprime toutes les valeurs en double dans l’expression spécifiée avant de procéder à la concaténation. Les espaces de fin étant ignorés, les chaînes 'a' et 'a ' sont considérées comme doublons. LISTAGG utilise la première valeur rencontrée. Pour plus d'informations, consultez Signification des blancs de fin.

aggregate_expression

Toute expression valide (par exemple, un nom de colonne) qui fournit les valeurs à regrouper. Les valeurs NULL et les chaînes vides sont ignorées.

delimiter

(Facultatif) Constante de chaîne qui sépare les valeurs concaténées. La valeur par défaut est NULL.

WITHIN GROUP (ORDER BY order_list)

(Facultatif) Clause qui spécifie l’ordre de tri des valeurs regroupées. Déterministe uniquement si ORDER BY fournit un ordonnancement unique. La valeur par défaut consiste à regrouper toutes les lignes et à renvoyer une valeur unique.

OVER

Clause qui spécifie le partitionnement de fenêtrage. La clause OVER ne peut pas contenir d’ordre de fenêtrage ou de spécification de cadre de fenêtrage.

PARTITION BY partition_expression

(Facultatif) Définit la plage d’enregistrements de chaque groupe dans la clause OVER.

Renvoie

Si le jeu de résultats est supérieur à 16 000 000 octets, LISTAGG renvoie l'erreur suivante :

Invalid operation: Result size exceeds LISTAGG limit

Exemples

Les exemples suivants utilisent la table WINSALES. Pour obtenir une description de la table WINSALES, consultez Exemple de tableau contenant des exemples de fonctions de fenêtrage.

L'exemple suivant renvoie une liste de vendeurs IDs, classée par numéro de vendeur.

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

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

L'exemple suivant renvoie une liste de vendeurs IDs pour l'acheteur B, classée par date.

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

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

L’exemple suivant renvoie une liste des dates de ventes de l’acheteur B séparées par des barres virgules.

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

L’exemple suivant utilise DISTINCT pour renvoyer une liste de dates de vente uniques pour l’acheteur B.

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

L'exemple suivant renvoie une liste des ventes séparées par des IDs virgules pour chaque numéro d'acheteur.

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 |
+---------+-------------------------+

L'exemple suivant illustre la prise en charge de LISTAGG avec des résultats concaténés allant jusqu'à 16 000 000 octets :

CREATE TABLE large_data (
    id INT,
    content VARCHAR(65535)
);

INSERT INTO large_data VALUES 
    (1, REPEAT('A', 65535)),
    (2, REPEAT('B', 65535)),
    (3, REPEAT('C', 65535));

SELECT LEN(LISTAGG(content, ',') WITHIN GROUP (ORDER BY id)) AS total_length
FROM large_data;

 total_length
--------------
       196607