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> ) ]
Copy

Fonction de fenêtre

LISTAGG( [ DISTINCT ] <expr1> [, <delimiter> ] )
    [ WITHIN GROUP ( <orderby_clause> ) ]
    OVER ( [ PARTITION BY <expr2> ] )
Copy

Arguments

Obligatoire :

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 indique que la fonction est utilisée en tant que fonction de fenêtre. Pour plus de détails, voir Fonctions de la fenêtre.

Facultatif :

DISTINCT

Supprime les valeurs en double de la liste.

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.

WITHIN GROUP orderby_clause

Clause contenant 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 clause principale ORDER BY d’une instruction SELECT. Voir ORDER BY.

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 Fonctions de la fenêtre.

Renvoie

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

Notez que cette fonction ne renvoie pas une « liste » ou un 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.

  • 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 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 'sp')
        FROM table1
        ORDER BY last_name;
    
    Copy

Exemples

SELECT LISTAGG(O_ORDERKEY, ' ')
    FROM orders WHERE O_TOTALPRICE > 450000;

---------------------------------------------+
          LISTAGG(O_ORDERKEY, ' ')           |
---------------------------------------------+
 41445 55937 67781 80550 95808 101700 103136 |
---------------------------------------------+
Copy
SELECT LISTAGG(DISTINCT O_ORDERSTATUS, '|')
    FROM orders WHERE O_TOTALPRICE > 450000;

--------------------------------------+
 LISTAGG(DISTINCT O_ORDERSTATUS, '|') |
--------------------------------------+
 F|O                                  |
--------------------------------------+
Copy
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 |
---------------+--------------------------------------------------------------------+
Copy

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