LISTAGG¶

Sintaxe¶

Função de agregação

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

Função de janela

LISTAGG( [ DISTINCT ] <expr1> [, <delimiter> ] )
    [ 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. A expressão deve ser avaliada como uma cadeia de caracteres, ou como um tipo de dados que pode ser convertido em uma cadeia de caracteres.

delimiter

Uma cadeia de caracteres ou uma expressão que avalia como uma cadeia de caracteres. Na prática, isto geralmente é uma cadeia de caracteres de um único caractere. A cadeia de caracteres deve ser delimitada por aspas simples, como mostrado nos exemplos abaixo.

Se nenhuma cadeia de caracteres delimiter for especificada, a cadeia de caracteres vazia é usada como delimiter.

O delimiter deve ser uma constante.

expr2

Esta expressão é usada para agrupar as linhas em partições.

orderby_clause

Uma expressão (normalmente um nome de coluna) que determina a ordem dos valores da lista.

Retornos¶

Retorna uma cadeia de caracteres que inclui todos os valores de entrada que não são NULL, separados pelo delimiter.

(Note que isto não retorna uma “lista” (por exemplo, não retorna um ARRAY; retorna uma única cadeia de caracteres que contém todos os valores de entrada diferentes de NULL).

Notas de uso¶

• DISTINCT é suportado para esta função.

• Se você não especificar a WITHIN GROUP (<cláusula_ordenarpor>), 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 DISTINCT e WITHIN GROUP, ambos devem se referir à mesma coluna. Por exemplo:

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

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

  SELECT listagg(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.

• Com relação a NULL ou valores de entrada 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 usada como uma função de janela, esta função não oferece suporte a:

  • Subcláusula ORDER BY na cláusula OVER().

  • Quadros de janela.

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 especificou uma expressão com agrupamento.

• O delimiter não pode utilizar 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 'sp')
      from table1
      ORDER BY last_name;
  

Exemplos¶

SELECT listagg(O_ORDERKEY, ' ')
    FROM orders WHERE O_TOTALPRICE > 450000;

---------------------------------------------+
          LISTAGG(O_ORDERKEY, ' ')           |
---------------------------------------------+
 41445 55937 67781 80550 95808 101700 103136 |
---------------------------------------------+

SELECT listagg(DISTINCT O_ORDERSTATUS, '|')
    FROM orders WHERE O_TOTALPRICE > 450000;

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

SELECT O_ORDERSTATUS, listagg(O_CLERK, ', ') WITHIN GROUP (ORDER BY O_TOTALPRICE DESC)
    FROM orders WHERE O_TOTALPRICE > 450000 GROUP BY O_ORDERSTATUS;

---------------+--------------------------------------------------------------------+
 O_ORDERSTATUS |  LISTAGG(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 mostra o agrupamento com LISTAGG. Observe a diferença na ordem de saída com as diferentes especificações de agrupamento.

-- Collation
SELECT LISTAGG(spanish_phrase, '|') 
        WITHIN GROUP (ORDER BY COLLATE(spanish_phrase, 'sp'))
    FROM collation_demo
    GROUP BY english_phrase;
+---------------------------------------------------------------+
| LISTAGG(SPANISH_PHRASE, '|')                                  |
|         WITHIN GROUP (ORDER BY COLLATE(SPANISH_PHRASE, 'SP')) |
|---------------------------------------------------------------|
| piña colada|Pinatubo (Mount)|pint|Pinta                       |
+---------------------------------------------------------------+
-- Different collation.
SELECT LISTAGG(spanish_phrase, '|') 
        WITHIN GROUP (ORDER BY COLLATE(spanish_phrase, 'utf8'))
    FROM collation_demo
    GROUP BY english_phrase;
+-----------------------------------------------------------------+
| LISTAGG(SPANISH_PHRASE, '|')                                    |
|         WITHIN GROUP (ORDER BY COLLATE(SPANISH_PHRASE, 'UTF8')) |
|-----------------------------------------------------------------|
| Pinatubo (Mount)|Pinta|pint|piña colada                         |
+-----------------------------------------------------------------+