top-nested operator

  • Artículo

El top-nested operador realiza la agregación jerárquica y la selección de valores.

Imagine que tiene una tabla con información de ventas como regiones, vendedores y cantidades vendidas. El top-nested operador puede ayudarle a responder preguntas complejas, como "¿Cuáles son las cinco principales regiones por ventas y quiénes son los tres principales vendedores de cada una de esas regiones?"

Los datos de origen se particionan en función de los criterios establecidos en la primera top-nested cláusula, como region. A continuación, el operador elige los registros principales de cada partición mediante una agregación, como agregar cantidades de ventas. Cada cláusula posterior top-nested refina las particiones creadas por la cláusula anterior, creando una jerarquía de grupos más precisos.

El resultado es una tabla con dos columnas por cláusula. Una columna contiene los valores de creación de particiones, como región, mientras que la otra columna contiene los resultados del cálculo de agregación, como la suma de ventas.

Syntax

T|top-nested [ N ] ofExpr [withothers=ConstExpr] byAggregation [asc | desc] [,
  top-nested ... ]

Obtenga más información sobre las convenciones de sintaxis.

Parámetros

Nombre Tipo Requerido Descripción
T string ✔️ Expresión tabular de entrada.
N. int Número de valores principales que se van a devolver para este nivel de jerarquía. Si se omite, se devuelven todos los valores distintos.
Expr string ✔️ Expresión sobre el registro de entrada que indica qué valor se va a devolver para este nivel de jerarquía. Normalmente, hace referencia a una columna de T o implica un cálculo como bin() en una columna. Opcionalmente, establezca un nombre de columna de salida como Name=Expr.
ConstExpr string Si se especifica, para cada nivel de jerarquía, se agrega un registro con el valor que es la agregación en todos los registros que no lo han hecho en la parte superior.
Agregación string Función de agregación aplicada a los registros con el mismo valor expr . El resultado determina los registros principales. Consulte Funciones de agregación admitidas. Opcionalmente, establezca un nombre de columna de salida como Agregación de= nombres.

Funciones de agregación admitidas

Se admiten las siguientes funciones de agregación:

Nota

También se admite cualquier combinación algebraica de las agregaciones.

Devoluciones

Tabla con dos columnas para cada cláusula. Una columna contiene valores únicos calculados mediante Expr y la otra columna muestra los resultados obtenidos del cálculo de agregación .

Incluir datos de otras columnas

Solo se muestran las columnas especificadas como cláusula top-nestedExpr en la tabla de salida.

Para incluir todos los valores de una columna en un nivel específico:

  1. No especifique el valor de N.
  2. Use el nombre de columna como valor de Expr.
  3. Use Ignore=max(1) como valor de Aggregation.
  4. Quite la columna innecesaria Ignore con project-away.

Para obtener un ejemplo, consulte Obtención de los eventos más recientes por estado con datos adicionales de otras columnas.

Consideraciones de rendimiento

El número de registros puede crecer exponencialmente con el número de cláusulas y el crecimiento del top-nested registro es incluso más rápido si no se especifica ningún límite N . Este operador puede consumir una cantidad considerable de recursos.

Si la distribución de la agregación es considerablemente no uniforme, limite el número de valores distintos que se van a devolver especificando N. A continuación, use la withothers= especificación ConstExpr para obtener una indicación del peso de todos los demás casos.

Ejemplos

Introducción al top-nested operador

La siguiente consulta crea particiones de la StormEvents tabla por la State columna y calcula la latitud total de cada estado. La consulta selecciona los dos estados principales con la suma de latitud más alta. Dentro de estos dos estados principales, la consulta agrupa los datos por Source y selecciona los tres orígenes principales con la suma de latitud más alta. Para cada uno de los tres orígenes principales de los dos estados principales, la consulta agrupa los datos por EndLocation y selecciona con EndLocation la suma de latitud más alta.

Ejecución de la 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.

Salida

State aggregated_State Source aggregated_Source EndLocation aggregated_EndLocation
KANSAS 87771.2355000001 Cuerpos de seguridad 18744.823 FT SCOTT 264.858
KANSAS 87771.2355000001 Público 22855.6206 BUCKLIN 488.2457
KANSAS 87771.2355000001 Observador entrenado 21279.7083 SHARON SPGS 388.7404
TEXAS 123400.5101 Público 13650.9079 AMARILLO 246.2598
TEXAS 123400.5101 Cuerpos de seguridad 37228.5966 PERRYTON 289.3178
TEXAS 123400.5101 Observador entrenado 13997.7124 CLAUDE 421.44

Mejora de los resultados anidados superiores con datos de otra columna

La consulta siguiente se basa en el ejemplo anterior mediante la introducción de una cláusula adicional top-nested . En esta nueva cláusula, la ausencia de una especificación numérica da como resultado la extracción de todos los valores distintos de EventType entre las particiones. La max(1) función de agregación es simplemente un marcador de posición, lo que representa su resultado irrelevante, por lo que el operador de proyecto quita la Ignore columna. El resultado muestra todos los tipos de eventos asociados a los datos agregados anteriormente.

Ejecución de la 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

Salida

