Weighted Average and aggregations

Elastic Stack Elasticsearch
1

Hi,

I did query, aggregations and avg on ES-5.0.0.

GET titubb56k/ihale/_search 
{"size":0,
  "query": {"match": { "UrunNo":  "99" } },
  "aggs":{
      "group_by_tarih":{
        "terms":{
          "field": "AlimTarihi",
          "order":{
            "Birim_Fiyat_ort":"asc"
          }},
          "aggs" : {
            "Birim_Fiyat_ort" : { "avg" : { "field": "BirimFiyat"}}
          }  }  }}

It works true.

Now I want to do weighted average and aggregations.

for example:

my fields:

  1. Adet
  2. BirimFiyat
  3. UrunNo

weigheted average formula:

sum(Adet*BirimFiyat)/sum(Adet).

It is possible? How can I do?

2

Actually I did something:

GET titubb56k/ihale/_search 
{
"size":0,
  "query": {"match": { "UrunNo":  "99" } },
  "aggs":{
      "group_by_tarih":{
        "terms":{ "field": "AlimTarihi",
        "order":{
            "Birim_Fiyat_agirlik_ort":"asc"
          }},
          "aggs" : {
         "Birim_Fiyat_agirlik_ort": {"avg" : { "script" : "[value: doc['BirimFiyat'].value, weight: doc['Adet'].value]" }}
          }  }  }}

but I have error:

{
  "error": {
    "root_cause": [
      {
        "type": "parsing_exception",
        "reason": "Unexpected token VALUE_STRING [script] in [Birim_Fiyat_agirlik_ort].",
        "line": 11,
        "col": 72
      }
    ],
    "type": "parsing_exception",
    "reason": "Unexpected token VALUE_STRING [script] in [Birim_Fiyat_agirlik_ort].",
    "line": 11,
    "col": 72
  },
  "status": 400
}
3

Have you seen the docs for using scripts and the avg aggregation?

https://www.elastic.co/guide/en/elasticsearch/reference/5.0/search-aggregations-metrics-avg-aggregation.html#_script

6

The context of a script is a single document. It is passed the current doc and it is expected to return a value for that single document. It could for example subtract a "startTime" and "endTime" property held on a document to derive a duration value

8

I tried other things:

GET titubb56k/ihale/_search 
{
  "aggs": {
    "myAggr": {
      "terms": {
        "field": "UrunNo",
        "order": { "weightedAvg": "desc"}
      },  
      "aggs": {
          "weightedAvg": { "avg" : { "script" : "[values: doc['BirimFiyat'].value, weights: doc['Adet'].value]" }}
       }    }  }  }

But I have error:

{
  "error": {
    "root_cause": [
      {
        "type": "parsing_exception",
        "reason": "Unexpected token VALUE_STRING [script] in [weightedAvg].",
        "line": 9,
        "col": 49
      }
    ],
    "type": "parsing_exception",
    "reason": "Unexpected token VALUE_STRING [script] in [weightedAvg].",
    "line": 9,
    "col": 49
  },
  "status": 400
}

I don't understand.
Can you help me please?

9

What is the problem? Or Is weighted average possible on ES-5.0.0 ? How it is done if possible?

10

I did.

POST ortalama/avg/_search
{
  "size": 3,
  "aggs": {
    "gunluk_satis": {
      "terms": {
        "field": "IdareKodu"
      },
      "aggs": {
        "pay": {
          "sum": {
            "script": {
              "lang": "expression",
              "inline": "doc['BirimFiyat'] * doc['Adet']"
            }
          }
        },
        "payda": {
          "sum": {
            "script": {
              "lang": "expression",
              "inline": "doc['Adet']"
            }
          }
        },
        "sonuc": {
                    "bucket_script": {
                        "buckets_path": {
                          "Pay": "pay",
                          "Payda": "payda"
                        },
                        "script": "params.Pay / params.Payda"
                    }
                }
      }
    }
  }
}

that worked.

1 Like
11

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