- Categorias:
Funções de agregação (Dados semiestruturados) , Funções de janela (Geral) , Funções de dados semiestruturados (Matriz/objeto)
ARRAY_AGG¶
Retorna os valores de entrada, articulados em um ARRAY. Se a entrada estiver vazia, um ARRAY vazio é devolvido.
- Aliases:
ARRAYAGG
Sintaxe¶
Função de agregação
ARRAY_AGG( [ DISTINCT ] <expr1> ) [ WITHIN GROUP ( <orderby_clause> ) ]
Função de janela
ARRAY_AGG( [ DISTINCT ] <expr1> )
[ WITHIN GROUP ( <orderby_clause> ) ]
OVER ( [ PARTITION BY <expr2> ] )
Argumentos¶
expr1
A expressão (normalmente um nome de coluna) que determina os valores a serem colocados na lista.expr2
A expressão (normalmente um nome de coluna) que determina as partições nas quais se agrupam os valores.orderby_clause
Uma expressão (normalmente um nome de coluna) que determina a ordem dos valores da lista.
Retornos¶
Retorna um valor do tipo ARRAY.
A quantidade máxima de dados que ARRAY_AGG
pode retornar para uma única chamada é 16 MB.
Notas de uso¶
DISTINCT
é suportado para esta função.Se você não especificar a
WITHIN GROUP (<cláusula_ordempor>)
, a ordem dos elementos dentro de cada matriz é imprevisível. (Uma cláusulaORDER BY
fora da cláusula WITHIN GROUP se aplica à ordem das linhas de saída, não à ordem dos elementos da matriz dentro de uma linha).Se você especificar
DISTINCT
eWITHIN GROUP
, ambos devem se referir à mesma coluna. Por exemplo:SELECT array_agg(DISTINCT O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERKEY) ...;
Se você especificar colunas diferentes para
DISTINCT
eWITHIN GROUP
, ocorre um erro: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
Você deve especificar a mesma coluna para
DISTINCT
eWITHIN GROUP
ou omitirDISTINCT
.Valores NULL são omitidos da saída.
Quando usado como uma função de janela:
Esta função não oferece suporte para:
Subcláusula ORDER BY na cláusula OVER().
Quadros de janela.
Exemplos¶
As consultas de exemplo abaixo utilizam as tabelas e dados mostrados abaixo:
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);
Este exemplo mostra a saída não articulada de um SELECT
que não usa ARRAY_AGG()
. O contraste na saída entre este exemplo e o exemplo a seguir mostra que ARRAY_AGG()
articula os dados.
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 | +------------+
Este exemplo mostra como usar ARRAY_AGG()
para articular uma coluna de saída em uma matriz em uma única linha:
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 | | ] | +--------------------------------------------------------------+
Este exemplo mostra o uso da palavra-chave DISTINCT com 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" | | ] | +-----------------------------------------------------------------------------+
Este exemplo usa duas cláusulas ORDER BY separadas, uma controla a ordem dentro da matriz de saída dentro de cada linha, e a outra controla a ordem das linhas de saída:
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" | | | ] | +---------------+-------------------------------------------------------------+