Sale Amount Per Second in a Five Minute Window


#1

I am trying to get the total amount of sale divide by 300 secs to get the sale amount per second in a five minute window

I am so far only able to construct the query until here. There seems to be no way to do a division on "total_value_five_mins".

My elasticsearch version is 2.3

Tried all the elasticsearch docs can't understand a single one.

{ 
  "size": 0,
  "query": {   
    "aggs" : {
        "five_minute_data" : {
            "date_histogram" : {
                "field" : "timestamp",
                "interval" : "5m"
            },
            "aggs": {
              "total_value_five_mins": {
                "sum": {
                  "field": "sales"
                }
              }
            }
            
        }
    }
}

(Abdon Pijpelink) #2

There are several ways of doing this, but the easiest may be to use a "bucket script aggregation" (https://www.elastic.co/guide/en/elasticsearch/reference/current/search-aggregations-pipeline-bucket-script-aggregation.html). This is an pipeline aggregation that executes a script on the result of another aggregation.

The following should work for you. This takes the result of the total_value_five_mins sum aggregation and divides the result by 300.

{
  "size": 0,
  "aggs": {
    "five_minute_data": {
      "date_histogram": {
        "field": "timestamp",
        "interval": "5m"
      },
      "aggs": {
        "total_value_five_mins": {
          "sum": {
            "field": "sales"
          }
        },
        "sales_per_second": {
          "bucket_script": {
            "buckets_path": {
              "total_value_five_mins": "total_value_five_mins"
            },
            "script": "params.total_value_five_mins / 300"
          }
        }
      }
    }
  }
}

#3

does bucket script works on elasticsearch 2.3?


(Abdon Pijpelink) #4

Yep, should work. Check out the 2.3 docs here: https://www.elastic.co/guide/en/elasticsearch/reference/2.3/search-aggregations-pipeline-bucket-script-aggregation.html


#5

Got this error

{
   "error": {
      "root_cause": [],
      "type": "reduce_search_phase_exception",
      "reason": "[reduce] ",
      "phase": "fetch",
      "grouped": true,
      "failed_shards": [],
      "caused_by": {
         "type": "script_exception",
         "reason": "failed to run inline script [params.total_value_five_mins / 300] using lang [groovy]",
         "caused_by": {
            "type": "missing_property_exception",
            "reason": "No such property: params for class: bf3afb5e69f712ab651c4734eb9b7e2fbd478eb8"
         }
      }
   },
   "status": 503
}

(Colin Goodheart-Smithe) #6

change your script to be "total_value_five_mins / 300" for version 2.3 of Elasticsearch.


(system) #8

This topic was automatically closed 28 days after the last reply. New replies are no longer allowed.