GROUP BY coloca a consulta SELECT no modo de agregação, que funciona da seguinte forma:
- A cláusula
GROUP BYcontém uma lista de expressões (ou uma única expressão, que é considerada uma lista de tamanho 1). Essa lista atua como uma “chave de agrupamento”, enquanto cada expressão individual é chamada de “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 de agregação aplicadas a expressões que não sã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 de agregação, mas não em ambas.
- O resultado da agregação da consulta
SELECTconterá tantas linhas quantos forem os valores únicos da “chave de agrupamento” na tabela de origem. Em geral, isso reduz significativamente o número de linhas, muitas vezes em ordens de grandeza, mas não necessariamente: o número de linhas permanece o mesmo se todos os valores da “chave de agrupamento” forem distintos.
Há outra forma de executar agregação em uma tabela. Se uma consulta contiver colunas da tabela apenas dentro de funções de agregação, a
cláusula GROUP BY poderá ser omitida, e a agregação por um conjunto vazio de chaves será assumida. Essas consultas sempre retornam exatamente uma linha.Processamento de NULL
NULL==NULL. Isso difere do processamento de NULL na maioria dos outros contextos.
Veja um exemplo do que isso significa.
Suponha que você tenha esta tabela:
SELECT sum(x), y FROM t_null_big GROUP BY y resulta em:
GROUP BY para y = NULL somou x, como se NULL fosse esse valor.
Se você passar várias colunas para GROUP BY, o resultado trará todas as combinações dos valores selecionados, como se NULL fosse um valor específico.
Modificador ROLLUP
ROLLUP é usado para calcular subtotais para as expressões-chave, com base na ordem em que elas aparecem 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, são calculados para a última expressão-chave da 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 ou uma string vazia.
Observe que a cláusula HAVING pode afetar os resultados dos subtotais.
Query
GROUP BY 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 colunadayé preenchida com zeros);GROUP BY year(agora as colunasmonthedaysão preenchidas com zeros);- e os totais (e as três colunas das expressões-chave ficam com zero).
Response
WITH.
Query
- Configuração group_by_use_nulls para compatibilidade com o padrão SQL.
Modificador CUBE
CUBE é usado para calcular subtotais para todas as combinações 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 ou vazios.
Lembre-se de que a cláusula HAVING pode afetar os resultados dos subtotais.
Query
GROUP BY tem três expressões-chave, o resultado contém oito tabelas com subtotais para todas as combinações dessas expressões-chave:
GROUP BY year, month, dayGROUP BY year, monthGROUP BY year, dayGROUP BY yearGROUP BY month, dayGROUP BY monthGROUP BY day- e os totais.
GROUP BY são preenchidas com zeros.
Response
WITH.
Query
- A configuração group_by_use_nulls para compatibilidade com o padrão SQL.
Modificador WITH TOTALS
WITH TOTALS for especificado, outra linha será calculada. Essa linha terá colunas-chave contendo valores padrão (zeros ou linhas vazias) e colunas de funções de agregação com os valores calculados considerando todas as linhas (os valores “totais”).
Essa linha extra só é produzida nos formatos JSON*, TabSeparated* e Pretty*, separadamente das demais linhas:
- Nos formatos
XMLeJSON*, essa linha é exibida como um campototalsseparado. - Nos formatos
TabSeparated*,CSV*eVertical, a linha vem após o resultado principal, precedida por uma linha vazia (depois dos outros dados). - Nos formatos
Pretty*, a linha é exibida como uma tabela separada após o resultado principal. - No formato
Template, a linha é exibida de acordo com o template especificado. - Nos outros formatos, ela não está disponível.
totals é exibido nos resultados de consultas
SELECT e não é exibido em INSERT INTO ... SELECT.WITH TOTALS pode se comportar de maneiras diferentes quando HAVING está presente. O comportamento depende da configuração totals_mode.
Configurando o processamento de totais
totals_mode = 'before_having'. Nesse caso, ‘totals’ é calculado sobre todas as linhas, incluindo as que não passam por HAVING nem por max_rows_to_group_by.
As outras alternativas incluem em ‘totals’ apenas as linhas que passam por HAVING e se comportam de forma diferente com a configuração max_rows_to_group_by e group_by_overflow_mode = 'any'.
after_having_exclusive – Não inclua linhas que não passaram por max_rows_to_group_by. Em outras palavras, ‘totals’ terá menos linhas ou o mesmo número de linhas que teria se max_rows_to_group_by fosse omitido.
after_having_inclusive – Inclua em ‘totals’ todas as linhas que não passaram por max_rows_to_group_by. Em outras palavras, ‘totals’ terá mais linhas ou o mesmo número de linhas que teria se max_rows_to_group_by fosse omitido.
after_having_auto – Conte o número de linhas que passaram por HAVING. Se esse número for maior que um determinado limite (por padrão, 50%), inclua em ‘totals’ todas as linhas que não passaram por max_rows_to_group_by. Caso contrário, não as inclua.
totals_auto_threshold – Por padrão, 0.5. O coeficiente para after_having_auto.
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 equivalentes, 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).
GROUP BY ALL
GROUP BY ALL equivale a listar todas as expressões selecionadas no SELECT que não são funções de agregação.
Por exemplo:
GROUP BY conterão o máximo de campos não agregados que pudermos extrair dela.
Por exemplo:
Exemplos
GROUP BY calcula um conjunto de valores de funções de agregação.
modificador GROUPING SETS
GROUPING SETS.
Embora consultas com os modificadores ROLLUP, CUBE e GROUPING SETS sejam sintaticamente equivalentes, elas podem ter desempenhos diferentes.
Enquanto GROUPING SETS tenta executar tudo em paralelo, ROLLUP e CUBE executam a mesclagem final das agregações em uma única thread.
Quando as colunas de origem contêm valores padrão, pode ser difícil distinguir se uma linha faz 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.
- A configuração group_by_use_nulls para compatibilidade com o padrão SQL.
Detalhes da implementação
Otimização de GROUP BY dependendo da chave de ordenação da tabela
GROUP BY contiver pelo menos um prefixo da chave de ordenaçã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 em memória externa
GROUP BY.
A configuração max_bytes_before_external_group_by determina o limite de consumo de RAM para despejar dados temporários do GROUP BY no sistema de arquivos. Se for definida como 0 (o padrão), ela fica desabilitada.
Como alternativa, você pode definir max_bytes_ratio_before_external_group_by, que permite usar GROUP BY em memória externa somente quando a consulta atingir um determinado limite de uso de memória.
Ao usar max_bytes_before_external_group_by, recomendamos definir max_memory_usage com um valor aproximadamente duas vezes maior (ou max_bytes_ratio_before_external_group_by=0.5). Isso é necessário porque há dois estágios na agregação: ler os dados e formar dados intermediários (1) e mesclar os dados intermediários (2). O despejo de dados no sistema de arquivos só pode ocorrer durante o estágio 1. Se os dados temporários não tiverem sido despejados, o estágio 2 poderá exigir até a mesma quantidade de memória do estágio 1.
Por exemplo, se max_memory_usage tiver sido definido como 10000000000 e você quiser usar agregação externa, faz sentido definir max_bytes_before_external_group_by como 10000000000 e max_memory_usage como 20000000000. Quando a agregação externa é acionada (se tiver havido pelo menos um despejo de dados temporários), o consumo máximo de RAM fica apenas ligeiramente acima de max_bytes_before_external_group_by.
Com o processamento distribuído de consultas, 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 gravados em disco, bem como ao mesclar resultados de servidores remotos quando a configuração distributed_aggregation_memory_efficient está habilitada, consome-se até 1/256 * the_number_of_threads da quantidade total de RAM.
Quando a agregação externa está habilitada, se houver menos de max_bytes_before_external_group_by de dados (ou seja, os dados não forem gravados em disco), a consulta será executada tão rapidamente quanto sem agregação externa. Se algum dado temporário for gravado em disco, o tempo de execução será várias vezes maior (aproximadamente três vezes).
Se você tiver um ORDER BY com um LIMIT após GROUP BY, a quantidade de RAM usada dependerá da quantidade de dados em LIMIT, e não na tabela inteira. Mas, se o ORDER BY não tiver LIMIT, não se esqueça de ativar a ordenação externa (max_bytes_before_external_sort).