如何在KQL的summarize语句中获取operation_Id众数的出现次数？

问题描述 #

需要在现有KQL查询中新增一列，实现两个功能：

1. 找出make_set(operation_Id)生成的列表中出现次数最多的数值（众数）
2. 输出该数值的出现次数

现有查询：

dependencies
| join kind=inner (
    requests
    | project operation_Id, customDimensions, operation_ParentId, name, id
    | extend req_custom_dim = customDimensions, req_endpoint = name
) on $left.operation_Id == $right.operation_Id
| extend customDimensions = parse_json(req_custom_dim) 
| extend 
    siteName = tostring(customDimensions.SiteName), 
    clientName = tostring(customDimensions.ClientName),
    endpoint = req_endpoint
| where (strlen(trim(" ", clientName)) > 0) and (strlen(trim(" ", siteName)) > 0)
| where type == "SQL"
| project endpoint, clientName, siteName, duration, operation_Id, customDimensions, operation_ParentId, id, target, operation_Name, itemType, client_City, itemCount, type, name, data
| summarize Count = count(), operationIdSet = make_set(operation_Id), avg = count()/array_length(make_set(operation_Id)) by endpoint, clientName, siteName

解决方案 #

修改后的查询通过两步聚合实现需求：先统计每个分组内各operation_Id的出现次数，再聚合找出最大次数（即众数的出现次数），同时可获取对应的众数：

dependencies
| join kind=inner (
    requests
    | project operation_Id, customDimensions, operation_ParentId, name, id
    | extend req_custom_dim = customDimensions, req_endpoint = name
) on $left.operation_Id == $right.operation_Id
| extend customDimensions = parse_json(req_custom_dim) 
| extend 
    siteName = tostring(customDimensions.SiteName), 
    clientName = tostring(customDimensions.ClientName),
    endpoint = req_endpoint
| where (strlen(trim(" ", clientName)) > 0) and (strlen(trim(" ", siteName)) > 0)
| where type == "SQL"
| project endpoint, clientName, siteName, operation_Id
// 第一步：统计每个(endpoint, clientName, siteName)组内，各operation_Id的出现次数
| summarize op_count = count() by endpoint, clientName, siteName, operation_Id
// 第二步：按主分组聚合，计算总次数、去重集合、平均值，同时获取众数及其出现次数
| summarize 
    TotalCount = sum(op_count),
    operationIdSet = make_set(operation_Id),
    avg = sum(op_count)/array_length(make_set(operation_Id)),
    // 众数的出现次数
    ModeCount = max(op_count),
    // 出现次数最多的operation_Id（若有多个众数，取其中一个）
    ModeOperationId = arg_max(op_count, operation_Id).operation_Id
by endpoint, clientName, siteName

关键说明 #

• 新增的第一步聚合summarize op_count = count() by endpoint, clientName, siteName, operation_Id：为每个operation_Id在所属分组内统计出现次数。
• ModeCount = max(op_count)：直接取分组内最大的出现次数，即众数的出现次数。
• ModeOperationId = arg_max(op_count, operation_Id).operation_Id：获取出现次数最多的operation_Id，如果存在多个operation_Id出现次数相同且均为最大值，此方法会返回其中一个；若需要获取所有众数，可改用make_list(operation_Id)配合筛选逻辑，例如：

  ModeOperationIds = make_list_if(operation_Id, op_count == max(op_count))
  

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