# Complicated aggregation query in period interval **URL:** \ **Category:** Elasticsearch\ **Created:** [October 6, 2019, 2:09pm UTC](https://discuss.elastic.co/t/complicated-aggregation-query-in-period-interval/202450 "2019-10-06T14:09:23Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![sevatal](https://avatars.discourse-cdn.com/v4/letter/s/b5e925/32.png) [@sevatal](https://discuss.elastic.co/u/sevatal)\ **Post date:** [October 6, 2019, 2:09pm UTC](https://discuss.elastic.co/t/complicated-aggregation-query-in-period-interval/202450/1 "2019-10-06T14:09:23Z")
We want to save historical information on a specific entity-type that our system creates and modify. This data will be used to run analytics and show charts about statistics over time. For simplicity let’s say it’s a User object which has various attributes (title, name, birthdate, role, and weight). The historical data is stored in ElasticSearch so that every change on the user object opens to create a new document with user attributes values and in addition EffectiveStartDate and EffectiveEndDate that represent the duration of time for which the values of the attribute were correct for this user. This means that for each user object at any point in time there should only be one document with the an empty EffectiveEndDate. The analytics and statistics we do are at a resolution of days, and we are looking a way to run a script while querying to duplicate the documents as the number of days in the interval [EffectiveStartDate, EffectiveEndDate] Question: Suppose the weight attribute of the user can change multiple times a day, how can we run the following query such that it will only take into account the last weight the user had in each day so we can create an aggregation such as “For the time period (week/month/year) query and aggregate(average, max, min) users-weight at the end of each day” ---
**Author:** ![gabriel\_tessier](https://sea2.discourse-cdn.com/elastic/user_avatar/discuss.elastic.co/gabriel_tessier/32/27911_2.png) [@gabriel\_tessier](https://discuss.elastic.co/u/gabriel_tessier)\ **Post date:** [October 7, 2019, 4:14am UTC](https://discuss.elastic.co/t/complicated-aggregation-query-in-period-interval/202450/2 "2019-10-07T04:14:33Z")
Hi @sevatal, If I understand correctly you have a user that post his weight several time a day and create a new doc on each update: ``` { "user": "Tom", "date_time": "2019-10-10 10:00:00", "weight": 5, ..... } { "user": "Tom", "date_time": "2019-10-10 11:00:00", "weight": 10, ..... } ``` So for this case you want to retrieve the value 10 as it's the last value for your user Tom this day (2019-10-10)? To get the last value you can order\_by date\_time desc to have the last value first then limit to 1 so you get the only last value, then you can make what ever aggregations you need. There's a range date field to help on interval search if you need to get a value for a week, or working days etc...: [https://www.elastic.co/guide/en/elasticsearch/reference/7.4/range.html](https://www.elastic.co/guide/en/elasticsearch/reference/7.4/range.html) ---
**Author:** ![sevatal](https://avatars.discourse-cdn.com/v4/letter/s/b5e925/32.png) [@sevatal](https://discuss.elastic.co/u/sevatal)\ **Post date:** [October 7, 2019, 2:26pm UTC](https://discuss.elastic.co/t/complicated-aggregation-query-in-period-interval/202450/3 "2019-10-07T14:26:29Z")
It is not exactly. Please, see example below: { "user": "Tom", "start\_date\_time": "2019-10-10 10:00:00", "end\_date\_time": "2019-10-10 11:00:00", "weight": 5, ..... } { "user": "Tom", "start\_date\_time": "2019-10-10 11:00:00", "end\_date\_time": "2019-12-10 11:00:00", "weight": 10, ..... } { "user": "Tom", "start\_date\_time": "2019-12-10 11:00:00", "end\_date\_time": "2019-12-10 12:00:00", "weight": 15, ..... } { "user": "Tom", "start\_date\_time": "2019-14-10 11:00:00", "end\_date\_time": null, "weight": 20, ..... } { "user": "Seva", "start\_date\_time": "2019-10-10 08:00:00", "end\_date\_time": "2019-13-10 09:00:00", "weight": 5, ..... } { "user": "Seva", "start\_date\_time": "2019-13-10 09:00:00", "end\_date\_time": "2019-14-10 11:00:00", "weight": 10, ..... } { "user": "Seva", "start\_date\_time": "2019-15-10 11:00:00", "end\_date\_time": null, "weight": 15, ..... } The question is: I want to know for the period of time ("2019-10-10", "2019-16-10") how many users were in each "weight" per day (end of day) Expected result: Date Weight Unique Count 2019-10-10 5 1 10 1 15 0 20 0 2019-11-10 5 1 10 1 15 0 20 0 2019-12-10 5 1 10 0 15 1 20 0 2019-13-10 5 0 10 1 15 1 20 0 2019-14-10 5 0 10 1 15 0 20 1 2019-15-10 5 0 10 0 15 1 20 1 2019-16-10 5 0 10 0 15 1 20 1 ---
**Author:** ![system](https://us1.discourse-cdn.com/elastic/original/3X/1/a/1ac57faf039f6b580b3f104ef42a2a89e41014de.png) [@system](https://discuss.elastic.co/u/system)\ **Post date:** [November 4, 2019, 2:26pm UTC](https://discuss.elastic.co/t/complicated-aggregation-query-in-period-interval/202450/4 "2019-11-04T14:26:30Z")
This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.