Calculating means from two different parsed fields in the same search
I am trying to calculate the mean for two different fields in a log. I can calculate each mean separately, but when I try to include both in the same search I get an error message. Is there a way to calculate two means in the same search? Here is my query:
_collector= ds_jobs "emr_usage" |json auto "fulla.cost", "pyspark.cost" as fulla_cost, pyspark_cost nodrop |avg(fulla_cost) |avg(pyspark_cost)
Here is the error:
Unable to run query: _collector= ds_jobs "emr_usage" |json auto keys "fulla.cost","pyspark.cost","pyspark_cost" as %"fulla_cost",%"pyspark_cost" refonly |avg(fulla_cost) |avg(pyspark_cost). field pyspark_cost not found (1)
-
Kelly,
The issue here is that using an aggregate operator drops other fields that aren't mentioned, so subsequent piped operators won't see them. You can run one query for both aggregates by comma separating them, which would make your new query look like the following:
_collector= dsjobs "emrusage"
| json auto "fulla.cost", "pyspark.cost" as fullacost, pysparkcost nodrop
| avg(fullacost), avg(pyspark_cost) -
David
Thanks for your quick response! I did try separating the two averages by a comma and I got the following error:
Unable to run query: _collector= ds_jobs "emr_usage" |json auto "fulla.cost", "pyspark.cost" as fulla_cost, pyspark_cost nodrop |avg(fulla_cost), avg(pyspark_cost). Schema.addField: Tried to add duplicate field _avg to Schema_avg:class java.lang.Double,map_1:class java.lang.Integer
-
Ah, right, I forgot.
If you're using the same operator, you'll need to rename one or both with "as" so there's not a field name conflict. Otherwise both operators try to create an "_avg" field.
_collector= dsjobs "emrusage"
| json auto "fulla.cost", "pyspark.cost" as fullacost, pysparkcost nodrop
| avg(fullacost) as full_avg, avg(pysparkcost) as park_avg
Please sign in to leave a comment.
Comments
4 comments