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
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