top-nested 运算符top-nested operator

生成分层聚合和前位值选择,其中每个级别都是前一级别的优化。Produces a hierarchical aggregation and top values selection, where each level is a refinement of the previous one.

T | top-nested 3 of Location with others="Others" by sum(MachinesNumber), top-nested 4 of bin(Timestamp,5m) by sum(MachinesNumber)

top-nested 运算符接受表格数据作为输入,以及一个或多个聚合子句。The top-nested operator accepts tabular data as input, and one or more aggregation clauses. 第一个聚合子句(最左侧)根据针对这些记录的某个表达式的惟一值,将输入记录细分为多个分区。The first aggregation clause (left-most) subdivides the input records into partitions, according to the unique values of some expression over those records. 然后,子句会持有一定数量的记录,这可以最大化或最小化针对记录的这个表达式。The clause then keeps a certain number of records that maximize or minimize this expression over the records. 然后,下一个聚合子句以嵌套方式应用一个类似的函数。The next aggregation clause then applies a similar function, in a nested fashion. 以下每个子句都应用于前一个子句生成的分区。Each following clause is applied to the partition produced by the previous clause. 将持续为所有聚合子句执行此过程。This process continues for all aggregation clauses.

例如,top-nested 运算符可用于解答以下问题:“对于一个包含销售数据(如国家/地区、销售人员和销售数量)的表:按销售额排名前五位的国家/地区分别有哪些?For example, the top-nested operator can be used to answer the following question: "For a table containing sales figures, such as country, salesperson, and amount sold: what are the top five countries by sales? 每个国家/地区排名前三的销售人员是哪几位?”What are the top three salespeople in each of these countries?"

语法Syntax

T | top-nested TopNestedClause2 [, TopNestedClause2 ...]T | top-nested TopNestedClause2 [, TopNestedClause2 ...]

其中 TopNestedClause 具有以下语法:Where TopNestedClause has the following syntax:

[ N ] of [ ExprName =] Expr [with others = ConstExpr ] by [ AggName =] Aggregation [asc | desc][ N ] of [ ExprName =] Expr [with others = ConstExpr ] by [ AggName =] Aggregation [asc | desc]

参数Arguments

对于每个 TopNestedClause:For each TopNestedClause :

  • Nlong 类型的文本,指示要为此层次结构级别返回多少前位值。N : A literal of type long indicating how many top values to return for this hierarchy level. 如果省略,将返回所有非重复值。If omitted, all distinct values will be returned.

  • ExprName :如果指定,则设置与 Expr 值相对应的输出列的名称。ExprName : If specified, sets the name of the output column corresponding to the values of Expr .

  • Expr :针对输入记录的表达式,指示要为此层次结构级别返回的值。Expr : An expression over the input record indicating which value to return for this hierarchy level. 通常它是表格式输入 (T) 的列引用,或针对此类列的某些计算(如 bin())的列引用。Typically it's a column reference for the tabular input ( T ), or some calculation (such as bin()) over such a column.

  • ConstExpr :如果指定,对于每个层次结构级别,将添加 1 个记录,其值是所有未“使其成为前位值”的记录的聚合值。ConstExpr : If specified, for each hierarchy level, 1 record will be added with the value that is the aggregation over all records that didn't "make it to the top".

  • AggName :如果指定此标识符,则该标识符将在输出中为 Aggregation 的值设置列名称。AggName : If specified, this identifier sets the column name in the output for the value of Aggregation .

  • Aggregation :一个数值表达式,指示要应用于具有相同值 Expr 的所有记录的聚合。Aggregation : A numeric expression indicating the aggregation to apply to all records sharing the same value of Expr . 这个聚合的值决定了得到的记录中哪些排在“前位”。The value of this aggregation determines which of the resulting records are "top".

    支持以下聚合函数:The following aggregation functions are supported:

  • ascdesc(默认)可能会控制实际从聚合值范围“底部”还是“顶部”进行选择。asc or desc (the default) may appear to control whether selection is actually from the "bottom" or "top" of the range of aggregated values.

返回Returns

此运算符返回一个表,其中每个聚合子句有两列:This operator returns a table that has two columns for each aggregation clause:

  • 其中一列包含子句的 Expr 计算的非重复值(列名为 ExprName,如果指定了的话) One column holds the distinct values of the clause's Expr calculation (having the column name ExprName if specified)

  • 其中一列保存 Aggregation 计算的结果(列名为 AggregationName,如果指定了的话) One column holds the result of the Aggregation calculation (having the column name AggregationName if specified)

说明Notes

不输出未指定为 Expr 值的输入列。Input columns that aren't specified as Expr values aren't outputted. 若要获取特定级别的所有值,请添加一个聚合计数:To get all values at a certain level, add an aggregation count that:

  • 省略 N 的值Omits the value of N
  • 使用列名称作为 Expr 的值Uses the column name as the value of Expr
  • 使用 Ignore=max(1) 作为该聚合,然后忽略(或 project-away)Ignore 列。Uses Ignore=max(1) as the aggregation, and then ignore (or project-away) the column Ignore.

