如何在KQL中实现按时间桶选取Top N聚合行？

需求：按时间桶（如按天）与指定列（如大洲）分组，计算聚合值后，仅保留每个时间桶内排名前N的聚合行。以下示例已实现按日分组计算各大洲的val总和，需进一步筛选每日排名前2的记录。

解决方案代码 #

let T = datatable(d:datetime , continent:string, country:string, val:int)
[
    datetime(2022-10-05T01:40:00.00), "Asia", "China", 10,
    datetime(2022-10-05T02:50:00.00), "Asia", "India", 25,
    datetime(2022-10-05T03:55:00.00), "Asia", "Japan", 15,
    datetime(2022-10-05T01:40:00.00), "Europe", "Czech Republic", 1,
    datetime(2022-10-05T02:50:00.00), "Europe", "France", 8,
    datetime(2022-10-05T07:55:00.00), "Europe", "Germany", 9,
    datetime(2022-10-05T04:55:00.00), "North America", "USA", 25,
    datetime(2022-10-05T05:55:00.00), "North America", "Haiti", 5,
    datetime(2022-10-05T09:55:00.00), "North America", "Jamaica", 3,
    datetime(2022-10-06T01:40:00.00), "Asia", "China", 7,
    datetime(2022-10-06T02:50:00.00), "Asia", "India", 8,
    datetime(2022-10-06T03:55:00.00), "Asia", "Japan", 15,
    datetime(2022-10-06T01:40:00.00), "Europe", "Czech Republic", 29,
    datetime(2022-10-06T02:50:00.00), "Europe", "France", 14,
    datetime(2022-10-06T07:55:00.00), "Europe", "Germany", 13,
    datetime(2022-10-06T04:55:00.00), "North America", "USA", 12,
    datetime(2022-10-06T05:55:00.00), "North America", "Haiti", 7,
    datetime(2022-10-06T09:55:00.00), "North America", "Jamaica", 4,
];
T
| summarize sumval = sum(val) by bin(d,1d), continent
| partition by bin(d,1d) (
    sort by sumval desc
    | take 2
)

两种常用实现思路 #

1. partition by + take 方式

   • 用partition by bin(d,1d)将数据按时间桶拆分独立分组
   • 每个分组内按聚合值降序排序，再通过take 2直接取前2条，逻辑直观简洁

2. rank() 函数方式

   • 聚合后通过extend生成排名字段：| extend rank = rank(desc, sumval) over (partition by bin(d,1d))
   • 筛选rank <= 2的行即可得到目标结果，适合需要保留排名字段的场景，示例代码如下：

T
| summarize sumval = sum(val) by bin(d,1d), continent
| extend rank = rank(desc, sumval) over (partition by bin(d,1d))
| where rank <= 2
| sort by bin(d,1d) asc, rank asc

内容的提问来源于stack exchange，提问作者Zikato