Categorias:: Funções de agregação (Geral) , Sintaxe e uso da função de janela (Geral)

LISTAGG¶

Retorna os valores de entrada concatenados, separados pela cadeia de caracteres delimiter.

Sintaxe¶

Função de agregação

LISTAGG( [ DISTINCT ] <expr1> [, <delimiter> ] )
    [ WITHIN GROUP ( <orderby_clause> ) ]

Copy

Função de janela

LISTAGG( [ DISTINCT ] <expr1> [, <delimiter> ] )
    [ WITHIN GROUP ( <orderby_clause> ) ]
    OVER ( [ PARTITION BY <expr2> ] )

Copy

Argumentos exigidos¶

expr1: Uma expressão (normalmente um nome de coluna) que determina os valores a serem colocados na lista. A expressão deve ser avaliada como uma cadeia de caracteres ou como um tipo de dados que pode ser convertido em cadeia de caracteres.
OVER(): A cláusula OVER é necessária quando 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.

Argumentos opcionais¶

DISTINCT

Remove valores duplicados da lista.

delimiter

Uma cadeia de caracteres ou uma expressão que avalia como uma cadeia de caracteres. Normalmente, esse valor é uma cadeia de caracteres de um único caractere. A cadeia de caracteres deve ser delimitada por aspas simples, como mostrado nos exemplos abaixo.

Se delimiter não for especificado, uma cadeia de caracteres vazia será usada como delimiter.

O delimiter deve ser uma constante.

WITHIN GROUP orderby_clause

Uma ou mais expressões (normalmente nomes de colunas) que determinam a ordem dos valores para cada grupo na lista.

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

PARTITION BY expr2

Subclá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 uma cadeia de caracteres que inclui todos os valores de entrada que não são NULL, separados pelo delimiter.

Esta função não retorna uma lista ou uma matriz. Ela retorna uma única cadeia de caracteres com todos os valores de entrada não NULL.

Notas de uso¶

Se você não especificar WITHIN GROUP (ORDERBY), a ordem dos elementos dentro de cada lista é 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 lista 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 LISTAGG(DISTINCT O_ORDERKEY) WITHIN GROUP (ORDER BY O_ORDERKEY) ...;
```
Copy
Se você especificar colunas diferentes para DISTINCT e WITHIN GROUP, ocorrerá um erro:
```
SELECT LISTAGG(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.
Em relação a valores de entrada NULL ou vazios:
- Se a entrada estiver vazia, uma cadeia de caracteres vazia é retornada.
- Se todas as expressões de entrada forem avaliadas como NULL, a saída é uma cadeia de caracteres vazia.
- Se algumas mas não todas as expressões de entrada forem avaliadas como NULL, a saída contém todos os valores nãoNULL e exclui os valores NULL.

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.

Detalhes do agrupamento¶

The collation of the result is the same as the collation of the input.
Os elementos dentro da lista são ordenados de acordo com os agrupamentos, se a subcláusula ORDER BY especificar uma expressão com agrupamento.
delimiter não pode usar uma especificação de agrupamento.
Especificar o agrupamento dentro de ORDER BY não afeta o agrupamento do resultado. Por exemplo, a instrução abaixo contém duas cláusulas ORDER BY, uma para LISTAGG e outra para os resultados da consulta. Especificar o agrupamento dentro da primeira não afeta o agrupamento da segunda. Se você precisar comparar a saída em ambas as cláusulas ORDER BY, você deve especificar o agrupamento explicitamente em ambas as cláusulas.
```
SELECT LISTAGG(x, ', ') WITHIN GROUP (ORDER BY last_name COLLATE 'es')
  FROM table1
  ORDER BY last_name;
```
Copy

Exemplos¶

Esses exemplos usam a função LISTAGG.

Como usar a função LISTAGG para concatenar valores em resultados de consulta¶

Os exemplos a seguir usam a função LISTAGG para concatenar valores nos resultados de consultas em dados de pedidos.

Nota

Esses exemplos consulta os dados de amostra TPC-H. Antes de executar as consultas, execute a seguinte instrução SQL:

USE SCHEMA snowflake_sample_data.tpch_sf1;

Copy

Este exemplo lista os valores o_orderkey distintos para pedidos com um o_totalprice maior que 520000 e usa uma cadeia de caracteres vazia para delimiter:

SELECT LISTAGG(DISTINCT o_orderkey, ' ')
  FROM orders
  WHERE o_totalprice > 520000;

Copy

+-------------------------------------------------+
| LISTAGG(DISTINCT O_ORDERKEY, ' ')               |
|-------------------------------------------------|
| 2232932 1750466 3043270 4576548 4722021 3586919 |
+-------------------------------------------------+

Este exemplo lista os valores o_orderstatus distintos para pedidos com um o_totalprice maior que 520000 e usa uma barra vertical para delimiter:

SELECT LISTAGG(DISTINCT o_orderstatus, '|')
  FROM orders
  WHERE o_totalprice > 520000;

Copy

+--------------------------------------+
| LISTAGG(DISTINCT O_ORDERSTATUS, '|') |
|--------------------------------------|
| O|F                                  |
+--------------------------------------+

Este exemplo lista os valores o_orderstatus e o_clerk de cada pedido com um o_totalprice maior que 520000 agrupados por o_orderstatus. A consulta usa uma vírgula para delimiter:

SELECT o_orderstatus,
   LISTAGG(o_clerk, ', ')
     WITHIN GROUP (ORDER BY o_totalprice DESC)
  FROM orders
  WHERE o_totalprice > 520000
  GROUP BY o_orderstatus;

Copy

+---------------+---------------------------------------------------+
| O_ORDERSTATUS | LISTAGG(O_CLERK, ', ')                            |
|               |      WITHIN GROUP (ORDER BY O_TOTALPRICE DESC)    |
|---------------+---------------------------------------------------|
| O             | Clerk#000000699, Clerk#000000336, Clerk#000000245 |
| F             | Clerk#000000040, Clerk#000000230, Clerk#000000924 |
+---------------+---------------------------------------------------+

Como usar agrupamento com a função LISTAGG¶

Os exemplos a seguir mostram agrupamento com a função LISTAGG. Os exemplos usam os seguintes dados:

CREATE OR REPLACE TABLE collation_demo (
  spanish_phrase VARCHAR COLLATE 'es');

Copy

INSERT INTO collation_demo (spanish_phrase) VALUES
  ('piña colada'),
  ('Pinatubo (Mount)'),
  ('pint'),
  ('Pinta');

Copy

Observe a diferença na ordem de saída com as diferentes especificações de agrupamento. Esta consulta usa a especificação de agrupamento es:

SELECT LISTAGG(spanish_phrase, '|')
    WITHIN GROUP (ORDER BY COLLATE(spanish_phrase, 'es')) AS es_collation
  FROM collation_demo;

Copy

+-----------------------------------------+
| ES_COLLATION                            |
|-----------------------------------------|
| Pinatubo (Mount)|pint|Pinta|piña colada |
+-----------------------------------------+

Esta consulta usa a especificação de agrupamento utf8:

SELECT LISTAGG(spanish_phrase, '|')
    WITHIN GROUP (ORDER BY COLLATE(spanish_phrase, 'utf8')) AS utf8_collation
  FROM collation_demo;

Copy

+-----------------------------------------+
| UTF8_COLLATION                          |
|-----------------------------------------|
| Pinatubo (Mount)|Pinta|pint|piña colada |
+-----------------------------------------+