记录的数量可能随着聚合子句 ((N1+1) * (N2+1) * ...) 的数量呈指数增长。如果未指定 N 限制,记录会增长更快。The number of records may grow exponentially with the number of aggregation clauses ((N1+1) * (N2+1) * ...). Record growth is even faster if no N limit is specified. 使用时应考虑到此运算符可能会消耗大量的资源。Take into account that this operator may consume a considerable amount of resources.

如果聚合的分布相当不均匀,请限制返回的非重复值的数量(通过使用 N),并使用 with others= ConstExpr 选项来获得所有其他情况的“权重”指示 。If the distribution of the aggregation is considerably non-uniform, limit the number of distinct values to return (by using N ) and use the with others= ConstExpr option to get an indication for the "weight" of all other cases.

示例Examples

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)
状态State aggregated_Stateaggregated_State SourceSource aggregated_Sourceaggregated_Source EndLocationEndLocation aggregated_EndLocationaggregated_EndLocation
KANSASKANSAS 87771.235500000187771.2355000001 执法机构Law Enforcement 18744.82318744.823 FT SCOTTFT SCOTT 264.858264.858
KANSASKANSAS 87771.235500000187771.2355000001 公用Public 22855.620622855.6206 BUCKLINBUCKLIN 488.2457488.2457
KANSASKANSAS 87771.235500000187771.2355000001 专业观测员Trained Spotter 21279.708321279.7083 SHARON SPGSSHARON SPGS 388.7404388.7404
德克萨斯TEXAS 123400.5101123400.5101 公用Public 13650.907913650.9079 AMARILLOAMARILLO 246.2598246.2598
德克萨斯TEXAS 123400.5101123400.5101 执法机构Law Enforcement 37228.596637228.5966 PERRYTONPERRYTON 289.3178289.3178
德克萨斯TEXAS 123400.5101123400.5101 专业观测员Trained Spotter 13997.712413997.7124 CLAUDECLAUDE 421.44421.44

Use the option 'with others':Use the option 'with others':

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)


状态State aggregated_Stateaggregated_State SourceSource aggregated_Sourceaggregated_Source EndLocationEndLocation aggregated_EndLocationaggregated_EndLocation
KANSASKANSAS 87771.235500000187771.2355000001 执法机构Law Enforcement 18744.82318744.823 FT SCOTTFT SCOTT 264.858264.858
KANSASKANSAS 87771.235500000187771.2355000001 公用Public 22855.620622855.6206 BUCKLINBUCKLIN 488.2457488.2457
KANSASKANSAS 87771.235500000187771.2355000001 专业观测员Trained Spotter 21279.708321279.7083 SHARON SPGSSHARON SPGS 388.7404388.7404
德克萨斯TEXAS 123400.5101123400.5101 公用Public 13650.907913650.9079 AMARILLOAMARILLO 246.2598246.2598
德克萨斯TEXAS 123400.5101123400.5101 执法机构Law Enforcement 37228.596637228.5966 PERRYTONPERRYTON 289.3178289.3178
德克萨斯TEXAS 123400.5101123400.5101 专业观测员Trained Spotter 13997.712413997.7124 CLAUDECLAUDE 421.44421.44
KANSASKANSAS 87771.235500000187771.2355000001 执法机构Law Enforcement 18744.82318744.823 所有其他结束位置All Other End Locations 18479.96518479.965
KANSASKANSAS 87771.235500000187771.2355000001 公用Public 22855.620622855.6206 所有其他结束位置All Other End Locations 22367.374922367.3749
KANSASKANSAS 87771.235500000187771.2355000001 专业观测员Trained Spotter 21279.708321279.7083 所有其他结束位置All Other End Locations 20890.967920890.9679
德克萨斯TEXAS 123400.5101123400.5101 公用Public 13650.907913650.9079 所有其他结束位置All Other End Locations 13404.648113404.6481
德克萨斯TEXAS 123400.5101123400.5101 执法机构Law Enforcement 37228.596637228.5966 所有其他结束位置All Other End Locations 36939.278836939.2788
德克萨斯TEXAS 123400.5101123400.5101 专业观测员Trained Spotter 13997.712413997.7124 所有其他结束位置All Other End Locations 13576.272413576.2724
KANSASKANSAS 87771.235500000187771.2355000001 所有其他结束位置All Other End Locations 24891.083624891.0836
德克萨斯TEXAS 123400.5101123400.5101 所有其他结束位置All Other End Locations 58523.293200000158523.2932000001
所有其他州All Other States 1149279.59231149279.5923 所有其他结束位置All Other End Locations 1149279.59231149279.5923

