CODEWITHSUNDEEP
google-bigqueryuser-defined-functions
kee
kee

Reputation: 11629

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?

Upvotes: 4

Views: 767

Answers (2)

Saqib Ali
Saqib Ali

Reputation: 4448

User Defined Aggregate Functions (UDAF) are now available in Google BigQuery. Here is an example of defining a UDAF to calculate the Geometric Mean of a column of data.

Defining the UDAF:

CREATE TEMP AGGREGATE FUNCTION geometric_mean(
  column_values float64
)
RETURNS float64
AS
(
  EXP(SUM(LN(column_values))/COUNT(column_values))
);

Calling the UDAF

with test_data as (
  SELECT 1 AS col1 
  UNION ALL
  SELECT 3
  UNION ALL
  SELECT 5
)
select geometric_mean(col1) from test_data;

More info: https://qosf.com/UDAF-in-google-bigquery.html

Upvotes: 0

Elliott Brossard
Elliott Brossard

Reputation: 33765

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.

Upvotes: 1

Related Questions