For each agg function with input param as <T>, Add an equivalent agg function with input param as array<T> about presto

[rschlussel]

I think this would be semantically very problematic. Some aggregation functions can take arrays as input and would aggregate on the array in the usual way. If I sawapprox_distinct(array_column), I would expect it to give me the number of distinct arrays, and not the number of distinct elements across all arrays.

CROSS JOIN UNNEST can only be done with one array column at a time. What if i want to SUM two such columns in a single pass.
You can unnest multiple array columns at a time. see https://prestodb.io/docs/current/sql/select.html#unnest.

For some of the cases you described above, if you don't want to unnest, you can consider first using an array_function that does part of the aggregation for you on each array row, and then doing an aggregation on top of it. For example:
If you are trying to get the number of distinct elements across all arrays, you could unnest and do an approx_distinct as you described. Alternatively, you could do cardinality(set_union(array_column)).

If you are trying to get the sum across all arrays, you could unnest and sum as you described, or you could do sum(array_sum(array_column)

also cc: @kaikalur