# Calculating rolling average using aggregations **URL:** \ **Category:** Elasticsearch\ **Created:** [March 11, 2014, 8:26am UTC](https://discuss.elastic.co/t/calculating-rolling-average-using-aggregations/16281 "2014-03-11T08:26:18Z")\ **Posts on this page:** 11\ **Page:** 1
**Author:** ![Simon\_Cast](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/simon_cast/32/1514_2.png) [@Simon\_Cast](https://discuss.elastic.co/u/Simon_Cast)\ **Post date:** [March 11, 2014, 8:26am UTC](https://discuss.elastic.co/t/calculating-rolling-average-using-aggregations/16281/1 "2014-03-11T08:26:18Z")
Hi, I'm looking at using the new aggregations module of Elasticsearch to produce some metrics for my application. I want to calculate the rolling average for every hour in the last 28 days and then plot these on a chart. I can see how this could be done using a query per hour for the last 28 days. This seems problematic in terms of load and performance. Can this be done using a single query? Regards, Simon -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/d0a2dba7-f5a5-4944-ae0e-c6771b43ad6c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/d0a2dba7-f5a5-4944-ae0e-c6771b43ad6c%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Clinton\_Gormley](https://avatars.discourse-cdn.com/v4/letter/c/50afbb/32.png) [@Clinton\_Gormley](https://discuss.elastic.co/u/Clinton_Gormley)\ **Post date:** [March 11, 2014, 11:35am UTC](https://discuss.elastic.co/t/calculating-rolling-average-using-aggregations/16281/2 "2014-03-11T11:35:40Z")
Yes, easily. Aggregations are really powerful. Here's an example: # First insert some data curl -XPUT "[http://localhost:9200/myindex/mytype/1](http://localhost:9200/myindex/mytype/1)" -d' { "created": "2014/03/10 12:05:00", "somefield": 10 }' curl -XPUT "[http://localhost:9200/myindex/mytype/2](http://localhost:9200/myindex/mytype/2)" -d' { "created": "2014/03/10 12:05:00", "somefield": 5 }' curl -XPUT "[http://localhost:9200/myindex/mytype/3](http://localhost:9200/myindex/mytype/3)" -d' { "created": "2014/03/9 12:05:00", "somefield": 5 }' # Then return all results in the last 28 days # and calculate the avg per hour curl -XGET "[http://localhost:9200/myindex/mytype/\_search](http://localhost:9200/myindex/mytype/_search)" -d' { "query": { "range": { "created": { "gte": "now-28d/d", "to": "now" } } }, "aggs": { "per\_hour": { "date\_histogram": { "field": "created", "interval": "hour" }, "aggs": { "rolling\_avg": { "avg": { "field": "somefield" } } } } } }' On 11 March 2014 09:26, Simon Cast [simon.cast@gmail.com](mailto:simon.cast@gmail.com) wrote: > Hi, > > I'm looking at using the new aggregations module of Elasticsearch to > produce some metrics for my application. > > I want to calculate the rolling average for every hour in the last 28 days > and then plot these on a chart. I can see how this could be done using a > query per hour for the last 28 days. This seems problematic in terms of > load and performance. Can this be done using a single query? > > Regards, > > Simon > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/d0a2dba7-f5a5-4944-ae0e-c6771b43ad6c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/d0a2dba7-f5a5-4944-ae0e-c6771b43ad6c%40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/d0a2dba7-f5a5-4944-ae0e-c6771b43ad6c%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/d0a2dba7-f5a5-4944-ae0e-c6771b43ad6c%40googlegroups.com?utm_medium=email&utm_source=footer) > . > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAPt3XKS-tpSZMwM8WVBuXg%3Dez618xbTnCSTXAkWGrFsSxmdnew%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAPt3XKS-tpSZMwM8WVBuXg%3Dez618xbTnCSTXAkWGrFsSxmdnew%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Simon\_Cast](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/simon_cast/32/1514_2.png) [@Simon\_Cast](https://discuss.elastic.co/u/Simon_Cast)\ **Post date:** [March 12, 2014, 12:24pm UTC](https://discuss.elastic.co/t/calculating-rolling-average-using-aggregations/16281/3 "2014-03-12T12:24:02Z")
Thanks Clinton. That looks interesting but that creates an average of values per hour for the last 28 days. What I am looking for more precisely is to calculate a 28 rolling (or moving average) using the last 28 days of data and redoing that calculation every hour. I suppose what could be done is to do an average on a field that is a hourly count. On Tuesday, March 11, 2014 11:35:40 AM UTC, Clinton Gormley wrote: > Yes, easily. Aggregations are really powerful. Here's an example: > > # First insert some data > > curl -XPUT "[http://localhost:9200/myindex/mytype/1](http://localhost:9200/myindex/mytype/1)" -d' > { > "created": "2014/03/10 12:05:00", > "somefield": 10 > }' > > curl -XPUT "[http://localhost:9200/myindex/mytype/2](http://localhost:9200/myindex/mytype/2)" -d' > { > "created": "2014/03/10 12:05:00", > "somefield": 5 > }' > > curl -XPUT "[http://localhost:9200/myindex/mytype/3](http://localhost:9200/myindex/mytype/3)" -d' > { > "created": "2014/03/9 12:05:00", > "somefield": 5 > }' > > # Then return all results in the last 28 days > > # and calculate the avg per hour > > curl -XGET "[http://localhost:9200/myindex/mytype/\_search](http://localhost:9200/myindex/mytype/_search)" -d' > { > "query": { > "range": { > "created": { > "gte": "now-28d/d", > "to": "now" > } > } > }, > "aggs": { > "per\_hour": { > "date\_histogram": { > "field": "created", > "interval": "hour" > }, > "aggs": { > "rolling\_avg": { > "avg": { > "field": "somefield" > } > } > } > } > } > }' > > On 11 March 2014 09:26, Simon Cast \<[simon...@gmail.com](mailto:simon...@gmail.com) \\>wrote: > > > Hi, > > > > I'm looking at using the new aggregations module of Elasticsearch to > > produce some metrics for my application. > > > > I want to calculate the rolling average for every hour in the last 28 > > days and then plot these on a chart. I can see how this could be done using > > a query per hour for the last 28 days. This seems problematic in terms of > > load and performance. Can this be done using a single query? > > > > Regards, > > > > Simon > > > > -- > > You received this message because you are subscribed to the Google Groups > > "elasticsearch" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com) \. > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/d0a2dba7-f5a5-4944-ae0e-c6771b43ad6c%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/d0a2dba7-f5a5-4944-ae0e-c6771b43ad6c%40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/d0a2dba7-f5a5-4944-ae0e-c6771b43ad6c%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/d0a2dba7-f5a5-4944-ae0e-c6771b43ad6c%40googlegroups.com?utm_medium=email&utm_source=footer) > > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/15324eca-2048-4a57-abe4-f1730e1bf70e%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/15324eca-2048-4a57-abe4-f1730e1bf70e%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Clinton\_Gormley](https://avatars.discourse-cdn.com/v4/letter/c/50afbb/32.png) [@Clinton\_Gormley](https://discuss.elastic.co/u/Clinton_Gormley)\ **Post date:** [March 12, 2014, 1:43pm UTC](https://discuss.elastic.co/t/calculating-rolling-average-using-aggregations/16281/4 "2014-03-12T13:43:01Z")
Forgive my ignorance but could you provide a worked example of what you mean? I'm not getting exactly what you're after. On 12 March 2014 13:24, Simon Cast [simon.cast@gmail.com](mailto:simon.cast@gmail.com) wrote: > Thanks Clinton. > > That looks interesting but that creates an average of values per hour for > the last 28 days. What I am looking for more precisely is to calculate a 28 > rolling (or moving average) using the last 28 days of data and redoing that > calculation every hour. > > I suppose what could be done is to do an average on a field that is a > hourly count. > > On Tuesday, March 11, 2014 11:35:40 AM UTC, Clinton Gormley wrote: > > > Yes, easily. Aggregations are really powerful. Here's an example: > > > > # First insert some data > > > > curl -XPUT "[http://localhost:9200/myindex/mytype/1](http://localhost:9200/myindex/mytype/1)" -d' > > { > > "created": "2014/03/10 12:05:00", > > "somefield": 10 > > }' > > > > curl -XPUT "[http://localhost:9200/myindex/mytype/2](http://localhost:9200/myindex/mytype/2)" -d' > > { > > "created": "2014/03/10 12:05:00", > > "somefield": 5 > > }' > > > > curl -XPUT "[http://localhost:9200/myindex/mytype/3](http://localhost:9200/myindex/mytype/3)" -d' > > { > > "created": "2014/03/9 12:05:00", > > "somefield": 5 > > }' > > > > # Then return all results in the last 28 days > > > > # and calculate the avg per hour > > > > curl -XGET "[http://localhost:9200/myindex/mytype/\_search](http://localhost:9200/myindex/mytype/_search)" -d' > > { > > "query": { > > "range": { > > "created": { > > "gte": "now-28d/d", > > "to": "now" > > } > > } > > }, > > "aggs": { > > "per\_hour": { > > "date\_histogram": { > > "field": "created", > > "interval": "hour" > > }, > > "aggs": { > > "rolling\_avg": { > > "avg": { > > "field": "somefield" > > } > > } > > } > > } > > } > > }' > > > > On 11 March 2014 09:26, Simon Cast [simon...@gmail.com](mailto:simon...@gmail.com) wrote: > > > > > Hi, > > > > > > I'm looking at using the new aggregations module of Elasticsearch to > > > produce some metrics for my application. > > > > > > I want to calculate the rolling average for every hour in the last 28 > > > days and then plot these on a chart. I can see how this could be done using > > > a query per hour for the last 28 days. This seems problematic in terms of > > > load and performance. Can this be done using a single query? > > > > > > Regards, > > > > > > Simon > > > > > > -- > > > You received this message because you are subscribed to the Google > > > Groups "elasticsearch" group. > > > To unsubscribe from this group and stop receiving emails from it, send > > > an email to [elasticsearc...@googlegroups.com](mailto:elasticsearc...@googlegroups.com). > > > > > > To view this discussion on the web visit [https://groups.google.com/d/](https://groups.google.com/d/) > > > msgid/elasticsearch/d0a2dba7-f5a5-4944-ae0e-c6771b43ad6c% > > > [40googlegroups.com](http://40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/d0a2dba7-f5a5-4944-ae0e-c6771b43ad6c%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/d0a2dba7-f5a5-4944-ae0e-c6771b43ad6c%40googlegroups.com?utm_medium=email&utm_source=footer) > > > . > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). > > > > -- > > You received this message because you are subscribed to the Google Groups > > "elasticsearch" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/15324eca-2048-4a57-abe4-f1730e1bf70e%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/15324eca-2048-4a57-abe4-f1730e1bf70e%40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/15324eca-2048-4a57-abe4-f1730e1bf70e%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/15324eca-2048-4a57-abe4-f1730e1bf70e%40googlegroups.com?utm_medium=email&utm_source=footer) > > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAPt3XKRFsY\_F-dJdp2vN\_yLOtHZ1-brdE%2BvxN7ATdapf4rnOow%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAPt3XKRFsY_F-dJdp2vN_yLOtHZ1-brdE%2BvxN7ATdapf4rnOow%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Binh\_Ly\_2](https://avatars.discourse-cdn.com/v4/letter/b/d07c76/32.png) [@Binh\_Ly\_2](https://discuss.elastic.co/u/Binh_Ly_2)\ **Post date:** [March 12, 2014, 3:09pm UTC](https://discuss.elastic.co/t/calculating-rolling-average-using-aggregations/16281/5 "2014-03-12T15:09:59Z")
Unfortunately a MA aggregation is not available at the moment (unless you accumulate/include all the prior data that you need into every doc, or you run multiple queries and aggregate it yourself - neither of which is desirable or flexible) Clint, a MA is an average computed in a "sliding window" fashion, where the time interval slides as the average is computed. So for example, let's say I have a daily statistic for 5 days: [Day1 = 100, Day 2 = 105, Day 3 = 101, Day 4 = 120, Day 5 = 200] A 2 day in-the-past MA would be computed as follows: Day1 = No data yet (since we don't have 2 days in-the-past worth of info) Day2 = (Day1 + Day2) / 2 = (100 + 105) / 2 Day3 = (Day2 + Day3) / 2 = (105 + 101) / 2 ... -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/89d909af-377e-44bd-8cec-31249f9f1d60%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/89d909af-377e-44bd-8cec-31249f9f1d60%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Clinton\_Gormley](https://avatars.discourse-cdn.com/v4/letter/c/50afbb/32.png) [@Clinton\_Gormley](https://discuss.elastic.co/u/Clinton_Gormley)\ **Post date:** [March 12, 2014, 3:29pm UTC](https://discuss.elastic.co/t/calculating-rolling-average-using-aggregations/16281/6 "2014-03-12T15:29:55Z")
Heya Bihn The part I'm not getting is this: "the rolling average for every hour in the last 28 days". ie what period should each bucket/rolling avg cover? an hour? 28 days? You can still do rolling averages with aggregations, but they require a bit more work. I wanted to get the exact specs before trying to answer again ๐Ÿ™‚ clint On 12 March 2014 16:09, Binh Ly [binhly\_es@yahoo.com](mailto:binhly_es@yahoo.com) wrote: > Unfortunately a MA aggregation is not available at the moment (unless you > accumulate/include all the prior data that you need into every doc, or you > run multiple queries and aggregate it yourself - neither of which is > desirable or flexible) > > Clint, a MA is an average computed in a "sliding window" fashion, where > the time interval slides as the average is computed. So for example, let's > say I have a daily statistic for 5 days: > > [Day1 = 100, Day 2 = 105, Day 3 = 101, Day 4 = 120, Day 5 = 200] > > A 2 day in-the-past MA would be computed as follows: > > Day1 = No data yet (since we don't have 2 days in-the-past worth of info) > Day2 = (Day1 + Day2) / 2 = (100 + 105) / 2 > Day3 = (Day2 + Day3) / 2 = (105 + 101) / 2 > ... > > -- > You received this message because you are subscribed to the Google Groups > "elasticsearch" group. > To unsubscribe from this group and stop receiving emails from it, send an > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > To view this discussion on the web visit > [https://groups.google.com/d/msgid/elasticsearch/89d909af-377e-44bd-8cec-31249f9f1d60%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/89d909af-377e-44bd-8cec-31249f9f1d60%40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/89d909af-377e-44bd-8cec-31249f9f1d60%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/89d909af-377e-44bd-8cec-31249f9f1d60%40googlegroups.com?utm_medium=email&utm_source=footer) > . > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAPt3XKSkaL59uXBw6b9b\_0r\_%2BJHVPR-WL2P0JuXox90xCJDh%3Dg%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAPt3XKSkaL59uXBw6b9b_0r_%2BJHVPR-WL2P0JuXox90xCJDh%3Dg%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Clinton\_Gormley](https://avatars.discourse-cdn.com/v4/letter/c/50afbb/32.png) [@Clinton\_Gormley](https://discuss.elastic.co/u/Clinton_Gormley)\ **Post date:** [March 12, 2014, 7:56pm UTC](https://discuss.elastic.co/t/calculating-rolling-average-using-aggregations/16281/7 "2014-03-12T19:56:56Z")
OK, Binh took me aside and explained what you are after: a number per hour which reflects the average of the previous 28\*24 hours. There are two ways you can do this with aggregations. The first way is to create overlapping buckets, so each date will fall into multiple windows, eg: ``` Jan 1, 10 am to Jan 28, 10am Jan 1, 11 am to Jan 28, 11am Jan 1, 12 am to Jan 28, 12am etc ``` ... which can be done by constructing all of the date ranges you need, ie 28 \* 24 of them. The second way to do it is to use a script to convert each date into multiple dates: $date $date - 1h $date - 2h $date - 3h ... $date - 672h I've put together a simplified example (rolling avg per day) which demonstrates both techniques. Unfortunately, the script version showed up a bug in aggregations, which will be fixed in 1.1.0 - currently you can include 4 values, but no more. See the demo here: [Rolling average with Elasticsearch aggregations ยท GitHub](https://gist.github.com/clintongormley/9515005) clint On 12 March 2014 16:29, Clinton Gormley [clint@traveljury.com](mailto:clint@traveljury.com) wrote: > Heya Bihn > > The part I'm not getting is this: "the rolling average for every hour in > the last 28 days". ie what period should each bucket/rolling avg cover? > an hour? 28 days? > > You can still do rolling averages with aggregations, but they require a > bit more work. I wanted to get the exact specs before trying to answer > again ๐Ÿ™‚ > > clint > > On 12 March 2014 16:09, Binh Ly [binhly\_es@yahoo.com](mailto:binhly_es@yahoo.com) wrote: > > > Unfortunately a MA aggregation is not available at the moment (unless you > > accumulate/include all the prior data that you need into every doc, or you > > run multiple queries and aggregate it yourself - neither of which is > > desirable or flexible) > > > > Clint, a MA is an average computed in a "sliding window" fashion, where > > the time interval slides as the average is computed. So for example, let's > > say I have a daily statistic for 5 days: > > > > [Day1 = 100, Day 2 = 105, Day 3 = 101, Day 4 = 120, Day 5 = 200] > > > > A 2 day in-the-past MA would be computed as follows: > > > > Day1 = No data yet (since we don't have 2 days in-the-past worth of info) > > Day2 = (Day1 + Day2) / 2 = (100 + 105) / 2 > > Day3 = (Day2 + Day3) / 2 = (105 + 101) / 2 > > ... > > > > -- > > You received this message because you are subscribed to the Google Groups > > "elasticsearch" group. > > To unsubscribe from this group and stop receiving emails from it, send an > > email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). > > To view this discussion on the web visit > > [https://groups.google.com/d/msgid/elasticsearch/89d909af-377e-44bd-8cec-31249f9f1d60%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/89d909af-377e-44bd-8cec-31249f9f1d60%40googlegroups.com)[https://groups.google.com/d/msgid/elasticsearch/89d909af-377e-44bd-8cec-31249f9f1d60%40googlegroups.com?utm\_medium=email&utm\_source=footer](https://groups.google.com/d/msgid/elasticsearch/89d909af-377e-44bd-8cec-31249f9f1d60%40googlegroups.com?utm_medium=email&utm_source=footer) > > . > > > > For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAPt3XKQ3ZGG9Ak4AWUgsk%3DYMkQez9%3DLWdixzosRbDQS5ouqUiQ%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAPt3XKQ3ZGG9Ak4AWUgsk%3DYMkQez9%3DLWdixzosRbDQS5ouqUiQ%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Clinton\_Gormley](https://avatars.discourse-cdn.com/v4/letter/c/50afbb/32.png) [@Clinton\_Gormley](https://discuss.elastic.co/u/Clinton_Gormley)\ **Post date:** [March 13, 2014, 8:52am UTC](https://discuss.elastic.co/t/calculating-rolling-average-using-aggregations/16281/8 "2014-03-13T08:52:14Z")
I rethought this problem last night. The solutions I've presented already are a lot less efficient than they could be, as they increase the work per doc by a factor of the number of buckets (ie 24h \* 28d = 672). It'd be much more efficient to calculate this rolling average client side in a single pass over the data, eg: curl -XGET "[http://localhost:9200/myindex/\_search?size=0](http://localhost:9200/myindex/_search?size=0)" -d' { "aggs": { "per\_day": { "date\_histogram": { "field": "date", "interval": "day", "format": "yyyy-MM-dd" }, "aggs": { "total": { "sum": { "field": "num" } } } } } }' This gives you the doc count per interval, plus the total of `num` per interval. Now, it is easy to calculate the rolling average in a single pass across each slot: 1. Add up the doc counts and num totals for all values in the desired window. 2. Calc the average for the window 3. End if there are no more docs 4. Remove the oldest doc from total doc count and total num 5. Add the next doc into total doc count and total num 6. Repeat from step 2 -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/CAPt3XKSuE5M3reGFnWBY0EytVJUb2omkn1-XtsD9yrKutXtDBQ%40mail.gmail.com](https://groups.google.com/d/msgid/elasticsearch/CAPt3XKSuE5M3reGFnWBY0EytVJUb2omkn1-XtsD9yrKutXtDBQ%40mail.gmail.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Michael\_Gallego](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/michael_gallego/32/933_2.png) [@Michael\_Gallego](https://discuss.elastic.co/u/Michael_Gallego)\ **Post date:** [September 19, 2014, 12:12pm UTC](https://discuss.elastic.co/t/calculating-rolling-average-using-aggregations/16281/9 "2014-09-19T12:12:54Z")
+1 for this feature! What I need is pretty similar: calculate rolling sum, so for each day, I need to sum the previous 30 days (on each point). Oracle and Postgre make this very easy with aggregation function (and they can take advantage of very interesting optimization for sum, as each point is actually the sum of previous day + sum of elements in the given day). Actually, the simplest way I've found is asking for more data (so if I want rolling sum for 30 days, I ask for 60 days, and do the sum myself). But this is quite inefficient. -- You received this message because you are subscribed to the Google Groups "elasticsearch" group. To unsubscribe from this group and stop receiving emails from it, send an email to [elasticsearch+unsubscribe@googlegroups.com](mailto:elasticsearch+unsubscribe@googlegroups.com). To view this discussion on the web visit [https://groups.google.com/d/msgid/elasticsearch/b72eea67-d8cb-4ac3-ac4f-4e984183731b%40googlegroups.com](https://groups.google.com/d/msgid/elasticsearch/b72eea67-d8cb-4ac3-ac4f-4e984183731b%40googlegroups.com). For more options, visit [https://groups.google.com/d/optout](https://groups.google.com/d/optout). ---
**Author:** ![Michael\_Sander](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/michael_sander/32/838_2.png) [@Michael\_Sander](https://discuss.elastic.co/u/Michael_Sander)\ **Post date:** [November 15, 2016, 9:51pm UTC](https://discuss.elastic.co/t/calculating-rolling-average-using-aggregations/16281/10 "2016-11-15T21:51:58Z")
Bumping this thread. I'm looking for a similar aggregation. I'd like to create a **rolling cumulative average** , meaning, for each day, calculate the average over all previous days. I'd like to plot how the cumulative average changes over time. This is different than a windowed average because the window gets longer as time goes on. From what I've read above, it doesn't look like this can be done with a single aggregation. It looks like it requires an aggregation for each day. But perhaps some new features were added in the past two years that makes this possible. Any ideas? ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 5, 2017, 10:05pm UTC](https://discuss.elastic.co/t/calculating-rolling-average-using-aggregations/16281/11 "2017-07-05T22:05:44Z")