- Catégories :
Fonctions d’agrégation (Général) , Fonctions de la fenêtre (Général)
LISTAGG¶
Renvoie les valeurs d’entrée concaténées, séparées par la chaîne delimiter
.
Syntaxe¶
Fonction d’agrégation
LISTAGG( [ DISTINCT ] <expr1> [, <delimiter> ] ) [ WITHIN GROUP ( <orderby_clause> ) ]
Fonction de fenêtre
LISTAGG( [ DISTINCT ] <expr1> [, <delimiter> ] )
[ WITHIN GROUP ( <orderby_clause> ) ]
OVER ( [ PARTITION BY <expr2> ] )
Arguments¶
expr1
L’expression (généralement un nom de colonne) qui détermine les valeurs à insérer dans la liste. L’expression doit correspondre à une chaîne ou à un type de données pouvant être converti en chaîne.
delimiter
Chaîne ou expression dont le résultat est une chaîne. En pratique, il s’agit généralement d’une chaîne comportant un seul caractère. La chaîne doit être entourée de guillemets simples, comme indiqué dans les exemples ci-dessous.
Si aucune chaîne
delimiter
n’est spécifiée, la chaîne vide est utilisée commedelimiter
.delimiter
doit être une constante.expr2
Cette expression est utilisée pour regrouper les lignes dans des partitions.
orderby_clause
Expression (généralement un nom de colonne) déterminant l’ordre des valeurs dans la liste.
Renvoie¶
Retourne une chaîne qui inclut toutes les valeurs en entrée non NULL, séparées par le delimiter
.
(Notez que cela ne renvoie pas de « liste » (par exemple, cela ne renvoie pas de ARRAY) ; cela renvoie une seule chaîne contenant toutes les valeurs d’entrée autres que NULL.)
Notes sur l’utilisation¶
DISTINCT
est pris en charge pour cette fonction.Si vous ne spécifiez pas
WITHIN GROUP (<clause_grouperpar>)
, l’ordre des éléments dans chaque liste est imprévisible. (Une clauseORDER BY
en dehors de la clause WITHIN GROUP s’applique à l’ordre des lignes de sortie et non à l’ordre des éléments de la liste dans une ligne.)Si vous spécifiez
DISTINCT
etWITHIN GROUP
, les deux doivent faire référence à la même colonne. Par exemple :SELECT listagg(DISTINCT O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERKEY) ...;
Si vous spécifiez des colonnes différentes pour
DISTINCT
etWITHIN GROUP
, une erreur se produit :SELECT listagg(DISTINCT O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERSTATUS) ...;
SQL compilation error: [ORDERS.O_ORDERSTATUS] is not a valid order by expression
Vous devez soit spécifier la même colonne pour
DISTINCT
etWITHIN GROUP
, soit omettreDISTINCT
.En ce qui concerne les valeurs d’entrée NULL ou vides :
Si l’entrée est vide, une chaîne vide est renvoyée.
Si toutes les expressions en entrée correspondent à une valeur NULL, la sortie est une chaîne vide.
Si certaines expressions en entrée, mais pas toutes, correspondent à une valeur NULL, la sortie contient toutes les valeurs non NULL et exclut les valeurs NULL.
Lorsqu’elle est utilisée comme fonction de fenêtre, cette fonction ne prend pas en charge :
Sous-clause ORDER BY dans la clause OVER().
Cadres de fenêtre.
Détails du classement¶
The collation of the result is the same as the collation of the input.
Les éléments de la liste sont classés en fonction des classements, si la sous-clause ORDER BY spécifie une expression avec un classement.
Le
delimiter
ne peut pas utiliser de spécification de classement.La spécification du classement dans ORDER BY n’a pas d’incidence sur le classement du résultat. Par exemple, l’instruction ci-dessous contient deux clauses
ORDER BY
, une pourLISTAGG
et une pour les résultats de la requête. Spécifier le classement dans la première n’affecte pas le classement dans la seconde. Si vous devez classer la sortie dans les deux clauses ORDER BY, vous devez spécifier explicitement le classement dans les deux clauses.select listagg(x, ', ') within group (ORDER BY last_name collate 'sp') from table1 ORDER BY last_name;
Exemples¶
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 |
---------------+--------------------------------------------------------------------+
L’exemple suivant illustre un classement avec LISTAGG
. Notez la différence d’ordre de sortie avec les différentes spécifications de classement.
-- 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 | +-----------------------------------------------------------------+