At the moment, aggregations/groupings like MAX, AVG, MIN are able to deal with one field as parameter, but things like AVG((salary + 10)/experience) are also possible in an AVG aggregation in Elasticsearch through the use of a script.
If one tries to use AVG with something more than a simple field as parameter, the following is being returned back:
sql> select AVG(salary + 1) from test_emp;
Server error [Server encountered an error [Does not know how to convert argument Add[] for function Avg[]]. [SqlIllegalArgumentException[Does not know how to convert argu
ment Add[] for function Avg[]]
at org.elasticsearch.xpack.sql.planner.QueryTranslator.field(QueryTranslator.java:438)
at org.elasticsearch.xpack.sql.planner.QueryTranslator$Avgs.toAgg(QueryTranslator.java:799)
at org.elasticsearch.xpack.sql.planner.QueryTranslator$Avgs.toAgg(QueryTranslator.java:795)
at org.elasticsearch.xpack.sql.planner.QueryTranslator$SingleValueAggTranslator.asAgg(QueryTranslator.java:883)
In general aggregations cannot be applied on top of any scalar function, another example:
SELECT gender AS g, SUM(ABS(emp_no)) AS "sum"
FROM test_emp
GROUP BY 1;
This was reported via our forum here.
At the moment, aggregations/groupings like MAX, AVG, MIN are able to deal with one field as parameter, but things like
AVG((salary + 10)/experience)are also possible in an AVG aggregation in Elasticsearch through the use of a script.If one tries to use
AVGwith something more than a simple field as parameter, the following is being returned back:In general aggregations cannot be applied on top of any scalar function, another example:
This was reported via our forum here.