How to return hour by hour average on sum of 1h, for the last X days?

Question

My docs look like this:

{
        "_index": "mkt-time",
        "_id": "KN42CoMBbJv2xs4YVazh",
        "_score": 1,
        "_source": {
          "ExecNom": 265636.56,          
          "@timestamp": "2022-08-31T18:53:53.000Z",
          "other_field": "value"
          
        }
      },
      {
        "_index": "mkt-time",
        "_id": "Kd42CoMBbJv2xs4YVazh",
        "_score": 1,
        "_source": {          
          "ExecNom": 356136.067941,
          "@timestamp": "2022-09-02T09:11:05.000Z",
          "other_field": "value"
        }

Briefly, what I want do is:

[
    avg(sum(2022-08-14T00:00:00 -> 2022-08-14T01:00:00), sum(2022-08-15T00:00:00 -> 2022-08-15T01:00:00), ..., sum(2022-08-22T00:00:00 -> 2022-08-14T01:00:00)),
    avg(sum(2022-08-14T01:00:00 -> 2022-08-14T02:00:00), sum(2022-08-15T01:00:00 -> 2022-08-15T02:00:00), ..., sum(2022-08-22T01:00:00 -> 2022-08-22T02:00:00)),
    avg(sum(2022-08-14T02:00:00 -> 2022-08-14T03:00:00), sum(2022-08-15T02:00:00 -> 2022-08-15T03:00:00), ..., sum(2022-08-22T02:00:00 -> 2022-08-22T03:00:00)),
    ...
    avg(sum(2022-08-14T23:00:00 -> 2022-08-15T00:00:00), sum(2022-08-15T23:00:00 -> 2022-08-16T00:00:00), ..., sum(2022-08-22T00:00:00 -> 2022-08-23T00:00:00)))
]

I drafted something like:

GET /mkt-time/_search
{
  "size": 0,
  "runtime_mappings": {
    "hour_of_day": {
      "type": "keyword",
      "script": {
        "source": "emit(doc['@timestamp'].value.getHour().toString())"
      }
    }
  },
  "aggs": {
     "dates_between": {
        "filter": {
           "range": {
            "@timestamp": {
              "gte": "now-30d/d",
              "lt": "now/d"
            }
          }
        }
    },
    "per_hour": {
      "terms": {
        "field": "hour_of_day",
        "size":30
      },
      "aggs": {
        "avg_per_dat": {
// This is not what I want.  
//what I actually need is sum in that Hour, then divide the sum by number of days.
          "avg": { 
            "field": "ExecNom"
          }
        }
      }
    }
  }
}

Thank you!

// Please ignore the following // why SOF complains " It looks like your post is mostly code; please add some more details." quite annoying actually.

How to return hour by hour average on sum of 1h, for the last X days?

0 Answers