How to calculate average off bucket valeus?

Elastic Stack Elasticsearch
1

HI,

I Have the following problem. Here are my aggs.
I now want to get the average of my just created agg: "tot_kwh".
Can someone explain me how to do this?

  "aggs": {
    "group_by_transaction_id": {
      "terms": {
        "field": "Transaction_ID"
      },
      "aggs": {
        "tot_kwh": {
          "sum": {
            "field": "DeltaMinutesPreviousRowWithinTransaction"
          }
      
    }
    }
  
}
}
2

Use the stats aggregation instead of sum, and it will produce the average value for you.

"aggs": {
"group_by_transaction_id": {
"terms": {
"field": "Transaction_ID"
},
"aggs": {
"tot_kwh": {
"stats": {
"field": "DeltaMinutesPreviousRowWithinTransaction"
}

}
}

3

Hi crickes,
Thanks for your reply, but thats not exaclty what I meant. I currently have multiple buckets which have a value of "tot_kwh" that I've calculated. And now I want to calculate the average of all those tot_kwh values.
I posted the reponse I'm getting from my current querie below to make things more clear.

"buckets": [
{
  "key": "1931709",
  "doc_count": 1038,
  "tot_kwh": {
    "value": 1036
  }
},
{
  "key": "1933523",
  "doc_count": 682,
  "tot_kwh": {
    "value": 682
  }
},
{
  "key": "1932252",
  "doc_count": 604,
  "tot_kwh": {
    "value": 2990
  }
}
4

Ah ok, then I think you need to be looking at the pipeline AVG Bucket aggregation.

https://www.elastic.co/guide/en/elasticsearch/reference/5.4/search-aggregations-pipeline-avg-bucket-aggregation.html

Something similar to (not tested):

"aggs": {
    "group_by_transaction_id": {
      "terms": {
        "field": "Transaction_ID"
      },
      "aggs": {
        "tot_kwh": {
          "stats": {
            "field": "DeltaMinutesPreviousRowWithinTransaction"
          }
      },
      "avg_total_power": {
           "avg_bucket": {
                "buckets_path": "group_by_transaction_id>tot_kwh" 
            }
}
}
5

Hi crickes,

This seems to work thank you very much!

1 Like
6

Glad I could help.

7

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.