CODEWITHSUNDEEP
elasticsearch
TyForHelpDude
TyForHelpDude

Reputation: 5001

sum all values in specific hours of date dsl queries

I have index contains docs looks like this:

"_source": {
               "price": "11",
               "loggingdate": "15/02/2016 08:56:58",
            }

I need a query that sum all values in price values between 10:00 - 12:00 in this year

I need total of each hour(10:00,11:00,12:00) This gives me total 3 hours of the result(only 1 item in bucket) but I need in seperated for each hour(three item in bucket)

    {
   "size":0,
   "query":{
      "filtered":{
         "filter":{
            "bool":{
               "must":[
                  {
                     "range":{
                        "loggingdate":{
                           "gte":"now-1y"
                        }
                     }
                  },
                  {

                  },
                  {
                     "script":{
                        "script":"doc.loggingdate.date.getHourOfDay() >= 10 && doc.loggingdate.date.getHourOfDay() <= 12"
                     }
                  }
               ]
            }
         }
      }
   },
     "aggs": {
    "by_hour": {
      "date_histogram": {
        "field": "loggingdate",
        "interval": "hour"
      },
      "aggs": {
        "total": {
          "sum": {
            "field": "price"
          }
        }
      }
    }
  }
}

result:

"aggregations": {
      "by_hour": {
         "buckets": [
            {
               "key_as_string": "15/01/2016 10:00:00",
               "key": 1452852000000,
               "doc_count": 58453,
               "total": {
                  "value": 2106110494
               }
            },
            {
               "key_as_string": "15/01/2016 11:00:00",
               "key": 1452855600000,
               "doc_count": 23243,
               "total": {
                  "value": 849522038
               }
            },
            {
               "key_as_string": "15/01/2016 12:00:00",
               "key": 1452859200000,
               "doc_count": 11994,
               "total": {
                  "value": 430906409
               }
            },
            {
               "key_as_string": "17/01/2016 10:00:00",
               "key": 1453024800000,
               "doc_count": 1,
               "total": {
                  "value": 0
               }
            },...

I think I need use range with date_histogram but how can I sum all price values in other docs date_histogram gives me only docs in range..

Any idea?

Upvotes: 0

Views: 541

Answers (1)

Val
Val

Reputation: 217504

You basically need a range filter to select only documents of the desired year and then another script filter in order to only select documents with the hours between 10am and 12am. Finally, you simply need a sum aggregation to sum all the prices of the matching documents.

{
  "query": {
    "bool": {
      "filter": [
        {
          "range": {
            "loggingdate": {
              "gte": "2016-01-01",
              "lt": "2017-01-01"
            }
          }
        },
        {
          "script": {
            "script": "doc.loggingdate.date.getHourOfDay() >= min && doc.loggingdate.date.getHourOfDay() <= max",
            "params": {
              "min": 10,
              "max": 12
            }
          }
        }
      ]
    }
  },
  "aggs": {
    "total": {
      "sum": {
        "field": "price"
      }
    }
  }
}

UPDATE

If you need the total by hour, you can use this aggregations instead:

  "aggs": {
    "by_hour": {
      "terms": {
        "script": "doc.loggingdate.date.getHourOfDay()"
      },
      "aggs": {
        "total": {
          "sum": {
            "field": "price"
          }
        }
      }
    }
  }

Upvotes: 1

Related Questions