以下查询针对上例中使用的第一个级别显示了相同结果。The following query shows the same results for the first level used in the example above.

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

请求在顶部嵌套结果中设置另一列 (EventType)。Request another column (EventType) to the top-nested result.

StormEvents
| top-nested 2 of State by sum(BeginLat),    top-nested 2 of Source by sum(BeginLat),    top-nested 1 of EndLocation by sum(BeginLat), top-nested of EventType  by tmp = max(1)
| project-away tmp
状态State aggregated_Stateaggregated_State SourceSource aggregated_Sourceaggregated_Source EndLocationEndLocation aggregated_EndLocationaggregated_EndLocation EventTypeEventType
KANSASKANSAS 87771.235500000187771.2355000001 专业观测员Trained Spotter 21279.708321279.7083 SHARON SPGSSHARON SPGS 388.7404388.7404 雷雨大风Thunderstorm Wind
KANSASKANSAS 87771.235500000187771.2355000001 专业观测员Trained Spotter 21279.708321279.7083 SHARON SPGSSHARON SPGS 388.7404388.7404 冰雹Hail
KANSASKANSAS 87771.235500000187771.2355000001 专业观测员Trained Spotter 21279.708321279.7083 SHARON SPGSSHARON SPGS 388.7404388.7404 龙卷风Tornado
KANSASKANSAS 87771.235500000187771.2355000001 公用Public 22855.620622855.6206 BUCKLINBUCKLIN 488.2457488.2457 冰雹Hail
KANSASKANSAS 87771.235500000187771.2355000001 公用Public 22855.620622855.6206 BUCKLINBUCKLIN 488.2457488.2457 雷雨大风Thunderstorm Wind
KANSASKANSAS 87771.235500000187771.2355000001 公用Public 22855.620622855.6206 BUCKLINBUCKLIN 488.2457488.2457 洪水Flood
德克萨斯TEXAS 123400.5101123400.5101 专业观测员Trained Spotter 13997.712413997.7124 CLAUDECLAUDE 421.44421.44 冰雹Hail
德克萨斯TEXAS 123400.5101123400.5101 执法机构Law Enforcement 37228.596637228.5966 PERRYTONPERRYTON 289.3178289.3178 冰雹Hail
德克萨斯TEXAS 123400.5101123400.5101 执法机构Law Enforcement 37228.596637228.5966 PERRYTONPERRYTON 289.3178289.3178 洪水Flood
德克萨斯TEXAS 123400.5101123400.5101 执法机构Law Enforcement 37228.596637228.5966 PERRYTONPERRYTON 289.3178289.3178 山洪Flash Flood

为此级别(每个组)中的每个值指定索引排序顺序,以按最后一个嵌套级别(在本例中按 EndLocation)对结果排序:Give an index sort order for each value in this level (per group) to sort the result by the last nested level (in this example by EndLocation):

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)
| order 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
状态State SourceSource EndLocationsEndLocations endLocationSumsendLocationSums 索引indices
德克萨斯TEXAS 专业观测员Trained Spotter CLAUDECLAUDE 421.44421.44 00
德克萨斯TEXAS 专业观测员Trained Spotter AMARILLOAMARILLO 316.8892316.8892 11
德克萨斯TEXAS 专业观测员Trained Spotter DALHARTDALHART 252.6186252.6186 22
德克萨斯TEXAS 专业观测员Trained Spotter PERRYTONPERRYTON 216.7826216.7826 33
德克萨斯TEXAS 执法机构Law Enforcement PERRYTONPERRYTON 289.3178289.3178 00
德克萨斯TEXAS 执法机构Law Enforcement LEAKEYLEAKEY 267.9825267.9825 11
德克萨斯TEXAS 执法机构Law Enforcement BRACKETTVILLEBRACKETTVILLE 264.3483264.3483 22
德克萨斯TEXAS 执法机构Law Enforcement GILMERGILMER 261.9068261.9068 33
KANSASKANSAS 专业观测员Trained Spotter SHARON SPGSSHARON SPGS 388.7404388.7404 00
KANSASKANSAS 专业观测员Trained Spotter ATWOODATWOOD 358.6136358.6136 11
KANSASKANSAS 专业观测员Trained Spotter LENORALENORA 317.0718317.0718 22
KANSASKANSAS 专业观测员Trained Spotter SCOTT CITYSCOTT CITY 307.84307.84 33
KANSASKANSAS 公用Public BUCKLINBUCKLIN 488.2457488.2457 00
KANSASKANSAS 公用Public ASHLANDASHLAND 446.4218446.4218 11
KANSASKANSAS 公用Public PROTECTIONPROTECTION 446.11446.11 22
KANSASKANSAS 公用Public MEADE STATE PARKMEADE STATE PARK 371.1371.1 33