Proposal

For each aggregate function with input param as (T), Add an equivalent agg function with input param as array(T)

Why NOT use `CROSS JOIN UNNEST` and then do a SUM?

Existing work around in SQL, make the query very inefficient for large tables. 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.

Expected Behavior or Use Case

Let us take the example of agg function APPROX_DISTINCT(column) Add an overloaded agg function APPROX_DISTINCT(Array_column). Meaning I should be able to run a APPROX_DISTINCT on a column of type ARRAY(T)

It does not work but if it worked something on the lines of APPROX_DISTINCT(UNNEST(array_column)). i.e. COUNT(UNNEST(array_column)) would be same as SUM(CARDINALITY(array_column))

Apply the same to every single agg function e.g. AVG, SUM....

Presto Component, Service, or Connector

Presto inbuilt Aggregate functions

Possible Implementation

Overloaded versions of the function with a different parameter type

Example Screenshots (if appropriate):

Context

https://github.com/trinodb/trino/issues/22445

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

prestodb / presto

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