LISTAGG¶

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 obligatoires¶

expr1

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.

OVER()

La clause OVER est requise lorsque la fonction est utilisée en tant que fonction de fenêtre. Pour plus de détails, voir Syntaxe et utilisation des fonctions de fenêtre.

Arguments facultatifs¶

DISTINCT

Supprime les valeurs en double de la liste.

delimiter

Chaîne ou expression dont le résultat est une chaîne. En règle générale, cette valeur est une chaîne d’un seul caractère. La chaîne doit être entourée de guillemets simples, comme indiqué dans les exemples ci-dessous.

Si aucun delimiter n’est spécifié, une chaîne vide est utilisée comme delimiter.

delimiter doit être une constante.

WITHIN GROUP orderby_clause

Une ou plusieurs expressions (généralement des noms de colonnes) qui déterminent l’ordre des valeurs pour chaque groupe de la liste.

La syntaxe WITHIN GROUP (ORDER BY) prend en charge les mêmes paramètres que la ORDER BY clause d’une instruction SELECT.

PARTITION BY expr2

Sous-clause de fonction de fenêtre qui spécifie une expression (typiquement un nom de colonne). Cette expression définit les partitions qui regroupent les lignes d’entrée avant l’application de la fonction. Pour plus de détails, voir Syntaxe et utilisation des fonctions de fenêtre.

Renvoie¶

Retourne une chaîne qui inclut toutes les valeurs en entrée non NULL, séparées par le delimiter.

Cette fonction ne renvoie pas de liste ni de tableau. Elle renvoie une chaîne unique qui contient toutes les valeurs d’entrée non NULL.

Notes sur l’utilisation¶

• Si vous ne spécifiez pas WITHIN GROUP (ORDER BY), l’ordre des éléments dans chaque liste est imprévisible. (Une clause ORDERBY 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 un nombre pour une expression dans WITHIN GROUP (ORDER BY), ce nombre est analysé comme une constante numérique, et non comme la position ordinale d’une colonne dans la liste SELECT. Par conséquent, ne spécifiez pas les nombres comme des expressions WITHIN GROUP (ORDER BY).

• Si vous spécifiez DISTINCT et WITHIN 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) ...;
  

  Copy

  Si vous spécifiez des colonnes différentes pour DISTINCT et WITHIN GROUP, une erreur se produit :

  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
  

  Vous devez soit spécifier la même colonne pour DISTINCT et WITHIN GROUP, soit omettre DISTINCT.

• Concernant les valeurs d’entrées vides ou NULL :

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

• Lorsque cette fonction est appelée en tant que fonction de fenêtre, elle ne prend pas en charge :

  • Une clause ORDER BY dans la clause OVER.

  • Cadres de fenêtre explicites.

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 une 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 ORDERBY, une pour LISTAGG 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 'es')
    FROM table1
    ORDER BY last_name;
  

  Copy

Exemples¶

Ces exemples utilisent la fonction LISTAGG.

USE SCHEMA snowflake_sample_data.tpch_sf1;

SELECT LISTAGG(DISTINCT o_orderkey, ' ')
  FROM orders
  WHERE o_totalprice > 520000;

+-------------------------------------------------+
| LISTAGG(DISTINCT O_ORDERKEY, ' ')               |
|-------------------------------------------------|
| 2232932 1750466 3043270 4576548 4722021 3586919 |
+-------------------------------------------------+

SELECT LISTAGG(DISTINCT o_orderstatus, '|')
  FROM orders
  WHERE o_totalprice > 520000;

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

SELECT o_orderstatus,
   LISTAGG(o_clerk, ', ')
     WITHIN GROUP (ORDER BY o_totalprice DESC)
  FROM orders
  WHERE o_totalprice > 520000
  GROUP BY o_orderstatus;

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

CREATE OR REPLACE TABLE collation_demo (
  spanish_phrase VARCHAR COLLATE 'es');

INSERT INTO collation_demo (spanish_phrase) VALUES
  ('piña colada'),
  ('Pinatubo (Mount)'),
  ('pint'),
  ('Pinta');

SELECT LISTAGG(spanish_phrase, '|')
    WITHIN GROUP (ORDER BY COLLATE(spanish_phrase, 'es')) AS es_collation
  FROM collation_demo;

+-----------------------------------------+
| ES_COLLATION                            |
|-----------------------------------------|
| Pinatubo (Mount)|pint|Pinta|piña colada |
+-----------------------------------------+

SELECT LISTAGG(spanish_phrase, '|')
    WITHIN GROUP (ORDER BY COLLATE(spanish_phrase, 'utf8')) AS utf8_collation
  FROM collation_demo;

+-----------------------------------------+
| UTF8_COLLATION                          |
|-----------------------------------------|
| Pinatubo (Mount)|Pinta|pint|piña colada |
+-----------------------------------------+