Categorias:

Funções de agregação (Geral) , Funções 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

Obrigatório:

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 uma cadeia de caracteres.

OVER()

A cláusula OVER especifica que a função está sendo usada como uma função de janela. Para obter mais detalhes, consulte Funções de janela.

Opcional:

DISTINCT

Remove valores duplicados da lista.

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.

WITHIN GROUP orderby_clause

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

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

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 Funções de janela.

Retornos

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

Observe que esta função não retorna uma “lista” ou uma matriz; ela retorna uma única cadeia de caracteres que contém todos os valores de entrada diferentes de 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.

  • 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;
    
    Copy

Exemplos

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

---------------------------------------------+
          LISTAGG(O_ORDERKEY, ' ')           |
---------------------------------------------+
 41445 55937 67781 80550 95808 101700 103136 |
---------------------------------------------+
Copy
SELECT LISTAGG(DISTINCT O_ORDERSTATUS, '|')
    FROM orders WHERE O_TOTALPRICE > 450000;

--------------------------------------+
 LISTAGG(DISTINCT O_ORDERSTATUS, '|') |
--------------------------------------+
 F|O                                  |
--------------------------------------+
Copy
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 |
---------------+--------------------------------------------------------------------+
Copy

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