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¶

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 comme delimiter.

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 clause ORDER 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 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) ...;
  

  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) ...;
  

  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.

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