Presto - get sum of array elements

user_11019977 0
Viewed 2934

I'm using Athena and trying to get a sum of array elements which are double but the reduce function seems to only work on integers:

SELECT reduce(ARRAY [5.0, 20.0, 50.4], 0, (s, x) -> s + x, s -> s);

Will throw an error:

Unexpected parameters (array(double), integer, com.facebook.presto.sql.analyzer.TypeSignatureProvider@762f0fa7, com.facebook.presto.sql.analyzer.TypeSignatureProvider@29dfe267) for function reduce. Expected: reduce(array(T), S, function(S,T,S), function(S,R)) T, S, R

Is there a way to do it?

presto amazon-athena
2 Answers

This is a know bug in Presto. You can track https://github.com/prestosql/presto/issues/2760.

As a workaround, you can cast your array(decimal(..)) to either array(decimal(38,..)) or array(double):

presto> SELECT reduce(cast(ARRAY[5.0, 20.0, 50.4] as array(decimal(38,5))), 0, (s, x) -> s + x, s -> s);
  _col0
----------
 75.40000

presto> SELECT reduce(cast(ARRAY[5.0, 20.0, 50.4] as array(double)), 0, (s, x) -> s + x, s -> s);
 _col0
-------
  75.4

Athena is based on an older Presto version (currently .172, 3 years old), and apparently none of the above works in Athena (based on the fact your array is already array(double)).

user_654580

Doesn't look clean but I have found a way to make it work:

SELECT reduce(ARRAY [5.0, 20.0, 50.4],
              CAST(ROW(0.0, 0) AS ROW(sum DOUBLE, count INTEGER)),
              (s, x) -> CAST(ROW(x + s.sum, s.count + 1) AS ROW(sum DOUBLE, count INTEGER)),
              s -> IF(s.count = 0, NULL, s.sum));
user_110199770
Related