Categorias:: Funções de agregação (Dados semiestruturados) , Funções de janela (Geral) , Funções de dados semiestruturados e estruturados (Matriz/objeto)

ARRAY_AGG¶

Retorna os valores de entrada, articulados em uma matriz. Se a entrada estiver vazia, a função retornará uma matriz vazia.

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

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.

ORDER BY expr3 [ { ASC | DESC } ] [ NULLS { FIRST | LAST } ] [ {window_frame} ]

Expressão opcional para ordenar dentro de cada partição, seguida por um quadro de janela opcional. Para uma sintaxe window_frame detalhada, consulte Sintaxe e uso da função de janela.

Quando essa função é usada com um quadro baseado em intervalo, a cláusula ORDER BY oferece suporte a apenas uma única coluna. Os quadros baseados em linhas não têm essa restrição.

LIMIT não é suportado.

Retornos¶

Retorna um valor do tipo ARRAY.

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

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) ...;
```
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) ...;
```
```
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.
DISTINCT e WITHIN GROUP são compatíveis com as chamadas de função de janela somente quando não houver uma cláusula ORDER BY dentro da cláusula OVER. Quando uma cláusula ORDER BY é usada na cláusula OVER, os valores na matriz de saída seguem a mesma ordem padrão (ou seja, a ordem equivalente a WITHIN GROUP (ORDER BY expr3)).
Valores NULL são omitidos da saída.

Exemplos¶

As consultas de exemplo abaixo utilizam as tabelas e dados mostrados abaixo:

CREATE TABLE orders (
  o_orderkey INTEGER,
  o_clerk VARCHAR,
  o_totalprice NUMBER(12, 2),
  o_orderstatus CHAR(1)
);

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

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

O exemplo a seguir usa um conjunto de dados diferente. A função ARRAY_AGG é chamada como uma função de janela com um quadro de janela ROWS BETWEEN. Primeiro, crie a tabela e carregue-a com 14 linhas:

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

Agora execute a seguinte consulta. Observe que apenas um conjunto de resultados parcial é mostrado aqui.

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;

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