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