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
}
}
]
}
}
}