# Calculate avg on serial data **URL:** \ **Category:** Elasticsearch\ **Created:** [April 3, 2012, 9:54am UTC](https://discuss.elastic.co/t/calculate-avg-on-serial-data/7205 "2012-04-03T09:54:08Z")\ **Posts on this page:** 7\ **Page:** 1
**Author:** ![limac](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/limac/32/3981_2.png) [@limac](https://discuss.elastic.co/u/limac)\ **Post date:** [April 3, 2012, 9:54am UTC](https://discuss.elastic.co/t/calculate-avg-on-serial-data/7205/1 "2012-04-03T09:54:08Z")
Hi! I am using elasticsearch to store a serial values of a metric like {timestamp, value}. Now, I am trying to calculate the average of this metric of each minute in some time range. I did this by a filter to get all docs in that minute and calculate the avg and then the next minute. So I have to do a searching for every minute and the performance was terrible. Is there any sugestion for a better algorithm for better performance? Thank you for any comment! Alan ---
**Author:** ![Marcin\_Dojwa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marcin_dojwa/32/2307_2.png) [@Marcin\_Dojwa](https://discuss.elastic.co/u/Marcin_Dojwa)\ **Post date:** [April 3, 2012, 11:17am UTC](https://discuss.elastic.co/t/calculate-avg-on-serial-data/7205/2 "2012-04-03T11:17:27Z")
Hi, Check date\_histogram facet described here: > **[Elasticsearch Platform — Find real-time answers at scale](https://www.elastic.co)** > > Power insights and outcomes with the Elasticsearch Platform and AI. See into your data and find answers that matter with enterprise solutions designed to help you build, observe, and protect. Try Elasticsearch free today. Just give "interval":"minute" Then you have to work out how to return avg not count for each minute but I guess this can be done using "Script Value Field" (also described there). Best regards. 2012/4/3 Wang Yong [cnwangyong@gmail.com](mailto:cnwangyong@gmail.com) > Hi! > I am using elasticsearch to store a serial values of a metric like > {timestamp, value}. Now, I am trying to calculate the average of this > metric of each minute in some time range. I did this by a filter to get all > docs in that minute and calculate the avg and then the next minute. So I > have to do a searching for every minute and the performance was terrible. > Is there any sugestion for a better algorithm for better performance? Thank > you for any comment! > > Alan ---
**Author:** ![Marcin\_Dojwa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marcin_dojwa/32/2307_2.png) [@Marcin\_Dojwa](https://discuss.elastic.co/u/Marcin_Dojwa)\ **Post date:** [April 3, 2012, 1:26pm UTC](https://discuss.elastic.co/t/calculate-avg-on-serial-data/7205/3 "2012-04-03T13:26:20Z")
It's funny, I tried to help you and currently have the same problem 🙂 Does anyone know how to get date histogram with average field value within each date entry ? Standard date histogram allows to get count only but I do not know how to get sum, avg, etc... If I have at least sum I could get avg by myself. Thanks for help. Best regards. Marcin 2012/4/3 Marcin Dojwa [m.dojwa@livechatinc.com](mailto:m.dojwa@livechatinc.com) > Hi, > > Check date\_histogram facet described here: > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/facets/date-histogram-facet.html) > > Just give "interval":"minute" > > Then you have to work out how to return avg not count for each minute but > I guess this can be done using "Script Value Field" (also described there). > > Best regards. > > 2012/4/3 Wang Yong [cnwangyong@gmail.com](mailto:cnwangyong@gmail.com) > > > Hi! > > I am using elasticsearch to store a serial values of a metric like > > {timestamp, value}. Now, I am trying to calculate the average of this > > metric of each minute in some time range. I did this by a filter to get all > > docs in that minute and calculate the avg and then the next minute. So I > > have to do a searching for every minute and the performance was terrible. > > Is there any sugestion for a better algorithm for better performance? Thank > > you for any comment! > > > > Alan ---
**Author:** ![Marcin\_Dojwa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marcin_dojwa/32/2307_2.png) [@Marcin\_Dojwa](https://discuss.elastic.co/u/Marcin_Dojwa)\ **Post date:** [April 3, 2012, 1:42pm UTC](https://discuss.elastic.co/t/calculate-avg-on-serial-data/7205/4 "2012-04-03T13:42:39Z")
I found the answer, Alan, you have to use Date Histogram with key\_field pointing to timestamp and value\_field pointing value. Then you will get mean (average) of value within the timestamp entry. Best regards. 2012/4/3 Marcin Dojwa [m.dojwa@livechatinc.com](mailto:m.dojwa@livechatinc.com) > It's funny, I tried to help you and currently have the same problem 🙂 > Does anyone know how to get date histogram with average field value within > each date entry ? Standard date histogram allows to get count only but I do > not know how to get sum, avg, etc... If I have at least sum I could get avg > by myself. > > Thanks for help. > > Best regards. > Marcin > > 2012/4/3 Marcin Dojwa [m.dojwa@livechatinc.com](mailto:m.dojwa@livechatinc.com) > > > Hi, > > > > Check date\_histogram facet described here: > > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/facets/date-histogram-facet.html) > > > > Just give "interval":"minute" > > > > Then you have to work out how to return avg not count for each minute but > > I guess this can be done using "Script Value Field" (also described there). > > > > Best regards. > > > > 2012/4/3 Wang Yong [cnwangyong@gmail.com](mailto:cnwangyong@gmail.com) > > > > > Hi! > > > I am using elasticsearch to store a serial values of a metric like > > > {timestamp, value}. Now, I am trying to calculate the average of this > > > metric of each minute in some time range. I did this by a filter to get all > > > docs in that minute and calculate the avg and then the next minute. So I > > > have to do a searching for every minute and the performance was terrible. > > > Is there any sugestion for a better algorithm for better performance? Thank > > > you for any comment! > > > > > > Alan ---
**Author:** ![Marcin\_Dojwa](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/marcin_dojwa/32/2307_2.png) [@Marcin\_Dojwa](https://discuss.elastic.co/u/Marcin_Dojwa)\ **Post date:** [April 3, 2012, 2:35pm UTC](https://discuss.elastic.co/t/calculate-avg-on-serial-data/7205/5 "2012-04-03T14:35:45Z")
I've just found out that 'value\_field' must be mapped to have "index":"yes". Am I correct ? 2012/4/3 Marcin Dojwa [m.dojwa@livechatinc.com](mailto:m.dojwa@livechatinc.com) > I found the answer, Alan, you have to use Date Histogram with key\_field > pointing to timestamp and value\_field pointing value. Then you will get > mean (average) of value within the timestamp entry. > > Best regards. > > 2012/4/3 Marcin Dojwa [m.dojwa@livechatinc.com](mailto:m.dojwa@livechatinc.com) > > > It's funny, I tried to help you and currently have the same problem 🙂 > > Does anyone know how to get date histogram with average field value > > within each date entry ? Standard date histogram allows to get count only > > but I do not know how to get sum, avg, etc... If I have at least sum I > > could get avg by myself. > > > > Thanks for help. > > > > Best regards. > > Marcin > > > > 2012/4/3 Marcin Dojwa [m.dojwa@livechatinc.com](mailto:m.dojwa@livechatinc.com) > > > > > Hi, > > > > > > Check date\_histogram facet described here: > > > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/facets/date-histogram-facet.html) > > > > > > Just give "interval":"minute" > > > > > > Then you have to work out how to return avg not count for each minute > > > but I guess this can be done using "Script Value Field" (also described > > > there). > > > > > > Best regards. > > > > > > 2012/4/3 Wang Yong [cnwangyong@gmail.com](mailto:cnwangyong@gmail.com) > > > > > > > Hi! > > > > I am using elasticsearch to store a serial values of a metric like > > > > {timestamp, value}. Now, I am trying to calculate the average of this > > > > metric of each minute in some time range. I did this by a filter to get all > > > > docs in that minute and calculate the avg and then the next minute. So I > > > > have to do a searching for every minute and the performance was terrible. > > > > Is there any sugestion for a better algorithm for better performance? Thank > > > > you for any comment! > > > > > > > > Alan ---
**Author:** ![kimchy](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/kimchy/32/44952_2.png) [@kimchy](https://discuss.elastic.co/u/kimchy)\ **Post date:** [April 3, 2012, 2:37pm UTC](https://discuss.elastic.co/t/calculate-avg-on-serial-data/7205/6 "2012-04-03T14:37:22Z")
Yes, the value field needs ot be indexed as well. On Tue, Apr 3, 2012 at 5:35 PM, Marcin Dojwa [m.dojwa@livechatinc.com](mailto:m.dojwa@livechatinc.com)wrote: > I've just found out that 'value\_field' must be mapped to > have "index":"yes". Am I correct ? > > 2012/4/3 Marcin Dojwa [m.dojwa@livechatinc.com](mailto:m.dojwa@livechatinc.com) > > > I found the answer, Alan, you have to use Date Histogram with key\_field > > pointing to timestamp and value\_field pointing value. Then you will get > > mean (average) of value within the timestamp entry. > > > > Best regards. > > > > 2012/4/3 Marcin Dojwa [m.dojwa@livechatinc.com](mailto:m.dojwa@livechatinc.com) > > > > > It's funny, I tried to help you and currently have the same problem 🙂 > > > Does anyone know how to get date histogram with average field value > > > within each date entry ? Standard date histogram allows to get count only > > > but I do not know how to get sum, avg, etc... If I have at least sum I > > > could get avg by myself. > > > > > > Thanks for help. > > > > > > Best regards. > > > Marcin > > > > > > 2012/4/3 Marcin Dojwa [m.dojwa@livechatinc.com](mailto:m.dojwa@livechatinc.com) > > > > > > > Hi, > > > > > > > > Check date\_histogram facet described here: > > > > [Elasticsearch Platform — Find real-time answers at scale | Elastic](http://www.elasticsearch.org/guide/reference/api/search/facets/date-histogram-facet.html) > > > > > > > > Just give "interval":"minute" > > > > > > > > Then you have to work out how to return avg not count for each minute > > > > but I guess this can be done using "Script Value Field" (also described > > > > there). > > > > > > > > Best regards. > > > > > > > > 2012/4/3 Wang Yong [cnwangyong@gmail.com](mailto:cnwangyong@gmail.com) > > > > > > > > > Hi! > > > > > I am using elasticsearch to store a serial values of a metric like > > > > > {timestamp, value}. Now, I am trying to calculate the average of this > > > > > metric of each minute in some time range. I did this by a filter to get all > > > > > docs in that minute and calculate the avg and then the next minute. So I > > > > > have to do a searching for every minute and the performance was terrible. > > > > > Is there any sugestion for a better algorithm for better performance? Thank > > > > > you for any comment! > > > > > > > > > > Alan ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [July 6, 2017, 3:33am UTC](https://discuss.elastic.co/t/calculate-avg-on-serial-data/7205/7 "2017-07-06T03:33:50Z")