Search code examples
google-bigqueryuser-defined-functions

BigQuery: does it support UDAF?

I guess BigQuery doesn't support UDAF since all I can find was about UDF. Does BigQuery support UDAF? If not, is there any way to run a UDF on aggregated result somehow? Maybe by using ARRAY_AGG?

Solution

  • Yes, you can use ARRAY_AGG to model a UDAF. As an example, you can do:

    CREATE TEMP FUNCTION AggX(arr ARRAY<INT64>) AS (
  (SELECT SUM(x * x) FROM UNNEST(arr) AS x)
);

SELECT id, AggX(ARRAY_AGG(x)) AS result
FROM dataset.table
GROUP BY id

    Depending on what sort of logic you want to implement, you can even have the function take an ARRAY<STRUCT<...>> with whatever columns you want the function to operate on, and use ARRAY_AGG(STRUCT(...)) to build the input to it.