Tutorial: Usar funções de agregação

Aplica-se a: ✅Microsoft Fabric✅Azure Data Explorer✅Azure Monitor✅Microsoft Sentinel

As funções de agregação permitem agrupar e combinar dados de várias linhas em um valor de resumo. O valor de resumo depende da função escolhida, por exemplo, um valor de contagem, máximo ou médio.

Neste tutorial, você aprenderá como:

Este tutorial se baseia na base do primeiro tutorial, Aprenda operadores comuns.

Pré-requisitos

Para executar as consultas a seguir, você precisa de um ambiente de consulta com acesso aos dados de exemplo. Você pode usar um dos itens a seguir:

Usar o operador summarize

O operador summarize é essencial para realizar agregações em seus dados. O summarize operador agrupa by linhas com base na cláusula e, em seguida, usa a função de agregação fornecida para combinar cada grupo em uma única linha.

Encontre o número de eventos por estado usando summarize com a função de agregação de contagem .

StormEvents
| summarize TotalStorms = count() by State

Saída

Visualizar resultados da consulta

A visualização dos resultados da consulta em um gráfico pode ajudá-lo a identificar padrões, tendências e discrepâncias em seus dados. Você pode fazer isso com o operador de renderização .

Ao longo do tutorial, você verá exemplos de como usar render para exibir seus resultados. Por enquanto, vamos usar render para ver os resultados da consulta anterior em um gráfico de barras.

StormEvents
| summarize TotalStorms = count() by State
| render barchart

Contar linhas condicionalmente

Ao analisar seus dados, use countif() para contar linhas com base em uma condição específica para entender quantas linhas atendem aos critérios fornecidos.

A consulta a seguir usa countif() a contagem de tempestades que causaram danos. Em seguida, a consulta usa o top operador para filtrar os resultados e exibir os estados com a maior quantidade de danos à cultura causados por tempestades.

StormEvents
| summarize StormsWithCropDamage = countif(DamageCrops > 0) by State
| top 5 by StormsWithCropDamage

Saída

Agrupar dados em compartimentos

Para agregar por valores numéricos ou de tempo, primeiro você deve agrupar os dados em compartimentos usando a função bin( ). O uso bin() pode ajudá-lo a entender como os valores são distribuídos dentro de um determinado intervalo e fazer comparações entre diferentes períodos.

A consulta a seguir conta o número de tempestades que causaram danos à cultura em cada semana de 2007. O 7d argumento representa uma semana, pois a função requer um valor de intervalo de tempo válido.

StormEvents
| where StartTime between (datetime(2007-01-01) .. datetime(2007-12-31)) 
    and DamageCrops > 0
| summarize EventCount = count() by bin(StartTime, 7d)

Saída

Adicione | render timechart ao final da consulta para visualizar os resultados.

Calcule o mínimo, máximo, médio e soma

Para saber mais sobre os tipos de tempestades que causam danos à cultura, calcule os danos à colheita min(), max() e avg() para cada tipo de evento e classifique o resultado pelo dano médio.

Observe que você pode usar várias funções de agregação em um único summarize operador para produzir várias colunas computadas.

StormEvents
| where DamageCrops > 0
| summarize
    MaxCropDamage=max(DamageCrops), 
    MinCropDamage=min(DamageCrops), 
    AvgCropDamage=avg(DamageCrops)
    by EventType
| sort by AvgCropDamage

Saída

Os resultados da consulta anterior indicam que os eventos de geada/congelamento resultaram na maioria dos danos à cultura, em média. No entanto, a consulta bin() mostrou que os eventos com danos às culturas ocorreram principalmente nos meses de verão.

Use sum() para verificar o número total de culturas danificadas em vez da quantidade de eventos que causaram algum dano, como feito na count() consulta bin() anterior.

StormEvents
| where StartTime between (datetime(2007-01-01) .. datetime(2007-12-31)) 
    and DamageCrops > 0
| summarize CropDamage = sum(DamageCrops) by bin(StartTime, 7d)
| render timechart

Agora você pode ver um pico nos danos às colheitas em janeiro, que provavelmente foi devido ao Frost / Freeze.

Calcular porcentagens

O cálculo de porcentagens pode ajudá-lo a entender a distribuição e a proporção de diferentes valores em seus dados. Esta seção aborda dois métodos comuns para calcular porcentagens com a KQL (Linguagem de Consulta Kusto).

Calcular porcentagem com base em duas colunas

Use count() e countif para encontrar a porcentagem de eventos de tempestade que causaram danos às culturas em cada estado. Primeiro, conte o número total de tempestades em cada estado. Em seguida, conte o número de tempestades que causaram danos às plantações em cada estado.

Em seguida, use extend para calcular a porcentagem entre as duas colunas dividindo o número de tempestades com danos à cultura pelo número total de tempestades e multiplicando por 100.

Para garantir que você obtenha um resultado decimal, use a função todouble() para converter pelo menos um dos valores de contagem de inteiros em um double antes de realizar a divisão.