State 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 Viento de tormenta
KANSAS 87771.235500000068 Público 22855.6206 BUCKLIN 488.2457 Inundación
KANSAS 87771.235500000068 Público 22855.6206 BUCKLIN 488.2457 Viento de tormenta
KANSAS 87771.235500000068 Público 22855.6206 BUCKLIN 488.2457 Granizo
TEXAS 123400.51009999994 Observador entrenado 13997.712400000009 CLAUDE 421.44 Granizo
KANSAS 87771.235500000068 Cuerpos de seguridad 18744.823000000004 FT SCOTT 264.858 Riada
KANSAS 87771.235500000068 Cuerpos de seguridad 18744.823000000004 FT SCOTT 264.858 Viento de tormenta
KANSAS 87771.235500000068 Cuerpos de seguridad 18744.823000000004 FT SCOTT 264.858 Inundación
TEXAS 123400.51009999994 Cuerpos de seguridad 37228.596599999961 PERRYTON 289.3178 Granizo
... ... ... ... ... ...

Uso withothers para explorar los datos excluidos

Cuando se incorpora dentro de una top-nested cláusula , la withothers especificación introduce un registro adicional que agrega los datos excluidos de los resultados principales. En la consulta siguiente, se crea un registro adicional en las State columnas y aggregated_State , que representa la latitud colectiva de todos los estados excepto Kansas y Texas. Además, la EndLocation columna y aggregated_EndLocation tienen nueve registros adicionales. Estos registros muestran la latitud combinada de las ubicaciones finales que no se califican como la ubicación superior dentro de cada estado y origen.

Ejecución de la 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)

Salida

State aggregated_State Source aggregated_Source EndLocation aggregated_EndLocation
KANSAS 87771.2355000001 Cuerpos de seguridad 18744.823 FT SCOTT 264.858
KANSAS 87771.2355000001 Público 22855.6206 BUCKLIN 488.2457
KANSAS 87771.2355000001 Observador entrenado 21279.7083 SHARON SPGS 388.7404
TEXAS 123400.5101 Público 13650.9079 AMARILLO 246.2598
TEXAS 123400.5101 Cuerpos de seguridad 37228.5966 PERRYTON 289.3178
TEXAS 123400.5101 Observador entrenado 13997.7124 CLAUDE 421.44
KANSAS 87771.2355000001 Cuerpos de seguridad 18744.823 Todas las demás ubicaciones finales 18479.965
KANSAS 87771.2355000001 Público 22855.6206 Todas las demás ubicaciones finales 22367.3749
KANSAS 87771.2355000001 Observador entrenado 21279.7083 Todas las demás ubicaciones finales 20890.9679
TEXAS 123400.5101 Público 13650.9079 Todas las demás ubicaciones finales 13404.6481
TEXAS 123400.5101 Cuerpos de seguridad 37228.5966 Todas las demás ubicaciones finales 36939.2788
TEXAS 123400.5101 Observador entrenado 13997.7124 Todas las demás ubicaciones finales 13576.2724
KANSAS 87771.2355000001 Todas las demás ubicaciones finales 24891.0836
TEXAS 123400.5101 Todas las demás ubicaciones finales 58523.2932000001
Todos los demás estados 1149279.5923 Todas las demás ubicaciones finales 1149279.5923

En la consulta siguiente se muestran los mismos resultados para el primer nivel usado en el ejemplo anterior.

Ejecución de la consulta

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

Salida

sum_BeginLat
1149279.5923

Ordenar resultados jerárquicos

Para lograr un criterio de ordenación completo, la consulta siguiente usa la ordenación basada en índices para cada valor dentro del nivel de jerarquía actual, por grupo. Esta ordenación está orientada a organizar el resultado según el nivel anidado final, en este caso el EndLocation.

Ejecución de la 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

Salida

State Source EndLocations endLocationSums índices
TEXAS Observador entrenado CLAUDE 421.44 0
TEXAS Observador entrenado AMARILLO 316.8892 1
TEXAS Observador entrenado DALHART 252.6186 2
TEXAS Observador entrenado PERRYTON 216.7826 3
TEXAS Cuerpos de seguridad PERRYTON 289.3178 0
TEXAS Cuerpos de seguridad LEAKEY 267.9825 1
TEXAS Cuerpos de seguridad BRACKETTVILLE 264.3483 2
TEXAS Cuerpos de seguridad GILMER 261.9068 3
KANSAS Observador entrenado SHARON SPGS 388.7404 0
KANSAS Observador entrenado ATWOOD 358.6136 1
KANSAS Observador entrenado LENORA 317.0718 2
KANSAS Observador entrenado SCOTT CITY 307.84 3
KANSAS Público BUCKLIN 488.2457 0
KANSAS Público ASHLAND 446.4218 1
KANSAS Público PROTECCIÓN 446.11 2
KANSAS Público PARQUE ESTATAL DE MEADE 371.1 3

Obtención de los eventos más recientes por estado con datos adicionales de otras columnas

En la consulta siguiente se muestra cómo recuperar los dos eventos más recientes para cada estado de EE. UU. junto con los detalles pertinentes del evento. Observe el uso de dentro de determinadas columnas, identificadas por Ignore*, que ayuda a propagar datos a través de max(1) la consulta sin imponer ninguna lógica de selección.

Ejecución de la 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.

Obtención de los registros más recientes por identidad con datos adicionales de otras columnas

En la consulta siguiente se muestra cómo extraer los registros más recientes por identidad y se basa en los conceptos introducidos en el ejemplo anterior. La primera top-nested cláusula particiona los datos por valores distintos de id. La cláusula subsiguiente identifica los dos registros más recientes basados en para timestamp cada id. Otra información se anexa mediante un operador junto con un top-nested recuento no especificado y la agregación arbitraria max(1) . Por último, las columnas de agregación innecesarias se quitan mediante el project-away operador .

Ejecución de la 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.

Salida

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

Contenido relacionado