Share via

operador top-nested

  • Artigo

O top-nested operador executa a agregação hierárquica e a seleção de valor.

Imagine que você tenha uma tabela com informações de vendas, como regiões, vendedores e valores vendidos. O top-nested operador pode ajudá-lo a responder perguntas complexas, como "Quais são as cinco principais regiões por vendas e quem são os três principais vendedores em cada uma dessas regiões?"

Os dados de origem são particionados com base nos critérios definidos na primeira top-nested cláusula, como região. Em seguida, o operador escolhe os principais registros em cada partição usando uma agregação, como adicionar valores de vendas. Cada cláusula subsequente top-nested refina as partições criadas pela cláusula anterior, criando uma hierarquia de grupos mais precisos.

O resultado é uma tabela com duas colunas por cláusula. Uma coluna contém os valores de particionamento, como região, enquanto a outra coluna contém os resultados do cálculo de agregação, como a soma das vendas.

Syntax

T|top-nested [ N ] ofExpr [withothers=ConstExpr] byAgregação [asc | desc] [,
  top-nested ... ]

Saiba mais sobre as convenções de sintaxe.

Parâmetros

Nome Tipo Obrigatório Descrição
T string ✔️ A expressão tabular de entrada.
N int O número de valores principais a serem retornados para esse nível de hierarquia. Se omitido, todos os valores distintos serão retornados.
Expr string ✔️ Uma expressão sobre o registro de entrada que indica qual valor retornar para esse nível de hierarquia. Normalmente, ele se refere a uma coluna de T ou envolve um cálculo como bin() em uma coluna. Opcionalmente, defina um nome de coluna de saída como Nome=Expr.
ConstExpr string Se especificado, para cada nível de hierarquia, um registro é adicionado com o valor que é a agregação em todos os registros que não chegaram à parte superior.
Agregação string A função de agregação aplicada a registros com o mesmo valor Expr . O resultado determina os registros principais. Confira Funções de agregação com suporte. Opcionalmente, defina um nome de coluna de saída comoAgregação deNome=.

Funções de agregação com suporte

Há suporte para as seguintes funções de agregação:

Observação

Também há suporte para qualquer combinação algébrica das agregações.

Retornos

Uma tabela com duas colunas para cada cláusula. Uma coluna contém valores exclusivos calculados usando Expr e a outra coluna mostra os resultados obtidos do cálculo de Agregação .

Incluir dados de outras colunas

Somente colunas especificadas como uma top-nested cláusula Expr são exibidas na tabela de saída.

Para incluir todos os valores de uma coluna em um nível específico:

  1. Não especifique o valor de N.
  2. Use o nome da coluna como o valor de Expr.
  3. Use Ignore=max(1) como o valor de Agregação.
  4. Remova a coluna desnecessária Ignore com project-away.

Para obter um exemplo, consulte Obter os eventos mais recentes por estado com dados adicionais de outras colunas.

Considerações sobre o desempenho

O número de registros pode crescer exponencialmente com o número de top-nested cláusulas, e o crescimento recorde é ainda mais rápido se nenhum limite N for especificado. Esse operador pode consumir uma quantidade considerável de recursos.

Se a distribuição da agregação for consideravelmente não uniforme, limite o número de valores distintos a serem retornados especificando N. Em seguida, use a withothers= especificação ConstExpr para obter uma indicação do peso de todos os outros casos.

Exemplos

Introdução ao top-nested operador

A consulta a seguir particiona a StormEvents tabela pela State coluna e calcula a latitude total para cada estado. A consulta seleciona os dois principais estados com a soma de latitude mais alta. Nesses dois estados principais, a consulta agrupa os dados por Source e seleciona as três principais fontes com a soma de latitude mais alta. Para cada uma das três principais fontes nos dois principais estados, a consulta agrupa os dados por EndLocation e seleciona o EndLocation com a soma de latitude mais alta.

Executar a consulta

StormEvents                                        // Data source.
| top-nested 2 of State       by sum(BeginLat),    // Top 2 States by total latitude.
  top-nested 3 of Source      by sum(BeginLat),    // Top 3 Sources by total latitude in each State.
  top-nested 1 of EndLocation by sum(BeginLat)     // Top 1 EndLocation by total latitude in each Source and State.

Saída

Estado aggregated_State Source aggregated_Source EndLocation aggregated_EndLocation
KANSAS 87771.2355000001 Aplicação da lei 18744.823 FT SCOTT 264.858
KANSAS 87771.2355000001 Público 22855.6206 BUCKLIN 488.2457
KANSAS 87771.2355000001 Spotter Treinado 21279.7083 SHARON SPGS 388.7404
TEXAS 123400.5101 Público 13650.9079 AMARILLO 246.2598
TEXAS 123400.5101 Aplicação da lei 37228.5966 PERRYTON 289.3178
TEXAS 123400.5101 Spotter Treinado 13997.7124 CLAUDE 421.44

