- 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> ) ]
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 comodelimiter
.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áusulaORDER 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
eWITHIN 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
eWITHIN 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
eWITHIN GROUP
ou omitirDISTINCT
.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 paraLISTAGG
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 | +-----------------------------------------------------------------+