ARRAY_AGG |
Tableau d'agrégation |
|---|---|
| BigQuery | |
Syntaxe
| ARRAY_AGG([DISTINCT] expression [{IGNORE|RESPECT} NULLS] [ORDER BY key [{ASC|DESC}] [, ... ]] [LIMIT n]) [OVER (...)] |
Paramètres
| Nom | Description |
|---|---|
| expression | Ce paramètre permet de spécifier la colonne ou l'expression dont les valeurs seront agrégées dans un tableau. |
| DISTINCT | Ce paramètre facultatif permet d'inclure uniquement les valeurs distinctes de l'expression dans le tableau résultant, en éliminant les doublons. |
| {IGNORE|RESPECT} NULLS | Ce paramètre facultatif permet de contrôler si les valeurs NULL sont incluses ou exclues du tableau résultant lors de l'utilisation de fonctions d'agrégation de tableau comme ARRAY_AGG. Si vous utilisez IGNORE NULLS, les valeurs NULL présentes dans l'expression ne seront pas incluses dans le tableau final. Si vous utilisez RESPECT NULLS, les valeurs NULL seront incluses dans le tableau final. C'est le comportement par défaut si ce paramètre n'est pas spécifié. |
| ORDER BY key [{ASC|DESC}] [, ... ] | Ce paramètre facultatif vous permet de trier les éléments à l'intérieur du tableau résultant. key : C'est la colonne ou l'expression sur laquelle vous souhaitez baser le tri des éléments. {ASC|DESC} : Ce sont des mots-clés facultatifs qui spécifient l'ordre de tri : ASC (Ascendant) : Trie les éléments du plus petit au plus grand (par exemple, 1, 2, 3 ou A, B, C). C'est l'ordre par défaut si ni ASC ni DESC n'est spécifié. DESC (Descendant) : Trie les éléments du plus grand au plus petit (par exemple, 3, 2, 1 ou C, B, A). [, ... ] : Indique que vous pouvez spécifier plusieurs clés de tri. Si les valeurs de la première clé sont identiques, la deuxième clé de tri est utilisée, et ainsi de suite. |
| LIMIT n | Ce paramètre facultatif permet de limiter le nombre maximum d'éléments dans le tableau résultant à n. n doit être une constante de type INT64. |
| OVER (...) | Ce paramètre facultatif permet de spécifier une fenêtre pour la fonction, la transformant en une fonction analytique. Les clauses DISTINCT, IGNORE/RESPECT NULLS, ORDER BY et LIMIT ne peuvent pas être utilisées conjointement avec la clause OVER. |
Description
Cette fonction permet de demander un tableau de valeurs d'une expression spécifié et de retourner le résultat en ARRAY.
Remarques
- La fonction ARRAY_AGG permet d'agréger plusieurs valeurs issues d'une colonne ou d'une expression dans un tableau (ARRAY) : Elle est souvent utilisée pour transformer des lignes multiples en une seule ligne avec un tableau contenant toutes les valeurs correspondantes, ce qui est utile pour la restructuration de données ou la génération de formats hiérarchiques.
- L'option DISTINCT permet d'éliminer les doublons dans le tableau final : C'est une manière efficace de produire une liste de valeurs uniques sans avoir à effectuer un SELECT DISTINCT séparé, tout en conservant la structure d'agrégation d'un tableau.
- Grâce au paramètre ORDER BY, il est possible de contrôler l'ordre des éléments dans le tableau résultant : Cela permet, par exemple, de classer des notes, des événements ou des transactions dans un ordre précis avant de les encapsuler dans un ARRAY.
- L'option IGNORE NULLS permet d'exclure les valeurs NULL du tableau agrégé, tandis que RESPECT NULLS les inclut : Par défaut, ARRAY_AGG respecte les NULLs, mais dans certains cas, comme la création de rapports ou d'exports structurés, il est préférable de les ignorer pour éviter des éléments vides.
- Le paramètre LIMIT permet de restreindre le nombre d'éléments agrégés dans le tableau, ce qui est utile pour éviter des surcharges de données : Par exemple, on peut se limiter aux 5 dernières valeurs par utilisateur, tout en gardant un format compact de type tableau.
- ARRAY_AGG peut être utilisée en conjonction avec GROUP BY pour créer des colonnes contenant des sous-listes pour chaque groupe : C'est particulièrement utile pour créer des vues relationnelles enrichies, comme des clients avec leur liste de commandes, ou des catégories avec leurs produits.
- Lorsqu'utilisé avec la clause OVER (...), ARRAY_AGG devient une fonction analytique, produisant un tableau glissant par fenêtre : Cela permet, par exemple, de créer un historique glissant de valeurs sur une période définie, tout en respectant l'ordre et le contexte d'analyse (par exemple : les 10 dernières valeurs par date).
- Le résultat retourné est un ARRAY, que l'on peut manipuler avec des fonctions comme UNNEST, ARRAY_LENGTH, SAFE_OFFSET,.. : Cela offre une grande flexibilité pour analyser, filtrer ou reformater les données après agrégation, notamment dans des sous-requêtes complexes ou des pipelines de transformation.
Dernière mise à jour : Jeudi, le 18 Juin 2020