Aprimorar os resultados aninhados superiores com dados de outra coluna

A consulta a seguir baseia-se no exemplo anterior introduzindo uma cláusula extra top-nested . Nessa nova cláusula, a ausência de uma especificação numérica resulta na extração de todos os valores distintos de EventType entre as partições. A max(1) função de agregação é apenas um espaço reservado, tornando seu resultado irrelevante, portanto, o operador project-away remove a Ignore coluna. O resultado mostra todos os tipos de evento associados aos dados agregados anteriormente.

Executar a consulta

StormEvents
| top-nested 2 of State       by sum(BeginLat),
  top-nested 3 of Source      by sum(BeginLat),
  top-nested 1 of EndLocation by sum(BeginLat),
  top-nested   of EventType   by Ignore = max(1)
| project-away Ignore

Saída

Estado aggregated_State Source aggregated_Source EndLocation aggregated_EndLocation EventType
TEXAS 123400.51009999994 Público 13650.907900000002 AMARILLO 246.25979999999998 Granizo
TEXAS 123400.51009999994 Público 13650.907900000002 AMARILLO 246.25979999999998 Thunderstorm Wind
KANSAS 87771.235500000068 Público 22855.6206 BUCKLIN 488.2457 Saturação
KANSAS 87771.235500000068 Público 22855.6206 BUCKLIN 488.2457 Thunderstorm Wind
KANSAS 87771.235500000068 Público 22855.6206 BUCKLIN 488.2457 Granizo
TEXAS 123400.51009999994 Spotter Treinado 13997.712400000009 CLAUDE 421.44 Granizo
KANSAS 87771.235500000068 Aplicação da lei 18744.823000000004 FT SCOTT 264.858 Saturação de Flash
KANSAS 87771.235500000068 Aplicação da lei 18744.823000000004 FT SCOTT 264.858 Thunderstorm Wind
KANSAS 87771.235500000068 Aplicação da lei 18744.823000000004 FT SCOTT 264.858 Saturação
TEXAS 123400.51009999994 Aplicação da lei 37228.596599999961 PERRYTON 289.3178 Granizo
... ... ... ... ... ...

Usar withothers para explorar dados excluídos

Quando incorporada em uma top-nested cláusula , a withothers especificação introduz um registro extra que agrega dados excluídos dos principais resultados. Na consulta a seguir, um registro extra é criado nas State colunas e aggregated_State , representando a latitude coletiva de todos os estados, exceto Kansas e Texas. Além disso, a EndLocation coluna e aggregated_EndLocation têm mais nove registros. Esses registros mostram que a latitude combinada dos locais finais não se qualifica como o local superior em cada estado e origem.

Executar a consulta

StormEvents
| top-nested 2 of State with others = "All Other States" by sum(BeginLat),
  top-nested 3 of Source by sum(BeginLat),
  top-nested 1 of EndLocation with others = "All Other End Locations" by sum(BeginLat)

Saída

Estado aggregated_State Source aggregated_Source EndLocation aggregated_EndLocation
KANSAS 87771.2355000001 Aplicação da lei 18744.823 FT SCOTT 264.858
KANSAS 87771.2355000001 Público 22855.6206 BUCKLIN 488.2457
KANSAS 87771.2355000001 Spotter Treinado 21279.7083 SHARON SPGS 388.7404
TEXAS 123400.5101 Público 13650.9079 AMARILLO 246.2598
TEXAS 123400.5101 Aplicação da lei 37228.5966 PERRYTON 289.3178
TEXAS 123400.5101 Spotter Treinado 13997.7124 CLAUDE 421.44
KANSAS 87771.2355000001 Aplicação da lei 18744.823 Todos os outros locais finais 18479.965
KANSAS 87771.2355000001 Público 22855.6206 Todos os outros locais finais 22367.3749
KANSAS 87771.2355000001 Spotter Treinado 21279.7083 Todos os outros locais finais 20890.9679
TEXAS 123400.5101 Público 13650.9079 Todos os outros locais finais 13404.6481
TEXAS 123400.5101 Aplicação da lei 37228.5966 Todos os outros locais finais 36939.2788
TEXAS 123400.5101 Spotter Treinado 13997.7124 Todos os outros locais finais 13576.2724
KANSAS 87771.2355000001 Todos os outros locais finais 24891.0836
TEXAS 123400.5101 Todos os outros locais finais 58523.2932000001
Todos os Outros Estados 1149279.5923 Todos os outros locais finais 1149279.5923

A consulta a seguir mostra os mesmos resultados para o primeiro nível usado no exemplo anterior.

Executar a consulta

