aggregate function
Applies to: 
Databricks SQL Databricks Runtime
Aggregates elements in an array using a custom aggregator. This function is a synonym for reduce function.
Syntax
aggregate(expr, start, merge [, finish])
Arguments
expr: An ARRAY expression.start: An initial value of any type.merge: A lambda function used to aggregate the current element.finish: An optional lambda function used to finalize the aggregation.
Returns
The result type matches the result type of the finish lambda function if exists or start.
Applies an expression to an initial state and all elements in the array, and reduces this to a single state. The final state is converted into the final result by applying a finish function.
The merge function takes two parameters. The first being the accumulator, the second the element to be aggregated.
The accumulator and the result must be of the type of start.
The optional finish function takes one parameter and returns the final result.
Examples
> SELECT aggregate(array(1, 2, 3), 0, (acc, x) -> acc + x);
6
> SELECT aggregate(array(1, 2, 3), 0, (acc, x) -> acc + x, acc -> acc * 10);
60
> SELECT aggregate(array(1, 2, 3, 4),
named_struct('sum', 0, 'cnt', 0),
(acc, x) -> named_struct('sum', acc.sum + x, 'cnt', acc.cnt + 1),
acc -> acc.sum / acc.cnt) AS avg
2.5