Extend the Avg Aggregation to support weighted average

[lquerel]

The current Avg Aggretation only supports the simple form of average (ie: sum(values) / num of values). In order to support weighted average (ie: sum( value * weight) / sum (weight)) in an efficient way, I suggest the following changes in the API.

First implementation without modification of the existing API
The script property in the avg object can return a number of a list of numbers. My proposal is to also support a map containing 2 fixed properties: value and weight (or values and weights if multiple values) (. For example:

{"avg" : { "script" : "[value: doc['latency'].value, weight: doc['transaction'].value]" }}

I prototyped this solution with only 2 modifications. A first modification in the ScriptDoubleValues class to take into account this map. A second modification in the AvgAggregator class to apply the right formula accordingly to the context.

Second implementation requiring minor modifications of the existing API (more efficient because no script)
We can add a new property 'weight' to define the field to use in the document.

{ "avg" : { "field" : "latency",  "weight" : "transaction"} } 

I didn't prototype this solution because I'm still not sure which DoubleValues implementation to use to get efficiently the value and the weight. If you have some advices don't hesitate.

A third option is to use scripted metrics but until now it's not possible to sort the result of scripted metrics so that doesn't work for me. I created a pull request to add this support but I'm still waiting approval. But anyway a solution based on scripted metrics will be less efficient.

This proposal should be easy to implement and I'm pretty sure that many users will be happy with this extended version of the avg aggregation.

[jpountz]

Actually this is typically the kind of use-case for which the avg aggregation supports scripts. Any reason why you're not happy with scripts?

[lquerel]

The weighted average is equals to sum(value-i * weight-i) / sum(weight-i). Value and Weight are dynamic values extracted from a set of documents. Currently the script only returns the value for each document so I don't see any mathematical way to compute easily a weighted average without the weight for each document knowing that currently the result of the script is summed and divided at the end by the number of values (and not the sum of weights).

[lquerel]

I sent a pull request with the implementation of the first approach based on the script attribute.

#15781

For info, this pull request solves this issue too: #8486

[jpountz]

The weighted average is equals to sum(value-i * weight-i) / sum(weight-i). Value and Weight are dynamic values extracted from a set of documents. Currently the script only returns the value for each document so I don't see any mathematical way to compute easily a weighted average without the weight for each document knowing that currently the result of the script is summed and divided at the end by the number of values (and not the sum of weights).

Right, this involves some client-side logic, something like (not tested):

{
  "aggs": {
    "weighted_sum": {
      "sum": {
        "script": {
          "inline": "doc['value'].value * doc['weight'].value"
        }
      }
    },
    "sum_of_weights": {
      "sum": {
        "field": "weight"
        }
      }
    }
  }
}

And then you would need to divide the weighted_sum value by sum_of_weights. I can understand that this is less convenient that that eg you can't use it with kibana so I'm open to discussing adding a way to do weighted averages easily. But I would like to make sure there is enough demand for it before (so that the feature is worth maintaining) and have a discussion about what to do if either the source of values or weights is missing in a document or multi-valued. If we can't come with a nice API then I'd rather not implement the feature. I'll mark this issue as discuss so that others chime in.

[lquerel]

Thank for your response.

With this approach, how can you order the result of the weighted average? With my proposal it's done directly by the coordinator node with the following query.

{
  "aggs": {
    "myAggr": {
      "terms": {
        "field": "dimension",
        "order": { "weightedAgv": "desc"}
      },  
      "aggs": {
          "weightedAvg": {"avg" : { "script" : "[value: doc['latency'].value, weight: doc['transaction'].value]" }}
       }
    }
}

Except if I'm wrong, but with your approach I need to sort client side and means potentially loading a lot of data. Right ?
IMO it's a second good reason (with kibana) to support this feature natively. I will be very happy to help if needed.

Multi-valued values/weights are implemented in my pull request as follow:

Standard average

{ "avg" : { "script" : "doc['latency'].value" }} } 

By default the weight is equals to 1.

Weighted average (single-valued)

{ "avg" : { "script" : "[value: doc['latency'].value, weight: doc['transaction'].value]" }}} 

Weighted average (multi-valued)

{ "avg" : { "script" : "[values: doc['latency'].value, weights: doc['transaction'].value]" }}} 

Weights is optional, if not provided then by default weight=1 for each value.
If weights.size() < values.size() then by default weight=1 for each value without corresponding weight.
Otherwise we use the corresponding weight.

General remark: when the weight field is missing in the original doc then by default weight=1.

For the second approach exposed in my initial proposal, the idea is the same. In the current API the property 'field' is used to identify the value or the list of values used to compute the average. I suggest to introduce an optional property 'weight' to identify the value of the list of values used to determine all weights.

Is it good/general enough to implement the feature? Thanks in advance for your response.

[elephantum]

+1 for any reasonable solution. Weighted average is a common case in any business-related dashboard.

[jpountz]

Thinking about it a bit more: the only reason to not do it on client side is to be able to order by sub aggregation. However sorting by sub aggregation has the drawback of making counts very inaccurate, I am actually more thinking of deprecating the ability to sort by sub aggregations and by asending count because of that. So I don't think we should add a weighted average aggregation (or add weighting support to the avg agg) just to be able to sort by it.

If weights.size() < values.size() then by default weight=1 for each value without corresponding weight.

This won't work since elasticsearch does not maintain the order of values in fielddata: they always come in ascending order. So if your original document has {value: [5, 1], weight: [2,3]}, we will actually use a weight of 2 for 1 and 3 for 5.

[jpountz]

We just discussed it in Fixit Friday. We like the feature but would like to verify that there is traction for this feature before implementating it. Also we need to figure out a clear API, especially about what happens with multi-valued fields or missing values.