Average per day for a period

[Igor-lkm (Igor Likhomanov)]

Hello,

I have a quite simple aggregation to get sum and average total price for a period (1 day, 7 days, 30 days etc.) from my index. That works quite good.

Aggregation for 30 days looks like this:

{
  "size": 10000,
  "aggs": {
    "30d": {
      "filter": {
          "range": {
            "data.timestamp": {
              "gt": "now-30d"
            }
        }
      },
      "aggs": {

        "avgTotalPrice": { "avg": { "field": "data.payload.totalPrice" } },
        "sumTotalPrice": { "sum": { "field": "data.payload.totalPrice" } }
      }
    },
}

Now I would like to get daily average, just one number.

In fact what I am looking for is sumTotalPrice / amount of days

What is the most elegant way to do this?

My first thought was to get (somehow) amount of days based on range filter and use it in scripted field. Like "gt": "now-30d" is 30 days and "gt": "now-1m" is amount of days depends of the month. I would like to ask if this is possible.. (as I failed to do this) And then do my calculations sumTotalPrice / amount_of_days.

Is it possible or is there a better, simple and elegant way?

Thank you!

[Igor-lkm (Igor Likhomanov)]

At the end of the day I got what i need by using date_histogram and avg_bucket:

Query:

{
  "size": 0,
  "aggs": {
    "3d": {
      "filter": {
          "range": {
            "data.timestamp": {
              "gt": "now-3d"
            }
        }
      },
      "aggs": {
        "order_per_period": {
           "date_histogram": {
              "field": "data.timestamp",
              "calendar_interval": "day"
            },
            "aggs": {
              "sum_total_price": {
               "sum": {
                  "field": "data.totalPrice"
                }
              }
            }
         },
         "avgSumTotalPrice": {
          "avg_bucket": {
            "buckets_path": "order_per_period>sum_total_price"
         }
       }
      }
    }
  }
}

Response:

{
  "took": 3,
  "timed_out": false,
  "_shards": {
    "total": 1,
    "successful": 1,
    "skipped": 0,
    "failed": 0
  },
  "hits": {
    "total": {
      "value": 10000,
      "relation": "gte"
    },
    "max_score": null,
    "hits": []
  },
  "aggregations": {
    "30d": {
      "doc_count": 84,
      "order_per_period": {
        "buckets": [
          {
            "key_as_string": "2022-07-18T00:00:00.000Z",
            "key": 1658102400000,
            "doc_count": 26,
            "sum_total_price": {
              "value":  180
            }
          },
          {
            "key_as_string": "2022-07-19T00:00:00.000Z",
            "key": 1658188800000,
            "doc_count": 44,
            "sum_total_price": {
              "value": 390
            }
          },
          {
            "key_as_string": "2022-07-20T00:00:00.000Z",
            "key": 1658275200000,
            "doc_count": 13,
            "sum_total_price": {
              "value": 100
            }
          }
        ]
      },
      "avgSumTotalPrice": {
        "value": 233.33
      }
    }
  }
}