# Group/count/average consecutive buckets

**URL:** <https://discuss.elastic.co/t/group-count-average-consecutive-buckets/123868>\
**Category:** Elasticsearch\
**Created:** [March 14, 2018, 9:36am UTC](https://discuss.elastic.co/t/group-count-average-consecutive-buckets/123868 "2018-03-14T09:36:17Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Patrick\_Lam](https://avatars.discourse-cdn.com/v4/letter/p/ed8c4c/32.png) [@Patrick\_Lam](https://discuss.elastic.co/u/Patrick_Lam)\
**Post date:** [March 14, 2018, 9:36am UTC](https://discuss.elastic.co/t/group-count-average-consecutive-buckets/123868/1 "2018-03-14T09:36:17Z")

</div>

Suppose I have a date/time field for each document. Now suppose that I group my documents into hourly buckets using a date\_histogram aggregation. Now suppose that there are some hours with no documents. So the document count for a 12 hour period might look like this:

Hour 1: 12  
2: 10  
3: 0  
4: 5  
5: 90  
6: 11  
7: 0  
8: 0  
9: 56  
10: 88  
11: 33  
12: 111

What I want to do is find the average number of consecutive buckets with non-zero counts. So here, we have the first cluster of 2 consecutive hours with non-zero counts, followed by hour 3 with 0 count, then 3 consecutive buckets of non-zero, followed by two buckets with 0, and finally 4 consecutive buckets of non-zero.

So in this example, what I want is the avg(2, 3, 4) = 3. Is there a clever way to do this in ES using some combination of aggregations?

---

<div class="post-metadata">

**Author:** ![jpountz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpountz/32/45836_2.png) [@jpountz](https://discuss.elastic.co/u/jpountz)\
**Post date:** [March 14, 2018, 10:01am UTC](https://discuss.elastic.co/t/group-count-average-consecutive-buckets/123868/2 "2018-03-14T10:01:17Z")

</div>

Not that I know of. But doing this on client-side looks easy? For the record, doing it on the Elasticsearch side wouldn't be more efficient.

---

<div class="post-metadata">

**Author:** ![Patrick\_Lam](https://avatars.discourse-cdn.com/v4/letter/p/ed8c4c/32.png) [@Patrick\_Lam](https://discuss.elastic.co/u/Patrick_Lam)\
**Post date:** [March 14, 2018, 10:27am UTC](https://discuss.elastic.co/t/group-count-average-consecutive-buckets/123868/3 "2018-03-14T10:27:18Z")

</div>

Would it not be more efficient from a data transfer over the network perspective if I were only retrieving an average value as opposed to all the bucket counts? Especially if I were doing this over many queries?

---

<div class="post-metadata">

**Author:** ![jpountz](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/jpountz/32/45836_2.png) [@jpountz](https://discuss.elastic.co/u/jpountz)\
**Post date:** [March 14, 2018, 10:39am UTC](https://discuss.elastic.co/t/group-count-average-consecutive-buckets/123868/4 "2018-03-14T10:39:32Z")

</div>

If your client application and the Elasticsearch cluster are on the same local network, this wouldn't be an issue. Even on a remote network I don't think this would be an issue unless the date histogram would loooots of buckets.

---

<div class="post-metadata">

**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\
**Post date:** [April 11, 2018, 10:39am UTC](https://discuss.elastic.co/t/group-count-average-consecutive-buckets/123868/5 "2018-04-11T10:39:39Z")

</div>

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