时间戳

首页 > 解决方案 > 将多行合并为具有百分比贡献的单行

azure - 将多行合并为具有百分比贡献的单行

问题描述

我有一个表，其中包含节点 ID、操作名称和给定类型的失败次数。我想通过添加一个提供相对操作失败百分比的新列来折叠具有相同 ID 的节点。具体来说，这是表格的样子

let nodeFailures = datatable(nodeId:string, OpName:string, failureCount:long)
[
    "A", "DemoOp1", 2000, 
    "A", "DemoOp2", 4000, 
    "A", "DemoOp3", 4000, 
    "B", "DemoOp1", 4500, 
    "B", "DemoOp4", 4500 
];

我想把它折叠成 2 行 - 像

nodeFailuresSummarized = datatable(nodeId:string, failureCount:long, analysis: string)
[
    "A", 10000, "DemoOp1(20%), DemoOp2(40%), DemoOp3(40%)",
    "B", 9000, "DemoOp1(50%), DemoOp4(50%)"
];

我一直在玩summary 和 percentile*() 聚合的游戏，但运气不佳.. 在这里将不胜感激.. 非常感谢！

标签： azureazure-data-explorerkql

解决方案

let nodeFailures = datatable(nodeId:string, OpName:string, failureCount:long)
[
    "A", "DemoOp1", 2000, 
    "A", "DemoOp2", 4000, 
    "A", "DemoOp3", 4000, 
    "B", "DemoOp1", 4500, 
    "B", "DemoOp4", 4500 
];
let totalFailures = toscalar(nodeFailures | summarize sum(failureCount));
nodeFailures
| extend Percent = round(failureCount * 100.0 / totalFailures, 2)
| summarize sum(failureCount), List = make_list(strcat(OpName, " (", Percent, "%)")) by nodeId
| extend List = strcat_array(List, ", ")

输出将是：

| nodeId  | sum_failureCount | List|
| A       | 10000            | DemoOp1 (10.53%), DemoOp2 (21.05%), DemoOp3 (21.05%)|
| B       | 9000             | DemoOp1 (23.68%), DemoOp4 (23.68%)             
        |

推荐阅读