Kusto中带条件使用percentile()计算用户状态分位数问题咨询

完整Kusto查询实现 #

// 构造示例测试数据，实际使用时替换为你自己的表名即可
let SampleData = datatable(OnStatus:bool, OffStatus:bool, WebId:int, UserId:string)
[
    true, false, 1, "A123",
    true, false, 2, "A123",
    true, false, 3, "A123",
    false, true, 4, "A123",
    false, true, 1, "B123",
    false, true, 2, "B123",
    false, true, 3, "B123",
    false, true, 4, "B123"
];

步骤1：按用户维度输出分位数状态值

SampleData
// 按用户聚合计算状态为true的记录数和总记录数
| summarize 
    TotalRecords = count(),
    OnTrueCount = countif(OnStatus == true),
    OffTrueCount = countif(OffStatus == true)
by UserId
// 按占比判断分位数状态，完全匹配你给出的示例输出逻辑
| extend 
    P50_StatusOn = (OnTrueCount * 1.0 / TotalRecords) >= 0.5,
    P95_StatusOn = (OnTrueCount * 1.0 / TotalRecords) >= 0.95,
    P50_StatusOff = (OffTrueCount * 1.0 / TotalRecords) >= 0.5,
    P95_StatusOff = (OffTrueCount * 1.0 / TotalRecords) >= 0.95
// 输出指定字段
| project UserId, P50_StatusOn, P95_StatusOn, P50_StatusOff, P95_StatusOff

步骤2：统计各分位数状态的总计数

SampleData
| summarize 
    TotalRecords = count(),
    OnTrueCount = countif(OnStatus == true),
    OffTrueCount = countif(OffStatus == true)
by UserId
| extend 
    P50_StatusOn = (OnTrueCount * 1.0 / TotalRecords) >= 0.5,
    P95_StatusOn = (OnTrueCount * 1.0 / TotalRecords) >= 0.95,
    P50_StatusOff = (OffTrueCount * 1.0 / TotalRecords) >= 0.5,
    P95_StatusOff = (OffTrueCount * 1.0 / TotalRecords) >= 0.95
// 聚合统计各状态为true的总数量
| summarize
    P50_StatusOnCnt = countif(P50_StatusOn == true),
    P95_StatusOnCnt = countif(P95_StatusOn == true),
    P50_StatusOffCnt = countif(P50_StatusOff == true),
    P95_StatusOffCnt = countif(P95_StatusOff == true)

调用原生percentile()函数的写法

因Kusto原生percentile()采用最近秩算法，计算4条记录的95分位时会取排序后的第4个值，和示例输出的P95逻辑略有差异，若需强制使用原生函数可参考以下写法：

// 步骤1 原生percentile实现
SampleData
| extend OnStatusInt = iif(OnStatus, 1, 0), OffStatusInt = iif(OffStatus, 1, 0)
| summarize 
    P50_On = percentile(OnStatusInt, 50),
    P95_On = percentile(OnStatusInt, 95),
    P50_Off = percentile(OffStatusInt, 50),
    P95_Off = percentile(OffStatusInt, 95)
by UserId
| extend 
    P50_StatusOn = P50_On == 1,
    P95_StatusOn = P95_On == 1,
    P50_StatusOff = P50_Off == 1,
    P95_StatusOff = P95_Off == 1
| project UserId, P50_StatusOn, P95_StatusOn, P50_StatusOff, P95_StatusOff

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