Division between two sum fields

[jackcloudmini]

@shi-yuan How to divide between tow sum fields? "select sum(a)/sum(b)" It doesn't work! If supported，how to order by division result?

[shi-yuan]

Yeah，not supported，but you could use scripted_metric

[jackcloudmini]

@shi-yuan Could you give me a example for using scripted_metric?

[shi-yuan]

What't your whole sql ?

[jackcloudmini]

@shi-yuan select sum(band) as band, sum(time) as time, band/time from test

[shi-yuan]

Additional，you could use Bucket Script Aggregation：

{
...
"aggs": {
  "sum_1": {
    "sum": {
      "field": "flag_barisolve"
    }
  },
  "sum_2": {
    "sum": {
      "field": "flag_anagrafe"
    }
  },
  "division": {
    "bucket_script": {
      "buckets_path": {
        "my_var1": "sum_1",
        "my_var2": "sum_2"
      },
      "script": "params.my_var1 / params.my_var2"
    }
  }
}

[shi-yuan]

SELECT scripted_metric(
'init_script' = 'params._agg["bands"]=[];params._agg["times"]=[];',
'map_script'='params._agg["bands"].add(doc["band"].value);params._agg["times"].add(doc["time"].value);',
'combine_script'='return [params._agg["bands"].stream().mapToLong(i -> i).sum(),params._agg["times"].stream().mapToLong(i -> i).sum()];',
'reduce_script'='def band_sum=0,time_sum=0;for(item in params._aggs){ band_sum+=item[0];time_sum+=item[1];} return [band_sum,time_sum,(band_sum*1.0/time_sum)];'
) FROM test

[kinshuk4]

Additional，you could use Bucket Script Aggregation：

{
...
"aggs": {
  "sum_1": {
    "sum": {
      "field": "flag_barisolve"
    }
  },
  "sum_2": {
    "sum": {
      "field": "flag_anagrafe"
    }
  },
  "division": {
    "bucket_script": {
      "buckets_path": {
        "my_var1": "sum_1",
        "my_var2": "sum_2"
      },
      "script": "params.my_var1 / params.my_var2"
    }
  }
}

@shi-yuan you will get following error: pipeline aggregation named [division] of type [bucket_script]. Only sibling pipeline aggregations are allowed at the top level.

[shi-yuan]

{
  "query": {
    "match_all": {}
  },
  "size": 0,
  "aggs": {
    "all": {
      "terms": {
        "script": "1"
      },
      "aggs": {
        "sum_1": {
          "sum": {
            "field": "flag_barisolve"
          }
        },
        "sum_2": {
          "sum": {
            "field": "flag_anagrafe"
          }
        },
        "division": {
          "bucket_script": {
            "buckets_path": {
              "my_var1": "sum_1",
              "my_var2": "sum_2"
            },
            "script": "params.my_var1 / params.my_var2"
          }
        }
      }
    }
  }
}

[anjanarajagopal]

Does bucket_script aggregation automatically check for 0/0 division or is that something you would have to manage on your own?

[shi-yuan]

if 0/0, division.value is null