How to use the original data after aggregation?
num
----
10
20
30
40
50
I would like to calculate the average value of 30 for the above data using avg operator, and then subtract the average value from each of these data to obtain the following data.
avg (num) as deviation_num
deviation_num
----
-20
-10
0
10
20
However, running avg operator causes the aggregation to disappear and the original num is no longer displayed.
How can I perform such a calculation?
-
Hello Noguchi-san,
Let me share a sample for you.
(_sourceCategory=Labs/Apache/Access GET)
| parse "\"GET * HTTP/1.1\" * * *" as uri,status,size,referrer
| where status in ("200", "304", "403", "401", "500")
| status="200" ? 1 : 0 as total200
| status="304" ? 1 : 0 as total304
| status="403" ? 1 : 0 as total403
| status="500" ? 1 : 0 as total500
| status="401" ? 1 : 0 as total401
| sum(total200) as total200, sum(total304) as total304, sum(total403) as total403, sum(total500) as total500, sum(total401) as total401
| (total200 + total304 + total403 + total500 + total401) / 5 as sumavg
| (total200 - sumavg) as div200
| (total304 - sumavg) as div304
| (total403 - sumavg) as div403
| (total500 - sumavg) as div500
| (total401 - sumavg) as div401
| Fields - total200, total304, total403, total500, total401, sumavg
Output
-
回答ありがとうございます。
すみません、説明を分かりやすくするため、シンプルなデータで質問を上げましたが、実際に扱いたいデータは、5 つとは限らず、無数に存在し得るデータとなっています。
この場合、"200", "304", "403", "401", "500" の 5 つをフィールドとして追加しているので、このような処理が実現できていると思うのですが、5 で割るという処理も、5 というのを直接書いていますが、これもデータの個数をカウントして、その変数で割るという計算にしたいのです。
そのようなことが可能なものでしょうか。
以前、同様のことを Splunk だと eval というコマンドで実現可能だったものでして。
Please sign in to leave a comment.
Comments
2 comments