使用KQL添加最后一行，以显示基于列值的总计。

huangapple
go评论122阅读模式
英文:

Adding a last row using KQL to show grand total based on column values

问题

  1. 我有以下格式的数据
  3.     let T = datatable(OperationName:string, Result:string)
  4.     [
  5.        "Method1" , "success",
  6.        "Method1" , "failure",
  7.        "Method1" , "success",
  8.        "Method1" , "success",
  9.        "Method1" , "success",
  10.        "Method1" , "failure",
  11.        "Method2" , "success",
  12.        "Method2" , "failure",
  13.        "Method2" , "failure",
  14.     ];
  15.     T 
  16.     | summarize success = countif(Result == "success")
  17.                ,failure = countif(Result == "failure")
  18.                ,total   = count()
  19.                 by OperationName
  21. 它显示了每种方法的成功和失败次数，以及总数。
  23. 是否可以计算一个总和值，即总列值的和，并在最后一行的末尾显示，如下所示？
英文:

I have data in following format

  1. let T = datatable(OperationName:string, Result:string)
  2. [
  3.    "Method1" , "success",
  4.    "Method1" , "failure",
  5.    "Method1" , "success",
  6.    "Method1" , "success",
  7.    "Method1" , "success",
  8.    "Method1" , "failure",
  9.    "Method2" , "success",
  10.    "Method2" , "failure",
  11.    "Method2" , "failure",
  12. ];
  13. T 
  14. | summarize success = countif(Result == "success")
  15.            ,failure = countif(Result == "failure")
  16.            ,total   = count()
  17.             by OperationName

Which displays the number of successes and failures per method along with the total.

使用KQL添加最后一行，以显示基于列值的总计。

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?

使用KQL添加最后一行，以显示基于列值的总计。

答案1

得分: 1

你可以尝试使用 union 与一个已聚合的表格进行合并。

例如：

  1. let T = datatable(OperationName: string, Result: string)
  2. [
  3.     "Method1", "success",
  4.     "Method1", "failure",
  5.     "Method1", "success",
  6.     "Method1", "success",
  7.     "Method1", "success",
  8.     "Method1", "failure",
  9.     "Method2", "success",
  10.     "Method2", "failure",
  11.     "Method2", "failure",
  12. ];
  13. T 
  14. | summarize
  15.     success = countif(Result == "success"),
  16.     failure = countif(Result == "failure"),
  17.     total   = count()
  18.  by OperationName
  19. | as hint.materialized=true T
  20. | 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

或者，如果您对不太直观和不太结构化的输出（出于您自己的原因）感兴趣：

  1. let T = datatable(OperationName: string, Result: string)
  2. [
  3.     "Method1", "success",
  4.     "Method1", "failure",
  5.     "Method1", "success",
  6.     "Method1", "success",
  7.     "Method1", "success",
  8.     "Method1", "failure",
  9.     "Method2", "success",
  10.     "Method2", "failure",
  11.     "Method2", "failure",
  12. ];
  13. T 
  14. | summarize
  15.     success = countif(Result == "success"),
  16.     failure = countif(Result == "failure"),
  17.     total   = count()
  18.  by OperationName
  19. | as hint.materialized=true T
  20. | union (T | summarize total = sum(total) by OperationName = "")
  21. | 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:

  1. let T = datatable(OperationName: string, Result: string)
  2. [
  3.     "Method1", "success",
  4.     "Method1", "failure",
  5.     "Method1", "success",
  6.     "Method1", "success",
  7.     "Method1", "success",
  8.     "Method1", "failure",
  9.     "Method2", "success",
  10.     "Method2", "failure",
  11.     "Method2", "failure",
  12. ];
  13. T 
  14. | summarize
  15.     success = countif(Result == "success"),
  16.     failure = countif(Result == "failure"),
  17.     total   = count()
  18.  by OperationName
  19. | as hint.materialized=true T
  20. | 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):

  1. let T = datatable(OperationName: string, Result: string)
  2. [
  3.     "Method1", "success",
  4.     "Method1", "failure",
  5.     "Method1", "success",
  6.     "Method1", "success",
  7.     "Method1", "success",
  8.     "Method1", "failure",
  9.     "Method2", "success",
  10.     "Method2", "failure",
  11.     "Method2", "failure",
  12. ];
  13. T 
  14. | summarize
  15.     success = countif(Result == "success"),
  16.     failure = countif(Result == "failure"),
  17.     total   = count()
  18.  by OperationName
  19. | as hint.materialized=true T
  20. | union (T | summarize total = sum(total) by OperationName = "")
  21. | 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

huangapple
  • 本文由 发表于 2023年3月7日 12:34:08
  • 转载请务必保留本文链接：https://go.coder-hub.com/75658077.html
  • azure
  • azure-data-explorer
  • kql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定