Cannot find the way to calculate average count of events per hour in Elasticsearch


(Icg) #1

I have several events occurred on different dates and hours. I want to know the average count of events per hour. How can I do it in Elasticsearch?

This is my mapping:

PUT /test
{
    "mappings": {
      "registry": {
      "_source": {
        "enabled": true
      },
      "_all": {
        "enabled": true
      },
        "properties": {
          "Id": {
            "type":"text"
          },
          "Location": {
            "type":"geo_point"
          },
          "Country": {
            "type":"keyword"
          },
          "EventDate": {
            "type":"date",
            "format": "yyyy-MM-dd HH:mm:ss"
          },
        }
      }
    }
}

For example I have the following dates:

2017-03-22 15:14:22
2017-03-22 15:15:21
2017-03-22 16:15:00
2017-03-23 15:10:20
2017-03-23 16:09:20

Let's put this data into Elasticsearch:

POST test/registry/_bulk
{"index":{"_id":"1"}}
{"Id":"111","Location":{ "lat": 41.12,"lon": -71.34},"Country":"ESP","EventDate":"2017-03-22 15:14:22.0"}
{"index":{"_id":"2"}}
{"Id":"222","Location":{ "lat": 41.22,"lon": -73.34},"Country":"ESP","EventDate":"2017-03-22 15:15:21.0"}
{"index":{"_id":"3"}}
{"Id":"333","Location":{ "lat": 41.22,"lon": -73.34},"Country":"NLD","EventDate":"2017-03-22 16:15:00.0"}
{"index":{"_id":"4"}}
{"Id":"333","Location":{ "lat": 41.22,"lon": -73.34},"Country":"NLD","EventDate":"2017-03-23 15:10:20.0"}
{"index":{"_id":"5"}}
{"Id":"333","Location":{ "lat": 41.22,"lon": -73.34},"Country":"NLD","EventDate":"2017-03-23 16:09:20.0"}

So, the average number of events per hour is 1.5 for 15 (because 2 on 2017-03-22 and 1 on 2017-03-23) and 1 for 16.

How can I do this in Elasticsearch (5.5) My final goal is to create a bar chart in Kibana showing the average count per hours, however I'd prefer to make all calculations in the backend (Elasticsearch).

Currently I tested this query:

POST /test/_search
{
    "size": 0,
    "aggs": {
        "my_date_histo":{                
            "date_histogram":{
                "field":"EventDate",
                "interval":"hour"
            },
            "aggs":{
                "the_count":{
                    "value_count" : { "field" : "Id"}
                },
                "the_movavg":{
                    "moving_avg":{ "buckets_path": "the_count" } 
                }
            }
        }
    }
}

But it gives me the moving average that is not aggregated by hours:

{
  "took": 5,
  "timed_out": false,
  "_shards": {
    "total": 5,
    "successful": 5,
    "failed": 0
  },
  "hits": {
    "total": 5,
    "max_score": 0,
    "hits": []
  },
  "aggregations": {
    "my_date_histo": {
      "buckets": [
        {
          "key_as_string": "2017-02-17 15:00:00.000",
          "key": 1487343600000,
          "doc_count": 2,
          "the_count": {
            "value": 2
          }
        },
        {
          "key_as_string": "2017-02-17 16:00:00.000",
          "key": 1487347200000,
          "doc_count": 1,
          "the_count": {
            "value": 1
          },
          "the_movavg": {
            "value": 2
          }
        },
        {
          "key_as_string": "2017-02-17 17:00:00.000",
          "key": 1487350800000,
          "doc_count": 0,
          "the_count": {
            "value": 0
          }
        },
 ....
            {
              "key_as_string": "2017-02-18 15:00:00.000",
              "key": 1487430000000,
              "doc_count": 1,
              "the_count": {
                "value": 1
              },
              "the_movavg": {
                "value": 1.5
              }
            },
            {
              "key_as_string": "2017-02-18 16:00:00.000",
              "key": 1487433600000,
              "doc_count": 1,
              "the_count": {
                "value": 1
              },
              "the_movavg": {
                "value": 1.3333333333333333
              }
            }
          ]
        }
      }
    }

(system) #2

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