Catégories :

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

ARRAY_AGG

Renvoie les valeurs d’entrée, pivotées dans un tableau. Si l’entrée est vide, la fonction renvoie un tableau vide.

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> ] [ ORDER BY <expr3> [ { ASC | DESC } ] ] [ <window_frame> ] )
Copy

Arguments

Obligatoire :

expr1

Expression (généralement un nom de colonne) qui détermine les valeurs à insérer dans le tableau.

OVER()

La clause OVER indique que la fonction est utilisée en tant que fonction de fenêtre. Pour plus de détails, voir Syntaxe et utilisation des fonctions de fenêtre.

Facultatif :

DISTINCT

Supprime les valeurs dupliquées du tableau.

WITHIN GROUP orderby_clause

Clause contenant une ou plusieurs expressions (généralement des noms de colonnes) qui déterminent l’ordre des valeurs dans chaque tableau.

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

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 Syntaxe et utilisation des fonctions de fenêtre.

ORDER BY expr3 [ { ASC | DESC } ] [ {window_frame} ]

Expression facultative à partir de laquelle effectuer le classement dans chaque partition, suivie d’un cadre de fenêtre facultatif. Pour plus de détails sur la syntaxe window_frame, voir Syntaxe et utilisation des fonctions de fenêtre.

Lorsque cette fonction est utilisée avec un cadre basé sur une plage, la clause ORDER BY ne prend en charge qu’une seule colonne. Les cadres basés sur des lignes ne sont pas soumis à cette restriction.

LIMIT n’est pas pris en charge.

Renvoie

Renvoie une valeur de type ARRAY.

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

Notes sur l’utilisation

  • Si vous ne spécifiez pas WITHIN GROUP(ORDER BY), 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 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 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

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

  • DISTINCT et WITHIN GROUP sont pris en charge pour les appels de fonctions de fenêtre uniquement lorsqu’il n’y a pas de clause ORDER BY à l’intérieur de la clause OVER. Lorsqu’une clause ORDER BY est utilisée dans la clause OVER, les valeurs du tableau de sortie suivent le même ordre par défaut (c’est-à-dire l’ordre équivalent à WITHIN GROUP (ORDER BY expr3)).

  • Les valeurs NULL sont omises de la sortie.

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’une requête 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

L’exemple suivant utilise un ensemble de données différent. La fonction ARRAY_AGG est appelée en tant que fonction de fenêtre avec un cadre de fenêtre ROWS BETWEEN. Commencez par créer la table et chargez-la avec 14 lignes :

CREATE OR REPLACE TABLE array_data AS (
WITH data AS (
  SELECT 1 a, [1,3,2,4,7,8,10] b
  UNION ALL
  SELECT 2, [1,3,2,4,7,8,10]
  )
SELECT 'Ord'||a o_orderkey, 'c'||value o_clerk, index
  FROM data, TABLE(FLATTEN(b))
);
Copy

À présent, exécutez la requête suivante. Notez que seul un jeu de résultats partiel est présenté ici.

SELECT o_orderkey,
    ARRAY_AGG(o_clerk) OVER(PARTITION BY o_orderkey ORDER BY o_orderkey
      ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) AS result
  FROM array_data;
Copy
+------------+---------+
| O_ORDERKEY | RESULT  |
|------------+---------|
| Ord1       | [       |
|            |   "c1"  |
|            | ]       |
| Ord1       | [       |
|            |   "c1", |
|            |   "c3"  |
|            | ]       |
| Ord1       | [       |
|            |   "c1", |
|            |   "c3", |
|            |   "c2"  |
|            | ]       |
| Ord1       | [       |
|            |   "c1", |
|            |   "c3", |
|            |   "c2", |
|            |   "c4"  |
|            | ]       |
| Ord1       | [       |
|            |   "c3", |
|            |   "c2", |
|            |   "c4", |
|            |   "c7"  |
|            | ]       |
| Ord1       | [       |
|            |   "c2", |
|            |   "c4", |
|            |   "c7", |
|            |   "c8"  |
|            | ]       |
| Ord1       | [       |
|            |   "c4", |
|            |   "c7", |
|            |   "c8", |
|            |   "c10" |
|            | ]       |
| Ord2       | [       |
|            |   "c1"  |
|            | ]       |
| Ord2       | [       |
|            |   "c1", |
|            |   "c3"  |
|            | ]       |
...
Langage: Français