英文:
Adding a last row using KQL to show grand total based on column values
问题
我有以下格式的数据
let T = datatable(OperationName:string, Result:string)
[
"Method1" , "success",
"Method1" , "failure",
"Method1" , "success",
"Method1" , "success",
"Method1" , "success",
"Method1" , "failure",
"Method2" , "success",
"Method2" , "failure",
"Method2" , "failure",
];
T
| summarize success = countif(Result == "success")
,failure = countif(Result == "failure")
,total = count()
by OperationName
它显示了每种方法的成功和失败次数，以及总数。
是否可以计算一个总和值，即总列值的和，并在最后一行的末尾显示，如下所示？
英文:
I have data in following format
let T = datatable(OperationName:string, Result:string)
[
"Method1" , "success",
"Method1" , "failure",
"Method1" , "success",
"Method1" , "success",
"Method1" , "success",
"Method1" , "failure",
"Method2" , "success",
"Method2" , "failure",
"Method2" , "failure",
];
T
| summarize success = countif(Result == "success")
,failure = countif(Result == "failure")
,total = count()
by OperationName
Which displays the number of successes and failures per method along with the total.
Is it possible to calculate a grand total value, which is the sum of the total column values, and display it at the end of the last row, as shown below?
答案1
得分: 1
你可以尝试使用 union 与一个已聚合的表格进行合并。
例如：
let T = datatable(OperationName: string, Result: string)
[
"Method1", "success",
"Method1", "failure",
"Method1", "success",
"Method1", "success",
"Method1", "success",
"Method1", "failure",
"Method2", "success",
"Method2", "failure",
"Method2", "failure",
];
T
| summarize
success = countif(Result == "success"),
failure = countif(Result == "failure"),
total = count()
by OperationName
| as hint.materialized=true T
| union (T | summarize success = sum(success), failure = sum(failure), total = sum(total) by OperationName = "Grand Total")
| OperationName | success | failure | total |
|---|---|---|---|
| Method2 | 1 | 2 | 3 |
| Method1 | 4 | 2 | 6 |
| Grand Total | 5 | 4 | 9 |
或者，如果您对不太直观和不太结构化的输出（出于您自己的原因）感兴趣：
let T = datatable(OperationName: string, Result: string)
[
"Method1", "success",
"Method1", "failure",
"Method1", "success",
"Method1", "success",
"Method1", "success",
"Method1", "failure",
"Method2", "success",
"Method2", "failure",
"Method2", "failure",
];
T
| summarize
success = countif(Result == "success"),
failure = countif(Result == "failure"),
total = count()
by OperationName
| as hint.materialized=true T
| union (T | summarize total = sum(total) by OperationName = "")
| extend total = case(isempty(OperationName), strcat("Grand Total = ", total), tostring(total))
| OperationName | success | failure | total |
|---|---|---|---|
| Method2 | 1 | 2 | 3 |
| Method1 | 4 | 2 | 6 |
| Grand Total = 9 |
英文:
you could try unioning with a row that is an aggregation of the aggregated table.
for example:
let T = datatable(OperationName: string, Result: string)
[
"Method1", "success",
"Method1", "failure",
"Method1", "success",
"Method1", "success",
"Method1", "success",
"Method1", "failure",
"Method2", "success",
"Method2", "failure",
"Method2", "failure",
];
T
| summarize
success = countif(Result == "success"),
failure = countif(Result == "failure"),
total = count()
by OperationName
| as hint.materialized=true T
| union (T | summarize success = sum(success), failure = sum(failure), total = sum(total) by OperationName = "Grand Total")
| OperationName | success | failure | total |
|---|---|---|---|
| Method2 | 1 | 2 | 3 |
| Method1 | 4 | 2 | 6 |
| Grand Total | 5 | 4 | 9 |
or, if you're interested in a less-intuitive and less-structured output (for reasons of your own):
let T = datatable(OperationName: string, Result: string)
[
"Method1", "success",
"Method1", "failure",
"Method1", "success",
"Method1", "success",
"Method1", "success",
"Method1", "failure",
"Method2", "success",
"Method2", "failure",
"Method2", "failure",
];
T
| summarize
success = countif(Result == "success"),
failure = countif(Result == "failure"),
total = count()
by OperationName
| as hint.materialized=true T
| union (T | summarize total = sum(total) by OperationName = "")
| extend total = case(isempty(OperationName), strcat("Grand Total = ", total), tostring(total))
| OperationName | success | failure | total |
|---|---|---|---|
| Method2 | 1 | 2 | 3 |
| Method1 | 4 | 2 | 6 |
| Grand Total = 9 |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论