CODEWITHSUNDEEP
elasticsearch
Manoj R
Manoj R

Reputation: 3247

elasticsearch Need average per week of some value

I have simple data as 

sales, date_of_sales

I need is average per week i.e. sum(sales)/no.of weeks.

Please help.

What i have till now is 

{
  "size": 0,
  "aggs": {
    "WeekAggergation": {
      "date_histogram": {
        "field": "date_of_sales",
        "interval": "week"
      }
    },
    "TotalSales": {
      "sum": {
        "field": "sales"
      }
    },
    "myValue": {
      "bucket_script": {
        "buckets_path": {
          "myGP": "TotalSales",
          "myCount": "WeekAggergation._bucket_count"
        },
        "script": "params.myGP/params.myCount"
      }
    }
  }
}

I get the error 

Invalid pipeline aggregation named [myValue] of type [bucket_script]. 
Only sibling pipeline aggregations are allowed at the top level.

Upvotes: 0

Views: 833

Answers (1)

James Pittiglio
James Pittiglio

Reputation: 641

I think this may help:

{
  "size": 0,
  "aggs": {
    "WeekAggergation": {
      "date_histogram": {
        "field": "date_of_sale",
        "interval": "week",
        "format": "yyyy-MM-dd"
      },
      "aggs": {
        "TotalSales": {
          "sum": {
            "field": "sales"
          }
        },
        "AvgSales": {
          "avg": {
            "field": "sales"
          }
        }
      }
    },
    "avg_all_weekly_sales": {
      "avg_bucket": {
        "buckets_path": "WeekAggergation>TotalSales"
      }
    }
  }
}

Note the TotalSales aggregation is now a nested aggregation under the weekly histogram aggregation (I believe there was a typo in the code provided - the simple schema provided indicated the field name of date_of_sale and the aggregation provided uses the plural form date_of_sales). This provides you a total of all sales in the weekly bucket.

Additionally, AvgSales provides a similar nested aggregation under the weekly histogram aggregation so you can see the average of all sales specific to that week.

Finally, the pipeline aggregation avg_all_weekly_sales will give the average of weekly sales based on the TotalSales bucket and the number of non-empty buckets - if you want to include empty buckets, add the gap_policy parameter like so:

...

"avg_all_weekly_sales": {
      "avg_bucket": {
        "buckets_path": "WeekAggergation>TotalSales",
        "gap_policy": "insert_zeros"
      }
    }

...

(see: https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-avg-bucket-aggregation.html).

This pipeline aggregation may or may not be what you're actually looking for, so please check the math to ensure the result is what is expected, but should provide the correct output based on the original script.

Upvotes: 1

Related Questions