StormEvents
| where State !in ('TEXAS', 'KANSAS')
| summarize sum(BeginLat)

Saída

sum_BeginLat
1149279.5923

Classificar resultados hierárquicos

Para obter uma ordem de classificação abrangente, a consulta a seguir usa a classificação baseada em índice para cada valor dentro do nível de hierarquia atual, por grupo. Essa classificação é voltada para organizar o resultado de acordo com o nível aninhado final, nesse caso o EndLocation.

Executar a consulta

StormEvents
| top-nested 2 of State by sum(BeginLat),
  top-nested 2 of Source by sum(BeginLat),
  top-nested 4 of EndLocation by sum(BeginLat)
| sort by State, Source, aggregated_EndLocation
| summarize
    EndLocations = make_list(EndLocation, 10000),
    endLocationSums = make_list(aggregated_EndLocation, 10000)
    by State, Source
| extend indicies = range(0, array_length(EndLocations) - 1, 1)
| mv-expand EndLocations, endLocationSums, indicies

Saída

Estado Source EndLocations endLocationSums índices
TEXAS Spotter Treinado CLAUDE 421.44 0
TEXAS Spotter Treinado AMARILLO 316.8892 1
TEXAS Spotter Treinado DALHART 252.6186 2
TEXAS Spotter Treinado PERRYTON 216.7826 3
TEXAS Aplicação da lei PERRYTON 289.3178 0
TEXAS Aplicação da lei LEAKEY 267.9825 1
TEXAS Aplicação da lei BRACKETTVILLE 264.3483 2
TEXAS Aplicação da lei GILMER 261.9068 3
KANSAS Spotter Treinado SHARON SPGS 388.7404 0
KANSAS Spotter Treinado ATWOOD 358.6136 1
KANSAS Spotter Treinado LENORA 317.0718 2
KANSAS Spotter Treinado SCOTT CITY 307.84 3
KANSAS Público BUCKLIN 488.2457 0
KANSAS Público ASHLAND 446.4218 1
KANSAS Público PROTEÇÃO 446.11 2
KANSAS Público PARQUE ESTADUAL MEADE 371.1 3

Obter os eventos mais recentes por estado com dados adicionais de outras colunas

A consulta a seguir demonstra como recuperar os dois eventos mais recentes para cada estado dos EUA, juntamente com detalhes relevantes do evento. Observe o uso de max(1) em determinadas colunas, identificadas por Ignore*, o que ajuda a propagar dados por meio da consulta sem impor nenhuma lógica de seleção.

Executar a consulta

StormEvents
| top-nested of State by Ignore0=max(1),                  // Partition the data by each unique value of state.
  top-nested 2 of StartTime by Ignore1=max(StartTime),    // Get the 2 most recent events in each state.
  top-nested of EndTime by Ignore2=max(1),                // Append the EndTime for each event.
  top-nested of EpisodeId by Ignore3=max(1)               // Append the EpisodeId for each event.
| project-away Ignore*                                    // Remove the unnecessary aggregation columns.
| order by State asc, StartTime desc                      // Sort results alphabetically and chronologically.

Obter os registros mais recentes por identidade com dados adicionais de outras colunas

A consulta a seguir mostra como extrair os registros mais recentes por identidade e se baseia nos conceitos introduzidos no exemplo anterior. A primeira top-nested cláusula particiona os dados por valores distintos de id. A cláusula subsequente identifica os dois registros mais recentes com base no timestamp para cada id. Outras informações são acrescentadas usando um top-nested operador junto com uma contagem não especificada e a agregação arbitrária max(1) . Por fim, colunas de agregação desnecessárias são removidas usando o project-away operador .

Executar a consulta

datatable(id: string, timestamp: datetime, otherInformation: string) // Create a source datatable.
[
    "Barak", datetime(2015-01-01), "1",
    "Barak", datetime(2016-01-01), "2",
    "Barak", datetime(2017-01-20), "3",
    "Donald", datetime(2017-01-20), "4",
    "Donald", datetime(2017-01-18), "5",
    "Donald", datetime(2017-01-19), "6"
]
| top-nested of id by Ignore0=max(1),                     // Partition the data by each unique value of id.
  top-nested 2 of timestamp by Ignore1=max(timestamp),    // Get the 2 most recent events for each state.
  top-nested of otherInformation by Ignore2=max(1)        // Append otherInformation for each event.
| project-away Ignore0, Ignore1, Ignore2                  // Remove the unnecessary aggregation columns.

Saída

id timestamp otherInformation
Barak 2016-01-01T00:00:00Z 2
Donald 2017-01-19T00:00:00Z 6
Barak 2017-01-20T00:00:00Z 3
Donald 2017-01-20T00:00:00Z 4

Conteúdo relacionado