Catégories :

Fonctions d’agrégation (Données semi-structurées) , Fonctions de la fenêtre (Général) , Fonctions de données semi-structurées (Tableau/Objet)

ARRAY_AGG

Renvoie les valeurs d’entrée, pivotées dans un ARRAY. Si l’entrée est vide, un ARRAY vide est renvoyé.

Alias :

ARRAYAGG

Syntaxe

Fonction d’agrégation

ARRAY_AGG( [ DISTINCT ] <expr1> ) [ WITHIN GROUP ( <orderby_clause> ) ]
Copy

Fonction de fenêtre

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

Arguments

  • expr1 Expression (généralement un nom de colonne) qui détermine les valeurs à insérer dans la liste.

  • expr2 Expression (généralement un nom de colonne) qui détermine les partitions dans lesquelles grouper les valeurs.

  • orderby_clause Expression (généralement un nom de colonne) déterminant l’ordre des valeurs dans la liste.

Renvoie

Renvoie une valeur de type ARRAY.

La quantité maximale de données que ARRAY_AGG peut renvoyer pour un seul appel est 16 MB.

Notes sur l’utilisation

  • DISTINCT est pris en charge pour cette fonction.

  • Si vous ne spécifiez pas la WITHIN GROUP (<clause_grouperpar>), l’ordre des éléments dans chaque tableau 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 du tableau 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 array_agg(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 array_agg(DISTINCT O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERSTATUS) ...;
    
    Copy
    SQL compilation error: [ORDERS.O_ORDERSTATUS] is not a valid order by expression
    
    Copy

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

  • Les valeurs NULL sont omises de la sortie.

  • Dans le cas d’une utilisation en tant que fonction de fenêtre :

    • Cette fonction ne prend pas en charge :

      • Sous-clause ORDER BY dans la clause OVER().

      • Cadres de fenêtre.

Exemples

Les exemples de requêtes ci-dessous utilisent les tables et les données présentées ci-dessous :

CREATE TABLE orders (
    o_orderkey INTEGER,         -- unique ID for each order.
    o_clerk VARCHAR,            -- identifies which clerk is responsible.
    o_totalprice NUMBER(12, 2), -- total price.
    o_orderstatus CHAR(1)       -- 'F' = Fulfilled (sent); 
                                -- 'O' = 'Ordered but not yet Fulfilled'.
    );

INSERT INTO orders (o_orderkey, o_orderstatus, o_clerk, o_totalprice) 
  VALUES 
    ( 32123, 'O', 'Clerk#000000321',     321.23),
    ( 41445, 'F', 'Clerk#000000386', 1041445.00),
    ( 55937, 'O', 'Clerk#000000114', 1055937.00),
    ( 67781, 'F', 'Clerk#000000521', 1067781.00),
    ( 80550, 'O', 'Clerk#000000411', 1080550.00),
    ( 95808, 'F', 'Clerk#000000136', 1095808.00),
    (101700, 'O', 'Clerk#000000220', 1101700.00),
    (103136, 'F', 'Clerk#000000508', 1103136.00);
Copy

Cet exemple montre une sortie non pivotée d’un SELECT qui n’utilise pas ARRAY_AGG(). Le contraste au niveau de la sortie entre cet exemple et l’exemple suivant montre que ARRAY_AGG() pivote les données.

SELECT O_ORDERKEY AS order_keys
  FROM orders
  WHERE O_TOTALPRICE > 450000
  ORDER BY O_ORDERKEY;
+------------+
| ORDER_KEYS |
|------------|
|      41445 |
|      55937 |
|      67781 |
|      80550 |
|      95808 |
|     101700 |
|     103136 |
+------------+
Copy

Cet exemple montre comment utiliser ARRAY_AGG() pour faire pivoter une colonne de sortie dans un tableau d’une seule ligne :

SELECT ARRAY_AGG(O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERKEY ASC)
  FROM orders 
  WHERE O_TOTALPRICE > 450000;
+--------------------------------------------------------------+
| ARRAY_AGG(O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERKEY ASC) |
|--------------------------------------------------------------|
| [                                                            |
|   41445,                                                     |
|   55937,                                                     |
|   67781,                                                     |
|   80550,                                                     |
|   95808,                                                     |
|   101700,                                                    |
|   103136                                                     |
| ]                                                            |
+--------------------------------------------------------------+
Copy

Cet exemple montre l’utilisation du mot clé DISTINCT avec ARRAY_AGG().

SELECT ARRAY_AGG(DISTINCT O_ORDERSTATUS) WITHIN GROUP (ORDER BY O_ORDERSTATUS ASC)
  FROM orders 
  WHERE O_TOTALPRICE > 450000
  ORDER BY O_ORDERSTATUS ASC;
+-----------------------------------------------------------------------------+
| ARRAY_AGG(DISTINCT O_ORDERSTATUS) WITHIN GROUP (ORDER BY O_ORDERSTATUS ASC) |
|-----------------------------------------------------------------------------|
| [                                                                           |
|   "F",                                                                      |
|   "O"                                                                       |
| ]                                                                           |
+-----------------------------------------------------------------------------+
Copy

Cet exemple utilise deux clauses ORDER BY distinctes, l’une d’entre elles contrôlant l’ordre dans le tableau de sortie à l’intérieur de chaque ligne et l’autre contrôlant l’ordre des lignes de sortie :

SELECT 
    O_ORDERSTATUS, 
    ARRAYAGG(O_CLERK) WITHIN GROUP (ORDER BY O_TOTALPRICE DESC)
  FROM orders 
  WHERE O_TOTALPRICE > 450000
  GROUP BY O_ORDERSTATUS
  ORDER BY O_ORDERSTATUS DESC;
+---------------+-------------------------------------------------------------+
| O_ORDERSTATUS | ARRAYAGG(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