- 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> ) ]
Fonction de fenêtre
ARRAY_AGG( [ DISTINCT ] <expr1> )
[ WITHIN GROUP ( <orderby_clause> ) ]
OVER ( [ PARTITION BY <expr2> ] )
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 clauseORDER 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
etWITHIN 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) ...;
Si vous spécifiez des colonnes différentes pour
DISTINCT
etWITHIN GROUP
, une erreur se produit :SELECT array_agg(DISTINCT O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERSTATUS) ...;
SQL compilation error: [ORDERS.O_ORDERSTATUS] is not a valid order by expression
Vous devez soit spécifier la même colonne pour
DISTINCT
etWITHIN GROUP
, soit omettreDISTINCT
.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);
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 | +------------+
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 | | ] | +--------------------------------------------------------------+
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" | | ] | +-----------------------------------------------------------------------------+
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" | | | ] | +---------------+-------------------------------------------------------------+