Azure Data Explorer(Kusto)无pivot插件实现行转列咨询

完整可运行查询 #

let startDate = ago(7d);
let endDate = now();
let all_logs = materialize(MyTable | where TIMESTAMP > startDate and TIMESTAMP < endDate | where EventName == "SessionResult");
// 存储统计得到的宽表临时结果
let session_stats = all_logs
| summarize 
Total_Sessions = count(),
Successful_Sessions = countif(Successful == "True"),
Failed_Sessions = countif(Successful == "False")
| extend Success_Rate = round(100.0*Successful_Sessions/Total_Sessions, 2);
// 转换为长表结构
session_stats
| extend metrics = dynamic([
    {"Label": "总会话", "Count": Total_Sessions, "Rate": 100.0},
    {"Label": "成功会话", "Count": Successful_Sessions, "Rate": Success_Rate},
    {"Label": "失败会话", "Count": Failed_Sessions, "Rate": round(100.0 - Success_Rate, 2)}
])
| mv-expand metrics
| project 
    Label = tostring(metrics.Label),
    Count = tolong(metrics.Count),
    Rate = todouble(metrics.Rate)

实现逻辑说明 #

• 先将原有宽表统计结果存为临时变量session_stats，避免重复扫描源表，完全复用原有计算逻辑
• 用dynamic()构造包含3个指标项的数组，每个数组元素对应长表的一行数据，直接按需求填充三个字段的取值
• 通过mv-expand运算符展开动态数组，将数组内的每个对象转换为独立的行记录
• 最后用project提取目标字段，同时做类型转换保证输出数据类型统一

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