查找作为汇总运算结果的列中的最大值和最小值

发布于 2025-01-12 18:42:05 字数 1698 浏览 0 评论 0原文

我试图找到一种方法来计算列的最大值和最小值。

使用下面的查询，我首先使用汇总操作计算（每分钟请求数）RPM，然后选择 RPM 列的最大值和最小值。

从技术上讲，我可以在对列（升序或降序）进行排序后使用 take 操作来获取最小值或最大值，但它似乎在计算上效率不高。此外，它仅提供最大值或最小值，而不是同时提供这两个值。

最终输出应如下表所示：

RPM                   Timestamp
----                       
Max: value (e.g) 13 | 2022-03-02T14 
Min: value (e.g) 2  | 2022-03-06T11

let T = datatable(Timestamp:datetime, ResultType:string, ResultSignature:string, CorrelationId:string) [
"2022-03-02T14:35:05.6846874Z", "Throttled", "200", "a8de8a0b-2b95-4e16-a90f-d96c1f404850",
"2022-03-02T14:35:06.9535229Z", "Throttled", "200", "7e00ac15-6e82-42a5-8171-3145ae27728f",
"2022-03-02T14:34:21.1880149Z", "Non-Throttled", "200", "8fa9f7ee-6a91-4b8c-b170-9649befa698c",
"2022-03-02T14:34:36.9887590Z", "Non-Throttled", "200", "de7d82be-49b8-44dc-856c-16f76c7a4ae5",
"2022-03-02T14:34:39.3999879Z", "Non-Throttled", "200", "99b67d55-3ee4-4aee-9415-03919b2f23a4",
"2022-03-02T14:34:40.7854748Z", "Non-Throttled", "400", "dec5cd49-9d64-469a-83aa-db759c2e2fb1",
"2022-03-02T14:34:44.2007485Z", "Non-Throttled", "200", "5b412e71-6e48-49e2-9298-fd13d31619d1",
"2022-03-02T14:34:55.6858503Z", "Throttled", "200", "482592f9-722c-4f5d-8e48-967fa655d704",
"2022-03-02T14:25:17.0269766Z", "Throttled", "200", "1732c865-2474-4f76-b0cd-64af5981af7c",
"2022-03-02T14:25:18.9668944Z", "Throttled", "200", "234ec84c-3a0a-4329-a492-f8d590267ec6",
"2022-03-02T14:25:21.8262878Z", "Throttled", "200", "be8bd024-8f5c-4a01-9703-2945ef3bc8ba",
    ];
T 
| project Timestamp, ResultType, ResultSignature , CorrelationId
| summarize   RPM = count() by bin(Timestamp,1m)
| order by RPM desc
| take 1

原文

I am trying to find out a way to calculate the maximum and minimum values of a column.

Using the query below, I first calculate the (requests per minute) RPM by using the summary operation and then want to pick max and min values for the RPM column.

I can technically use the take operation after ordering the column (asc or desc) to get either min or max value but it doesn't seem to be computationally efficient. Also, it only provides either max or min value and not both values at the same time.

The final output should be like following table:

RPM                   Timestamp
----                       
Max: value (e.g) 13 | 2022-03-02T14 
Min: value (e.g) 2  | 2022-03-06T11

let T = datatable(Timestamp:datetime, ResultType:string, ResultSignature:string, CorrelationId:string) [
"2022-03-02T14:35:05.6846874Z", "Throttled", "200", "a8de8a0b-2b95-4e16-a90f-d96c1f404850",
"2022-03-02T14:35:06.9535229Z", "Throttled", "200", "7e00ac15-6e82-42a5-8171-3145ae27728f",
"2022-03-02T14:34:21.1880149Z", "Non-Throttled", "200", "8fa9f7ee-6a91-4b8c-b170-9649befa698c",
"2022-03-02T14:34:36.9887590Z", "Non-Throttled", "200", "de7d82be-49b8-44dc-856c-16f76c7a4ae5",
"2022-03-02T14:34:39.3999879Z", "Non-Throttled", "200", "99b67d55-3ee4-4aee-9415-03919b2f23a4",
"2022-03-02T14:34:40.7854748Z", "Non-Throttled", "400", "dec5cd49-9d64-469a-83aa-db759c2e2fb1",
"2022-03-02T14:34:44.2007485Z", "Non-Throttled", "200", "5b412e71-6e48-49e2-9298-fd13d31619d1",
"2022-03-02T14:34:55.6858503Z", "Throttled", "200", "482592f9-722c-4f5d-8e48-967fa655d704",
"2022-03-02T14:25:17.0269766Z", "Throttled", "200", "1732c865-2474-4f76-b0cd-64af5981af7c",
"2022-03-02T14:25:18.9668944Z", "Throttled", "200", "234ec84c-3a0a-4329-a492-f8d590267ec6",
"2022-03-02T14:25:21.8262878Z", "Throttled", "200", "be8bd024-8f5c-4a01-9703-2945ef3bc8ba",
    ];