StormEvents
| summarize 
    TotalStormsInState = count(),
    StormsWithCropDamage = countif(DamageCrops > 0)
    by State
| extend PercentWithCropDamage = 
    round((todouble(StormsWithCropDamage) / TotalStormsInState * 100), 2)
| sort by StormsWithCropDamage

Saída

Calcular porcentagem com base no tamanho da tabela

Para comparar o número de tempestades por tipo de evento com o número total de tempestades no banco de dados, primeiro salve o número total de tempestades no banco de dados como uma variável. As instruções let são usadas para definir variáveis em uma consulta.

Como as instruções de expressão tabular retornam resultados tabulares, use a função toscalar() para converter o count() resultado tabular da função em um valor escalar. Em seguida, o valor numérico pode ser usado no cálculo da porcentagem.

let TotalStorms = toscalar(StormEvents | summarize count());
StormEvents
| summarize EventCount = count() by EventType
| project EventType, EventCount, Percentage = todouble(EventCount) / TotalStorms * 100.0

Saída

Extrair valores exclusivos

Use make_set() para transformar uma seleção de linhas em uma tabela em uma matriz de valores exclusivos.

A consulta a seguir usa make_set() para criar uma matriz dos tipos de evento que causam mortes em cada estado. A tabela resultante é então classificada pelo número de tipos de tempestade em cada matriz.

StormEvents
| where DeathsDirect > 0 or DeathsIndirect > 0
| summarize StormTypesWithDeaths = make_set(EventType) by State
| project State, StormTypesWithDeaths
| sort by array_length(StormTypesWithDeaths)

Saída

Dados do bucket por condição

A função case() agrupa dados em buckets com base em condições especificadas. A função retorna a expressão de resultado correspondente para o primeiro predicado satisfeito ou a expressão else final se nenhum dos predicados for atendido.

Este exemplo agrupa os estados com base no número de ferimentos relacionados à tempestade que seus cidadãos sofreram.

StormEvents
| summarize InjuriesCount = sum(InjuriesDirect) by State
| extend InjuriesBucket = case (
                              InjuriesCount > 50,
                              "Large",
                              InjuriesCount > 10,
                              "Medium",
                              InjuriesCount > 0,
                              "Small",
                              "No injuries"
                          )
| sort by State asc

Saída

Crie um gráfico de pizza para visualizar a proporção de estados que sofreram tempestades resultando em um grande, médio ou pequeno número de feridos.

StormEvents
| summarize InjuriesCount = sum(InjuriesDirect) by State
| extend InjuriesBucket = case (
                              InjuriesCount > 50,
                              "Large",
                              InjuriesCount > 10,
                              "Medium",
                              InjuriesCount > 0,
                              "Small",
                              "No injuries"
                          )
| summarize InjuryBucketByState=count() by InjuriesBucket
| render piechart 

Executar agregações em uma janela deslizante

O exemplo a seguir mostra como resumir colunas usando uma janela deslizante.

A consulta calcula os danos materiais mínimos, máximos e médios de tornados, inundações e incêndios florestais usando uma janela deslizante de sete dias. Cada registro agregará os sete dias anteriores no conjunto de resultados. Além disso, os resultados conterão um registro por dia no período de análise.

Aqui está uma explicação passo a passo da consulta:

1. Agrupe cada registro em um único dia em relação a windowStart.
2. Adicione sete dias ao valor do compartimento para definir o final do intervalo para cada registro. Se o valor estiver fora do intervalo de windowStart e windowEnd, ajuste o valor de acordo.
3. Crie uma matriz de sete dias para cada registro, a partir do dia atual do registro.
4. Expanda a matriz da etapa 3 com mv-expand para duplicar cada registro para sete registros com intervalos de um dia entre eles.
5. Execute as agregações para cada dia. Devido à etapa 4, esta etapa realmente resume os sete dias anteriores.
6. Exclua os primeiros sete dias do resultado final, pois não há um período de lookback de sete dias para eles.

let windowStart = datetime(2007-07-01);
let windowEnd = windowStart + 13d;
StormEvents
| where EventType in ("Tornado", "Flood", "Wildfire") 
| extend bin = bin_at(startofday(StartTime), 1d, windowStart) // 1
| extend endRange = iff(bin + 7d > windowEnd, windowEnd, 
                      iff(bin + 7d - 1d < windowStart, windowStart, 
                        iff(bin + 7d - 1d < bin, bin, bin + 7d - 1d))) // 2
| extend range = range(bin, endRange, 1d) // 3
| mv-expand range to typeof(datetime) // 4
| summarize min(DamageProperty), max(DamageProperty), round(avg(DamageProperty)) by Timestamp=bin_at(range, 1d, windowStart), EventType // 5
| where Timestamp >= windowStart + 7d; // 6

Saída

A tabela de resultados a seguir é truncada. Para ver a saída completa, execute a consulta.

Próxima etapa

Agora que você está familiarizado com operadores de consulta comuns e funções de agregação, vá para o próximo tutorial para saber como unir dados de várias tabelas.