ARRAY_AGG¶

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¶

Obrigatório:

expr1

Uma expressão (normalmente um nome de coluna) que determina os valores a serem colocados na matriz.

OVER()

A cláusula OVER especifica que a função está sendo usada como uma função de janela. Para obter mais detalhes, consulte Sintaxe e uso da função de janela.

Opcional:

DISTINCT

Remove valores duplicados da matriz.

WITHIN GROUP orderby_clause

Cláusula que contém uma ou mais expressões (geralmente nomes de colunas) que determinam a ordem dos valores em cada matriz.

A sintaxe WITHIN GROUP(ORDER BY) oferece suporte aos mesmos parâmetros que a cláusula principal ORDER BY em uma instrução SELECT. Consulte ORDER BY.

PARTITION BY expr2

Cláusula de função de janela que especifica uma expressão (normalmente um nome de coluna). Esta expressão define partições que agrupam as linhas de entrada antes da aplicação da função. Para obter mais detalhes, consulte Sintaxe e uso da função de janela.

Retornos¶

Retorna um valor do tipo ARRAY.

A quantidade máxima de dados que ARRAY_AGG pode retornar para uma única chamada é 16MB.

Notas de uso¶

• Se você não especificar WITHIN GROUP(ORDERBY), a ordem dos elementos dentro de cada matriz é imprevisível. (Uma cláusula ORDER 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 um número para uma expressão em WITHIN GROUP(ORDER BY), esse número será analisado como uma constante numérica, não como a posição ordinal de uma coluna na lista SELECT. Portanto, não especifique números como expressões WITHIN GROUP(ORDER BY).

• Se você especificar DISTINCT e WITHIN GROUP, ambos devem se referir à mesma coluna. Por exemplo:

  SELECT ARRAY_AGG(DISTINCT O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERKEY) ...;
  

  Copy

  Se você especificar colunas diferentes para DISTINCT e WITHIN GROUP, ocorrerá um erro:

  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
  

  Você deve especificar a mesma coluna para DISTINCT e WITHIN GROUP ou omitir DISTINCT.

• Valores NULL são omitidos da saída.

• Quando esta função é chamada como uma função de janela, ela não oferece suporte para:

  • Uma cláusula ORDER BY dentro da cláusula OVER.

  • Quadros de janela explícitos.

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);

Copy

Este exemplo mostra a saída não articulada de uma consulta 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 |
+------------+

Copy

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                                                     |
| ]                                                            |
+--------------------------------------------------------------+

Copy

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"                                                                       |
| ]                                                                           |
+-----------------------------------------------------------------------------+

Copy

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"                                         |
|               | ]                                                           |
+---------------+-------------------------------------------------------------+

Copy