# Histogram aggregation on collapse result

**URL:** <https://discuss.elastic.co/t/histogram-aggregation-on-collapse-result/197440>\
**Category:** Elasticsearch\
**Created:** [August 30, 2019, 5:24am UTC](https://discuss.elastic.co/t/histogram-aggregation-on-collapse-result/197440 "2019-08-30T05:24:17Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![mybluedog24](https://avatars.discourse-cdn.com/v4/letter/m/8baadc/32.png) [@mybluedog24](https://discuss.elastic.co/u/mybluedog24)\
**Post date:** [August 30, 2019, 5:24am UTC](https://discuss.elastic.co/t/histogram-aggregation-on-collapse-result/197440/1 "2019-08-30T05:24:17Z")

</div>

Hi,

My question is that I need to find the first time login of each user, and then apply date\_histogram aggregation. I tried top\_hits aggregation, min aggregation and collapse. They all get the first time login of each user, but I don't know how to apply histogram aggregation on the top\_hit or min aggregation result. And for collapse, the doc says "The collapsing is applied to the top hits only and does not affect aggregations." Is there a way to do it?

Thank you so much.

Example data:

```auto
      {
        "_id" : "d78f4a88",
        "@timestamp" : "2019-08-23T20:03:13.297608",
        "eventType" : "login",
        "userId" : "9784a0008cf2"
      },
      {
        "_id" : "78852d56",
        "@timestamp" : "2019-08-27T18:13:58.963763",
        "eventType" : "login",
        "userId" : "9784a0008cf2"
      },
      {
        "_id" : "6a7b9406",
        "@timestamp" : "2019-08-28T03:47:04.704077",
        "eventType" : "login",
        "userId" : "3b3be93b0751"
      },
      {
        "_id" : "23490d4",
        "@timestamp" : "2019-08-28T23:54:23.704586",
        "eventType" : "login",
        "userId" : "3b3be93b0751"
      }

```

Thanks.

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [August 30, 2019, 9:09am UTC](https://discuss.elastic.co/t/histogram-aggregation-on-collapse-result/197440/2 "2019-08-30T09:09:47Z")

</div>

This sort of behavioural analysis is expensive - especially if the data is distributed across many nodes and the cardinality of userId is high.  
You need to bring the related data physically closer together using an entity-centric index keyed on userID. The new [dataframes](https://www.elastic.co/guide/en/elastic-stack-overview/current/ml-dataframes.html) feature would allow you to pivot your data in this way to make the analysis possible. You would join on the user ID and record the start date using the `min` aggregation on the timestamp field.

---

<div class="post-metadata">

**Author:** ![mybluedog24](https://avatars.discourse-cdn.com/v4/letter/m/8baadc/32.png) [@mybluedog24](https://discuss.elastic.co/u/mybluedog24)\
**Post date:** [August 30, 2019, 4:34pm UTC](https://discuss.elastic.co/t/histogram-aggregation-on-collapse-result/197440/3 "2019-08-30T16:34:56Z")

</div>

Hi @Mark_Harwood,

I see. Thank you so much. The data frame transforms is exactly what I need. But it's a new feature in version 7.2+ and our version is 6.7. Is there a way to do it without data frame transforms even it's expensive? We need a working demo soon before we can update to version 7.2.

Thank you so much.

---

<div class="post-metadata">

**Author:** ![Mark\_Harwood](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/mark_harwood/32/10538_2.png) [@Mark\_Harwood](https://discuss.elastic.co/u/Mark_Harwood)\
**Post date:** [August 30, 2019, 4:37pm UTC](https://discuss.elastic.co/t/histogram-aggregation-on-collapse-result/197440/4 "2019-08-30T16:37:54Z")

</div>

Try [this](https://twitter.com/elasticmark/status/1009380268409610240?s=20) script-based approach

---

<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:** [September 27, 2019, 4:38pm UTC](https://discuss.elastic.co/t/histogram-aggregation-on-collapse-result/197440/5 "2019-09-27T16:38:03Z")

</div>

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