top-nested 运算符top-nested operator

• 09/30/2020
• • o
  • v

生成分层聚合和前位值选择，其中每个级别都是前一级别的优化。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 :

• N ：long 类型的文本，指示要为此层次结构级别返回多少前位值。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:

  • sum()、sum(),
  • count()、count(),
  • max()、max(),
  • min()、min(),
  • dcount()、dcount(),
  • avg()、avg(),
  • percentile() 和percentile(), and
  • percentilew()。percentilew(). 还支持聚合的任何代数组合。Any algebraic combination of the aggregations is also supported.

• asc 或 desc（默认）可能会控制实际从聚合值范围“底部”还是“顶部”进行选择。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)

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)

以下查询针对上例中使用的第一个级别显示了相同结果。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)

请求在顶部嵌套结果中设置另一列 (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

为此级别（每个组）中的每个值指定索引排序顺序，以按最后一个嵌套级别（在本例中按 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