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 délimiteur.

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.

délimiteur

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 délimiteur n’est spécifiée, la chaîne vide est utilisée comme délimiteur.

délimiteur doit être une constante.

expr2

Cette expression est utilisée pour regrouper les lignes dans des partitions.

clause_grouperpar

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 délimiteur.

(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 (<orderby_clause>), 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 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’il est utilisé comme une 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 délimiteur 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                         |
+-----------------------------------------------------------------+