Cláusula GROUP BY
A cláusula GROUP BY altera a consulta SELECT para um modo de agregação, que funciona da seguinte maneira:
A cláusula GROUP BY contém uma lista de expressões (ou uma única expressão, que é considerada a lista de comprimento um)que atua como uma “chave de agrupamento”, enquanto cada expressão individual será referida como uma “expressão-chave”.
Todas as expressões nas cláusulas SELECT , HAVING e ORDER BY devem ser calculadas com base em expressões-chave ou em funções agregadas sobre expressões não-chave (incluindo colunas simples). Em outras palavras, cada coluna selecionada da tabela deve ser usada em uma expressão-chave ou dentro de uma função agregada, mas não em ambas.
O resultado da consulta SELECT de agregação conterá tantas linhas quantos forem os valores exclusivos da “chave de agrupamento” na tabela de origem, e isso reduz significativamente a contagem de linhas, frequentemente em ordens de magnitude, mas não necessariamente: a contagem de linhas permanecerá a mesma se todos os valores da “chave de agrupamento” forem distintos.
Quando você quiser agrupar dados na tabela por números de coluna em vez de nomes de coluna, habilite a configuração enable_positional_arguments .
Observação
Há uma maneira adicional de executar agregação sobre uma tabela, por exemplo, se a consulta contiver colunas de tabela somente dentro de funções de agregação GROUP BY, elas podem ser omitidas e a agregação por um conjunto vazio de chaves é assumida. Essas consultas sempre retornam exatamente uma linha.
Agrupamento de Valor NULL
O ClickHouse interpreta NULL como um valor, e NULL==NULL. Ele difere do processamento NULL na maioria dos outros contextos.
Aqui está um exemplo para mostrar o que isso significa.
Suponha que você tenha esta tabela:
A consulta SELECT sum(x), y FROM t_null_big GROUP BY y resulta em:
Você pode ver que GROUP BY para y = NULL resumido em x, como se NULL fosse esse valor.
Se você passar várias chaves para GROUP BY, o resultado lhe dará todas as combinações da seleção, como se NULL fosse um valor específico.
ROLLUP
O modificador ROLLUP é usado para calcular subtotais para as expressões-chave, com base na ordem delas na lista GROUP BY. As linhas de subtotais são adicionadas após a tabela de resultados.
Os subtotais são calculados na ordem inversa: primeiro, os subtotais são calculados para a última expressão-chave na lista, depois para a anterior e assim por diante até a primeira expressão-chave.
Nas linhas de subtotais, os valores das expressões-chave já "agrupadas" são definidos como 0 a uma linha vazia.
Observação
Lembre-se de que a cláusula HAVING pode afetar os resultados dos subtotais.
Exemplo
Considere a tabela t:
Consulta:
SELECT year, month, day, count(*) FROM t GROUP BY ROLLUP(year, month, day);
Como GROUP BYa seção tem três expressões-chave, o resultado contém quatro tabelas com subtotais "acumulados" da direita para a esquerda:
GROUP BY year, month, day;
GROUP BY year, month(e a coluna day é preenchida com zeros);
GROUP BY year(agora as colunas month, day são preenchidas com zeros);
e totais (e todas as três colunas de expressão-chave são zeros).
A mesma consulta também pode ser escrita usando q palavras-chave WITH.
SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH ROLLUP;
Veja também
configuração group_by_use_nulls para compatibilidade com o padrão SQL.
CUBE
O modificador CUBE é usado para calcular subtotais para cada combinação das expressões-chave na lista GROUP BY. As linhas de subtotais são adicionadas após a tabela de resultados.
Nas linhas de subtotais, os valores de todas as expressões-chave "agrupadas" são definidos como 0 em uma linha vazia.
Observação
Lembre-se de que a cláusula HAVING pode afetar os resultados dos subtotais.
Exemplo
Considere a tabela t:
Consulta:
SELECT year, month, day, count(*) FROM t GROUP BY CUBE(year, month, day);
Como a seção GROUP BY tem três expressões-chave, o resultado contém oito tabelas com subtotais para todas as combinações de expressões-chave:
GROUP BY year, month, day
Colunas de GROUP BY excluídas são preenchidas com zeros.
GROUP BY year, month
GROUP BY year, day
GROUP BY year
GROUP BY month, day
GROUP BY month
GROUP BY day
e totais.
A mesma consulta também pode ser escrita usando a palavras-chave WITH.
SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH CUBE;
Veja também
configuração group_by_use_nulls para compatibilidade com o padrão SQL.
COM TOTAIS
Se o modificador WITH TOTALS for especificado, outra linha será calculada, e esta linha terá colunas-chave contendo valores padrão (zeros ou linhas vazias) e colunas de funções agregadas com os valores calculados em todas as linhas (os valores “totais”).
Esta linha extra é produzida apenas nos formatos JSON*, TabSeparated* e Pretty*, separadamente das outras linhas:
Nos formatos XML e JSON*, esta linha é exibida como um campo 'totais' separado.
Nos formatos TabSeparated*, CSV* e Vertical, a linha vem depois do resultado principal, precedida por uma linha vazia (depois dos outros dados).
Em Pretty*formatos, a linha é exibida como uma tabela separada após o resultado principal.
No Templateformato, a linha é gerada de acordo com o modelo especificado.
Nos outros formatos não está disponível.
Observação
totais são exibidos nos resultados das consultas SELECT e não em INSERT INTO ... SELECT.
WITH TOTALS pode ser executado de diferentes maneiras quando HAVING está presente, e o comportamento depende da configuração totals_mode.
Configurando
Por padrão, totals_mode = 'before_having'. Neste caso, 'totais' é calculado em todas as linhas, incluindo aquelas que não passam por HAVING e max_rows_to_group_by.
As outras alternativas incluem apenas as linhas que passam por HAVING em 'totais' e se comportam de forma diferente com a configuração max_rows_to_group_bye group_by_overflow_mode = 'any'.
after_having_exclusive – Não inclui linhas que não passaram por max_rows_to_group_by. Em outras palavras, 'totais' terá menos ou o mesmo número de linhas que teria se max_rows_to_group_by fosse omitido.
after_having_inclusive – Inclui todas as linhas que não passaram por 'max_rows_to_group_by' em 'totals'. Em outras palavras, 'totals' terá mais ou o mesmo número de linhas que teria se max_rows_to_group_by fosse omitido.
after_having_auto – Conta o número de linhas que passaram por HAVING. Se for mais do que uma certa quantidade (por padrão, 50%), inclui todas as linhas que não passaram por 'max_rows_to_group_by' em 'totals'. Caso contrário, não as inclui.
totals_auto_threshold – Por padrão o coeficiente para after_having_auto é 0,5.
Se max_rows_to_group_by e group_by_overflow_mode = 'any' não forem usados, todas as variações de after_having serão as mesmas, e você poderá usar qualquer uma delas (por exemplo, after_having_auto).
Você pode usar WITH TOTALS em subconsultas, incluindo subconsultas na cláusula JOIN (nesse caso, os respectivos valores totais são combinados).
AGRUPADO POR
GROUP BY ALL é equivalente a listar todas as expressões SELECT que não são funções de agregação.
Por exemplo:
SELECT
a * 2,
b,
count(c),
FROM t
GROUP BY ALL
é o mesmo que
SELECT
a * 2,
b,
count(c),
FROM t
GROUP BY a * 2, b
Para um caso especial, se houver uma função que tenha funções agregadas e outros campos como argumentos, as GROUP BYchaves conterão o máximo de campos não agregados que podemos extrair dela.
Por exemplo:
SELECT
substring(a, 4, 2),
substring(substring(a, 1, 2), 1, count(b))
FROM t
GROUP BY ALL
é o mesmo que
SELECT
substring(a, 4, 2),
substring(substring(a, 1, 2), 1, count(b))
FROM t
GROUP BY substring(a, 4, 2), substring(a, 1, 2)
Exemplo:
SELECT
count(),
median(FetchTiming > 60 ? 60 : FetchTiming),
count() - sum(Refresh)
FROM hits
Ao contrário do MySQL (e em conformidade com o SQL padrão), você não pode obter algum valor de alguma coluna que não esteja em uma função de chave ou agregação (exceto expressões constantes). Para contornar isso, você pode usar a função de agregação 'any' (obter o primeiro valor encontrado) ou 'min/max'.
Exemplo:
SELECT
domainWithoutWWW(URL) AS domain,
count(),
any(Title) AS title -- getting the first occurred page header for each domain.
FROM hits
GROUP BY domain
Para cada valor de chave diferente encontrado, GROUP BY calcula um conjunto de valores de função agregada.
Conjuntos de agrupamentos
Este é o modificador mais geral. Este modificador permite especificar manualmente vários conjuntos de chaves de agregação (conjuntos de agrupamento). A agregação é realizada separadamente para cada conjunto de agrupamento e, depois disso, todos os resultados são combinados. Se uma coluna não for apresentada em um conjunto de agrupamento, ela será preenchida com um valor padrão.
Em outras palavras, os modificadores descritos acima podem ser representados via GROUPING SETS. Apesar do fato de que consultas com modificadores ROLLUP, CUBEe GROUPING SETSsão sintaticamente iguais, elas podem ter desempenho diferente. Quando tentamos executar GROUPING SETS, ROLLUP e CUBE, tudo em paralelo, estamos executando a mesclagem final dos agregados em um único thread.
Na situação em que as colunas de origem contêm valores padrão, pode ser difícil distinguir se uma linha é parte da agregação que usa essas colunas como chaves ou não. Para resolver esse problema, a função GROUPING deve ser usada.
Exemplo
As duas consultas a seguir são equivalentes.
-- Query 1
SELECT year, month, day, count(*) FROM t GROUP BY year, month, day WITH ROLLUP;
-- Query 2
SELECT year, month, day, count(*) FROM t GROUP BY
GROUPING SETS
(
(year, month, day),
(year, month),
(year),
()
);
Veja também
configuração group_by_use_nulls para compatibilidade com o padrão SQL.
Implementação
Agregação é um dos recursos mais importantes de um DBMS orientado a colunas e, portanto, sua implementação é uma das partes mais otimizadas do ClickHouse. Por padrão, a agregação é feita na memória usando uma tabela hash. Ela tem mais de 40 especializações que são escolhidas automaticamente dependendo dos tipos de dados de “chave de agrupamento”.
Otimização GROUP BY
A agregação pode ser realizada de forma mais eficaz se uma tabela for classificada por alguma chave e a expressão GROUP BY contiver pelo menos o prefixo da chave de classificação ou funções injetivas. Nesse caso, quando uma nova chave é lida da tabela, o resultado intermediário da agregação pode ser finalizado e enviado ao cliente. Esse comportamento é ativado pela configuração optimize_aggregation_in_order. Essa otimização reduz o uso de memória durante a agregação, mas em alguns casos pode tornar a execução da consulta mais lenta.
GROUP BY
Você pode habilitar o despejo de dados temporários no disco para restringir o uso de memória durante a utilização do GROUP BY. A configuração max_bytes_before_external_group_by determina na utilização do GROUP BY, o consumo limite de RAM para o despejo de dados temporários no sistema de arquivos. Se definido como 0 (o padrão), ele é desabilitado.
Ao usar max_bytes_before_external_group_by, recomendamos que você defina max_memory_usage aproximadamente o dobro do valor alto. Isso é necessário porque há dois estágios para agregação: leitura dos dados e formação de dados intermediários(1) e mesclagem dos dados intermediários(2). O despejo de dados para o sistema de arquivos só pode ocorrer durante o estágio 1. Se os dados temporários não foram despejados, o estágio 2 pode exigir até a mesma quantidade de memória que no estágio 1.
Por exemplo, se max_memory_usage foi definido como 10000000000 e você deseja usar agregação externa, faz sentido definir max_bytes_before_external_group_by como 10000000000 e max_memory_usagecomo 20000000000. Quando a agregação externa é acionada (se houver pelo menos um despejo de dados temporários), o consumo máximo de RAM é apenas um pouco maior que max_bytes_before_external_group_by.
Com o processamento de consultas distribuídas, a agregação externa é realizada em servidores remotos. Para que o servidor solicitante use apenas uma pequena quantidade de RAM, defina distributed_aggregation_memory_efficient como 1.
Ao mesclar dados liberados no disco, bem como ao mesclar resultados de servidores remotos quando a configuração distributed_aggregation_memory_efficient está ativa, o consomo é de até 1/256 * the_number_of_threadsda quantidade total de RAM.
Quando a agregação externa está habilitada, se houver menos max_bytes_before_external_group_byde dados (ou seja, os dados não foram liberados), a consulta é executada tão rápido quanto sem agregação externa. Se quaisquer dados temporários foram liberados, o tempo de execução será várias vezes maior (aproximadamente três vezes).
Se você tiver um ORDER BY com um LIMIT depois de GROUP BY, então a quantidade de RAM usada depende da quantidade de dados em LIMIT, não na tabela inteira. Mas se o ORDER BY não tiver LIMIT, não se esqueça de habilitar a classificação externa (max_bytes_before_external_sort).