top-nested 运算符
top-nested 运算符将执行分层聚合和值选择。
假设你有一个表，其中包含区域、销售人员和销售金额等销售信息。 top-nested 运算符可以帮助你回答复杂的问题，例如“按销售额排名前五的区域是什么，以及每个区域中排名前三的销售人员是谁？”
源数据将基于第一个 top-nested 子句中设置的条件（例如区域）进行分区。 接下来，该运算符将使用聚合（如将销售额相加）来挑选每个分区中排名靠前的记录。 每个后续 top-nested 子句都会优化由上一个子句创建的分区，从而创建更精确的组的层次结构。
最终每个子句都可得到一个包含两列的表。 一列保存分区值（如区域），另一列保存聚合计算的结果，如总销售额。
语法
T|top-nested [ N ] ofExpr [withothers=ConstExpr] byAggregation [asc | desc] [,
top-nested ... ]
详细了解语法约定。
参数
| 客户 | 类型 | 必需 | 说明 |
|---|---|---|---|
| T | string |
✔ | 输入表格表达式。 |
| N | int |
要为此层次结构级别返回的排在前面的值的数目。 如果省略，将返回所有非重复值。 | |
| Expr | string |
✔ | 针对输入记录的表达式，指示要为此层次结构级别返回的值。 通常，它会引用来自 T 的列，或涉及一列上类似 bin() 的计算。 （可选）将输出列名称设置为 Name=Expr。 |
| ConstExpr | string |
如果指定，则为每个层次结构级别添加一条记录，且该记录的值是所有未能排在前面的记录的聚合值。 | |
| 聚合 | string |
应用于具有相同 Expr 值的记录的聚合函数。 其结果将确定排名靠前的记录。 请参阅支持的聚合函数。 （可选）将输出列名称设置为 Name=Aggregation。 |
支持的聚合函数
支持以下聚合函数：
注意
还支持聚合的任何代数组合。
返回
每个子句都可得到一个包含两列的表。 一列包含使用 Expr 计算的唯一值，另一列显示通过 Aggregation 计算获得的结果。
添加来自其他列的数据
输出表中仅显示指定为 top-nested子句 Expr 的列。
若要包含特定级别的列的所有值：
- 请勿指定 N 的值。
- 使用列名称作为 Expr 的值。
- 使用
Ignore=max(1)作为 Aggregation 的值。 - 使用 project-away 移除不必要的
Ignore列。
有关示例，请参阅获取每个州的最新事件以及其他列中的其他数据。
性能注意事项
记录数可能会随着 top-nested 子句数而呈指数增长。如果未指定 N 限制，则记录增长甚至更快。 此运算符可能会消耗大量的资源。
如果聚合的分布相当不均匀，请通过指定 N 来限制返回的非重复值的数量。然后，使用 withothers=ConstExpr 规范来获得所有其他情况的“权重”指示。
示例
top-nested 运算符入门
以下查询将按 State 列对 StormEvents 表进行分区，并计算每个州的总纬度。 查询将选择纬度总和最高的前两个州。 在这前两个州中，查询会按 Source 对数据进行分组，并选择纬度总和最高的前三个源。 对于前两个州中的前三个源，查询将按 EndLocation 对数据进行分组，并选择纬度总和最高的 EndLocation。
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.
输出
| 状态 | aggregated_State | Source | aggregated_Source | EndLocation | aggregated_EndLocation |
|---|---|---|---|---|---|
| KANSAS | 87771.2355000001 | 执法机构 | 18744.823 | FT SCOTT | 264.858 |
| KANSAS | 87771.2355000001 | 公用 | 22855.6206 | BUCKLIN | 488.2457 |
| KANSAS | 87771.2355000001 | 专业观测员 | 21279.7083 | SHARON SPGS | 388.7404 |
| 德克萨斯 | 123400.5101 | 公用 | 13650.9079 | AMARILLO | 246.2598 |
| 德克萨斯 | 123400.5101 | 执法机构 | 37228.5966 | PERRYTON | 289.3178 |
| 德克萨斯 | 123400.5101 | 专业观测员 | 13997.7124 | CLAUDE | 421.44 |
使用来自另一列的数据增强 top-nested 结果
以下查询在上一个示例的基础上引入了一个额外的 top-nested 子句。 在此新子句中，缺少数值规范会导致跨分区提取 EventType 的所有非重复值。 max(1) 聚合函数只是一个占位符，旨在使其结果无关紧要，因此 project-away 运算符会移除 Ignore 列。 结果将显示与先前聚合的数据关联的所有事件类型。
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
输出
| 状态 | aggregated_State | Source | aggregated_Source | EndLocation | aggregated_EndLocation | EventType |
|---|---|---|---|---|---|---|
| 德克萨斯 | 123400.51009999994 | Public | 13650.907900000002 | AMARILLO | 246.25979999999998 | 冰雹 |
| 德克萨斯 | 123400.51009999994 | Public | 13650.907900000002 | AMARILLO | 246.25979999999998 | 雷雨大风 |
| KANSAS | 87771.235500000068 | 公用 | 22855.6206 | BUCKLIN | 488.2457 | 洪水 |
| KANSAS | 87771.235500000068 | 公用 | 22855.6206 | BUCKLIN | 488.2457 | 雷雨大风 |
| KANSAS | 87771.235500000068 | 公用 | 22855.6206 | BUCKLIN | 488.2457 | 冰雹 |
| 德克萨斯 | 123400.51009999994 | 专业观测员 | 13997.712400000009 | CLAUDE | 421.44 | 冰雹 |
| KANSAS | 87771.235500000068 | 执法机构 | 18744.823000000004 | FT SCOTT | 264.858 | 山洪 |
| KANSAS | 87771.235500000068 | 执法机构 | 18744.823000000004 | FT SCOTT | 264.858 | 雷雨大风 |
| KANSAS | 87771.235500000068 | 执法机构 | 18744.823000000004 | FT SCOTT | 264.858 | 洪水 |
| 德克萨斯 | 123400.51009999994 | 执法机构 | 37228.596599999961 | PERRYTON | 289.3178 | 冰雹 |
| ... | ... | ... | ... | ... | ... |
使用 withothers 浏览排除的数据
在包含在 top-nested 子句中时，withothers 规范会引入一条额外的记录，用于聚合从排名靠前的结果中排除的数据。 在以下查询中，将在 State 和 aggregated_State 列中创建一条额外的记录，表示除堪萨斯州和得克萨斯州以外的所有州的纬度总和。 此外，EndLocation 和 aggregated_EndLocation 列将有 9 条额外的记录。 这些记录显示了每个州和源内未能排在前面的最终位置的纬度总和。
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)
输出
| 状态 | aggregated_State | Source | aggregated_Source | EndLocation | aggregated_EndLocation |
|---|---|---|---|---|---|
| KANSAS | 87771.2355000001 | 执法机构 | 18744.823 | FT SCOTT | 264.858 |
| KANSAS | 87771.2355000001 | 公用 | 22855.6206 | BUCKLIN | 488.2457 |
| KANSAS | 87771.2355000001 | 专业观测员 | 21279.7083 | SHARON SPGS | 388.7404 |
| 德克萨斯 | 123400.5101 | 公用 | 13650.9079 | AMARILLO | 246.2598 |
| 德克萨斯 | 123400.5101 | 执法机构 | 37228.5966 | PERRYTON | 289.3178 |
| 德克萨斯 | 123400.5101 | 专业观测员 | 13997.7124 | CLAUDE | 421.44 |
| KANSAS | 87771.2355000001 | 执法机构 | 18744.823 | 所有其他结束位置 | 18479.965 |
| KANSAS | 87771.2355000001 | 公用 | 22855.6206 | 所有其他结束位置 | 22367.3749 |
| KANSAS | 87771.2355000001 | 专业观测员 | 21279.7083 | 所有其他结束位置 | 20890.9679 |
| 德克萨斯 | 123400.5101 | 公用 | 13650.9079 | 所有其他结束位置 | 13404.6481 |
| 德克萨斯 | 123400.5101 | 执法机构 | 37228.5966 | 所有其他结束位置 | 36939.2788 |
| 德克萨斯 | 123400.5101 | 专业观测员 | 13997.7124 | 所有其他结束位置 | 13576.2724 |
| KANSAS | 87771.2355000001 | 所有其他结束位置 | 24891.0836 | ||
| 德克萨斯 | 123400.5101 | 所有其他结束位置 | 58523.2932000001 | ||
| 所有其他州 | 1149279.5923 | 所有其他结束位置 | 1149279.5923 |
针对上例中使用的第一个级别，以下查询将显示相同的结果。
StormEvents
| where State !in ('TEXAS', 'KANSAS')
| summarize sum(BeginLat)
输出
| sum_BeginLat |
|---|
| 1149279.5923 |
对分层结果进行排序
为了实现全面的排序顺序，以下查询将按组对当前层次结构级别中的每个值使用基于索引的排序。 此排序旨在根据最终嵌套级别（在本例中为 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)
| 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
输出
| 状态 | Source | EndLocations | endLocationSums | 索引 |
|---|---|---|---|---|
| 德克萨斯 | 专业观测员 | CLAUDE | 421.44 | 0 |
| 德克萨斯 | 专业观测员 | AMARILLO | 316.8892 | 1 |
| 德克萨斯 | 专业观测员 | DALHART | 252.6186 | 2 |
| 德克萨斯 | 专业观测员 | PERRYTON | 216.7826 | 3 |
| 德克萨斯 | 执法机构 | PERRYTON | 289.3178 | 0 |
| 德克萨斯 | 执法机构 | LEAKEY | 267.9825 | 1 |
| 德克萨斯 | 执法机构 | BRACKETTVILLE | 264.3483 | 2 |
| 德克萨斯 | 执法机构 | GILMER | 261.9068 | 3 |
| KANSAS | 专业观测员 | SHARON SPGS | 388.7404 | 0 |
| KANSAS | 专业观测员 | ATWOOD | 358.6136 | 1 |
| KANSAS | 专业观测员 | LENORA | 317.0718 | 2 |
| KANSAS | 专业观测员 | SCOTT CITY | 307.84 | 3 |
| KANSAS | 公用 | BUCKLIN | 488.2457 | 0 |
| KANSAS | 公用 | ASHLAND | 446.4218 | 1 |
| KANSAS | 公用 | PROTECTION | 446.11 | 2 |
| KANSAS | 公用 | MEADE STATE PARK | 371.1 | 3 |
获取每个州的最新事件以及其他列中的其他数据
以下查询演示如何检索每个美国州的两个最新事件以及相关的事件详细信息。 请注意，在某些列中使用了由 Ignore* 标识的 max(1)，这有助于在不施加任何选择逻辑的情况下通过查询传播数据。
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.
获取每个身份的最新记录以及其他列中的其他数据
以下查询展示了如何提取每个身份的最新记录，并以上一示例中介绍的概念为基础。 第一个 top-nested 子句将按 id 的非重复值对数据进行分区。 后续子句将根据每个 timestamp 的 id 来确定两个最新记录。 此示例还使用 top-nested 运算符以及未指定的计数和任意 max(1) 聚合追加了其他信息。 最后，使用 project-away 运算符删除了不必要的聚合列。
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.
输出
| 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 |