T 
| project Timestamp, ResultType, ResultSignature , CorrelationId
| summarize   RPM = count() by bin(Timestamp,1m)
| order by RPM desc
| take 1

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

凉城凉梦凉人心 2025-01-19 18:42:05

您可以在已聚合的计数之上使用 arg_min() 和 arg_max() 聚合函数。

例如：

let T = datatable(Timestamp:datetime, ResultType:string, ResultSignature:string, CorrelationId:string) [
"2022-03-02T14:35:05.6846874Z", "Throttled", "200", "a8de8a0b-2b95-4e16-a90f-d96c1f404850",
"2022-03-02T14:35:06.9535229Z", "Throttled", "200", "7e00ac15-6e82-42a5-8171-3145ae27728f",
"2022-03-02T14:34:21.1880149Z", "Non-Throttled", "200", "8fa9f7ee-6a91-4b8c-b170-9649befa698c",
"2022-03-02T14:34:36.9887590Z", "Non-Throttled", "200", "de7d82be-49b8-44dc-856c-16f76c7a4ae5",
"2022-03-02T14:34:39.3999879Z", "Non-Throttled", "200", "99b67d55-3ee4-4aee-9415-03919b2f23a4",
"2022-03-02T14:34:40.7854748Z", "Non-Throttled", "400", "dec5cd49-9d64-469a-83aa-db759c2e2fb1",
"2022-03-02T14:34:44.2007485Z", "Non-Throttled", "200", "5b412e71-6e48-49e2-9298-fd13d31619d1",
"2022-03-02T14:34:55.6858503Z", "Throttled", "200", "482592f9-722c-4f5d-8e48-967fa655d704",
"2022-03-02T14:25:17.0269766Z", "Throttled", "200", "1732c865-2474-4f76-b0cd-64af5981af7c",
"2022-03-02T14:25:18.9668944Z", "Throttled", "200", "234ec84c-3a0a-4329-a492-f8d590267ec6",
"2022-03-02T14:25:21.8262878Z", "Throttled", "200", "be8bd024-8f5c-4a01-9703-2945ef3bc8ba",
]
;
let M = materialize(
    T
    | summarize RPM = count() by bin(Timestamp, 1m)
    | summarize (minRpm, minT) = arg_min(RPM, Timestamp),
                (maxRpm, maxT) = arg_max(RPM, Timestamp)
);
union 
(M | project RPM = minRpm, Timestamp = minT, Label = "Min"),
(M | project RPM = maxRpm, Timestamp = maxT, Label = "Max")

You can use the arg_min() and arg_max() aggregation functions, on top of your already-aggregated counts.

For example:

let T = datatable(Timestamp:datetime, ResultType:string, ResultSignature:string, CorrelationId:string) [
"2022-03-02T14:35:05.6846874Z", "Throttled", "200", "a8de8a0b-2b95-4e16-a90f-d96c1f404850",
"2022-03-02T14:35:06.9535229Z", "Throttled", "200", "7e00ac15-6e82-42a5-8171-3145ae27728f",
"2022-03-02T14:34:21.1880149Z", "Non-Throttled", "200", "8fa9f7ee-6a91-4b8c-b170-9649befa698c",
"2022-03-02T14:34:36.9887590Z", "Non-Throttled", "200", "de7d82be-49b8-44dc-856c-16f76c7a4ae5",
"2022-03-02T14:34:39.3999879Z", "Non-Throttled", "200", "99b67d55-3ee4-4aee-9415-03919b2f23a4",
"2022-03-02T14:34:40.7854748Z", "Non-Throttled", "400", "dec5cd49-9d64-469a-83aa-db759c2e2fb1",
"2022-03-02T14:34:44.2007485Z", "Non-Throttled", "200", "5b412e71-6e48-49e2-9298-fd13d31619d1",
"2022-03-02T14:34:55.6858503Z", "Throttled", "200", "482592f9-722c-4f5d-8e48-967fa655d704",
"2022-03-02T14:25:17.0269766Z", "Throttled", "200", "1732c865-2474-4f76-b0cd-64af5981af7c",
"2022-03-02T14:25:18.9668944Z", "Throttled", "200", "234ec84c-3a0a-4329-a492-f8d590267ec6",
"2022-03-02T14:25:21.8262878Z", "Throttled", "200", "be8bd024-8f5c-4a01-9703-2945ef3bc8ba",
]
;
let M = materialize(
    T
    | summarize RPM = count() by bin(Timestamp, 1m)
    | summarize (minRpm, minT) = arg_min(RPM, Timestamp),
                (maxRpm, maxT) = arg_max(RPM, Timestamp)
);
union 
(M | project RPM = minRpm, Timestamp = minT, Label = "Min"),
(M | project RPM = maxRpm, Timestamp = maxT, Label = "Max")

回复收藏 0 原文

~没有更多了~