Order by scripted_metric sub aggregation

eryabitskiy commented 9 years ago

Since there is a new Scripted metric aggregation (scripted_metric) in 1.4, it is possible to do a lot of amazing stuff. For example it is possible to implement Weighted Average aggregation, which we were missing before.

Now we are really missing a possibility to sort by scripted_metric results.

Live example:

We calculate weightedAvgVis with scripted_metric and want to get ids with TOP 5 values of weightedAvgVis. Since script returns double, it looks logically possible.

{  
   "from":0,
   "size":0,
   "query":{  
      "match_all":{   }
   },
   "aggregations":{  
      "idNodes":{  
         "terms":{  
            "field":"id",
            "size":5,
            "order":{  
               "weightedAvgVis":"asc"
            }
         },
         "aggregations":{  
            "weightedAvgVis":{  
               "scripted_metric":{  
                  "init_script":"_agg['weightedSum'] = 0d; _agg['countSum'] = 0L;",
                  "map_script":"_agg['weightedSum'] = _agg.weightedSum + _source['avgVis'] * _source['count']; _agg['countSum'] = _agg.countSum + _source['count'];",
                  "reduce_script":"weightedSum = 0d; countSum = 0L; for(a in _aggs) {weightedSum += a.weightedSum; countSum += a.countSum;};if(countSum == 0L) {return 0d;} else {return weightedSum / countSum}"
               }
            }
         }
      }
   }
}

colings86 commented 9 years ago

@eryabitskiy this is something that I have been thinking about but requires a few outstanding issues to be resolved first. Specifically these are https://github.com/elasticsearch/elasticsearch/pull/8421 and https://github.com/elasticsearch/elasticsearch/issues/8434. These would allow us to specify much more powerful order paths and the getProperty method on the scripted metric aggregation could be used to retrieve arbitrary properties of the scripts results.

snikch commented 9 years ago

Hi there, I'm not sure if this is appropriate but I thought you may want to gauge interest. We're very keen to see this as well. Currently we end up doing a lot of sorting on oversized resultsets in Go, whereas being able to sort on scripted metrics would save us this hassle.

vb3 commented 9 years ago

+1 This would be great if solved!

felipegs commented 9 years ago

+1

concordiadiscors commented 9 years ago

+1

rmruano commented 9 years ago

:+1: sort on scripted metrics aggregations would be a killer feature. In the meantime we're also doing client-side sorting on oversized results.

hendrik-schumacher commented 9 years ago

+1

aaneja commented 9 years ago

+1

edigu commented 9 years ago

+1

brianstebar2 commented 9 years ago

+1

tekwiz commented 9 years ago

+1 Aside from the other use-cases mentioned here, this feature would give the Kibana product some major strength, particularly for rapid prototyping.

genme commented 9 years ago

+1

adimasuhid commented 8 years ago

+1

iantruslove commented 8 years ago

(hopping on the "+1 to this feature" bandwagon)

mpereira commented 8 years ago

+1

vhiroki commented 8 years ago

+1

big-striped-cat commented 8 years ago

+1

lquerel commented 8 years ago

+1

bkj commented 8 years ago

+1

mariomara commented 8 years ago

+1

ssickles commented 8 years ago

+1

antmat commented 8 years ago

+1

vasiliy-kiryukhin commented 8 years ago

+1

lquerel commented 8 years ago

Regarding the computation of weighted average I sent a pull request to extend the current avg aggregation in order to support this kind of average natively without scripted metric.

Here the issue created on github: https://github.com/elastic/elasticsearch/issues/15731

Here the pull request (only the first approach is implemented): https://github.com/elastic/elasticsearch/pull/15781

lquerel commented 8 years ago

Regarding the support of the order clause for scripted metric I sent a pull request to support that.

https://github.com/elastic/elasticsearch/pull/15718