Formação
Módulo
Usar funções internas e GROUP BY no Transact-SQL - Training
Usar funções internas e GROUP BY no Transact-SQL
Este browser já não é suportado.
Atualize para o Microsoft Edge para tirar partido das mais recentes funcionalidades, atualizações de segurança e de suporte técnico.
No Power Query, pode agrupar valores em várias linhas num único valor agrupando as linhas de acordo com os valores numa ou mais colunas. Você pode escolher entre dois tipos de operações de agrupamento:
Agrupamentos de colunas.
Agrupamentos de linhas.
Para este tutorial, você está usando a tabela de exemplo a seguir.
Captura de ecrã de uma tabela com colunas que mostram Ano (2020), País (EUA, Panamá ou Canadá), Produto (Camisa ou Calções), Canal de vendas (Online ou Revendedor) e Unidades (vários valores de 55 a 7500)
Pode encontrar o botão Agrupar por em três locais:
Na guia Página Inicial, no grupo Transformar.
Na guia Transformar, no grupo Tabela.
No menu de atalho, quando você clica com o botão direito do mouse para selecionar colunas.
Neste exemplo, seu objetivo é resumir o total de unidades vendidas no nível do país e do canal de vendas. Use as colunas País e Canal de Vendas para executar o grupo por operação.
Esta operação fornece a tabela a seguir.
Com o recurso Agrupar por , as operações disponíveis podem ser categorizadas de duas maneiras:
A tabela a seguir descreve cada uma dessas operações.
Nome da Operação | Categoria | Description |
---|---|---|
Sum | Operação da coluna | Soma todos os valores de uma coluna |
Média | Operação da coluna | Calcula o valor médio de uma coluna |
Mediana | Operação da coluna | Calcula a mediana a partir de uma coluna |
Min | Operação da coluna | Calcula o valor mínimo de uma coluna |
Máx. | Operação da coluna | Calcula o valor máximo de uma coluna |
Percentil | Operação da coluna | Calcula o percentil, usando um valor de entrada de 0 a 100, de uma coluna |
Contar valores distintos | Operação da coluna | Calcula o número de valores distintos de uma coluna |
Contar linhas | Operação de linha | Calcula o número total de linhas de um determinado grupo |
Contar linhas distintas | Operação de linha | Calcula o número de linhas distintas de um determinado grupo |
Todas as linhas | Operação de linha | Produz todas as linhas agrupadas em um valor de tabela sem agregações |
Nota
As operações Contar valores distintos e Percentil só estão disponíveis no Power Query Online.
A partir do exemplo original, neste exemplo você cria uma coluna contendo o total de unidades e duas outras colunas que fornecem o nome e as unidades vendidas para o produto de melhor desempenho, resumidas no nível do país e do canal de vendas.
Use as seguintes colunas como Agrupar por colunas:
Crie duas novas colunas seguindo estas etapas:
Após a conclusão dessa operação, observe como a coluna Produtos tem valores [Tabela] dentro de cada célula. Cada valor [Tabela] contém todas as linhas que foram agrupadas pelas colunas País e Canal de Vendas da tabela original. Você pode selecionar o espaço em branco dentro da célula para ver uma visualização do conteúdo da tabela na parte inferior da caixa de diálogo.
Nota
O painel de visualização de detalhes pode não mostrar todas as linhas que foram usadas para a operação de grupo por. Você pode selecionar o valor [Tabela] para ver todas as linhas pertencentes à operação correspondente grupo por.
Em seguida, você precisa extrair a linha que tem o valor mais alto na coluna Unidades das tabelas dentro da nova coluna Produtos e chamar essa nova coluna de produto de melhor desempenho.
Com a nova coluna Produtos com valores [Tabela], você cria uma nova coluna personalizada acessando a guia Adicionar Coluna na faixa de opções e selecionando Coluna personalizada no grupo Geral .
Nomeie sua nova coluna como produto com melhor desempenho. Insira a fórmula Table.Max([Products], "Units" )
em Fórmula de coluna personalizada.
O resultado dessa fórmula cria uma nova coluna com valores [Record]. Esses valores de registro são essencialmente uma tabela com apenas uma linha. Esses registros contêm a linha com o valor máximo para a coluna Unidades de cada valor [Tabela] na coluna Produtos .
Com esta nova coluna de produto com melhor desempenho que contém valores [Record], pode selecionar o ícone de expansão, selecionar os campos Produto e Unidades e, em seguida, selecionar OK.
Depois de remover a coluna Produtos e definir o tipo de dados para ambas as colunas recém-expandidas, o resultado será semelhante à imagem a seguir.
Nota
A seguinte funcionalidade só está disponível no Power Query Online.
Para demonstrar como fazer o "agrupamento difuso", considere a tabela de exemplo mostrada na imagem a seguir.
O objetivo do agrupamento difuso é fazer uma operação grupo por grupo que usa um algoritmo de correspondência aproximada para cadeias de texto. O Power Query utiliza o algoritmo de semelhança Jaccard para medir a semelhança entre pares de instâncias. Em seguida, ele aplica clustering hierárquico aglomerativo para agrupar instâncias. A imagem a seguir mostra a saída esperada, onde a tabela é agrupada pela coluna Pessoa .
Para fazer o agrupamento difuso, execute as mesmas etapas descritas anteriormente neste artigo. A única diferença é que, desta vez, na caixa de diálogo Agrupar por , você marca a caixa de seleção Usar agrupamento difuso.
Para cada grupo de linhas, o Power Query seleciona a instância mais frequente como a instância "canónica". Se ocorrerem várias instâncias com a mesma frequência, o Power Query selecionará a primeira. Depois de selecionar OK na caixa de diálogo Agrupar por , você obterá o resultado esperado.
No entanto, você tem mais controle sobre a operação de agrupamento difuso expandindo as opções de grupo difuso.
As seguintes opções estão disponíveis para agrupamento difuso:
Neste exemplo, uma tabela de transformação é usada para demonstrar como os valores podem ser mapeados. A tabela de transformação tem duas colunas:
A imagem a seguir mostra a tabela de transformação usada neste exemplo.
Importante
É importante que a tabela de transformação tenha as mesmas colunas e nomes de colunas mostrados na imagem anterior (eles devem ser rotulados como "De" e "Para"). Caso contrário, o Power Query não reconhece a tabela como uma tabela de transformação.
Retorne à caixa de diálogo Agrupar por, expanda Opções de grupo difuso, altere a operação de Contar linhas para Todas as linhas, habilite a opção Mostrar pontuações de semelhança e selecione o menu suspenso Tabela de transformação.
Depois de selecionar a tabela de transformação, selecione OK. O resultado dessa operação fornece as seguintes informações:
Neste exemplo, a opção Ignorar maiúsculas e minúsculas foi habilitada, portanto, os valores na coluna De da tabela Transformação são usados para procurar a cadeia de texto sem considerar o caso da cadeia de caracteres. Essa operação de transformação ocorre primeiro e, em seguida, a operação de agrupamento difuso é executada.
A pontuação de similaridade também é mostrada no valor da tabela ao lado da coluna pessoa, que reflete exatamente como os valores foram agrupados e seus respetivos escores de similaridade. Você pode expandir esta coluna, se necessário, ou usar os valores das novas colunas Frequência para outros tipos de transformações.
Nota
Ao agrupar por várias colunas, a tabela de transformação executa a operação de substituição em todas as colunas se a substituição do valor aumentar a pontuação de similaridade.
Para obter mais informações sobre como as tabelas de transformação funcionam, vá para Preceitos da tabela de transformação.
Formação
Módulo
Usar funções internas e GROUP BY no Transact-SQL - Training
Usar funções internas e GROUP BY no Transact-SQL