Skip to content

UDAF-in-google-bigquery.md

Latest commit

 

History

History
34 lines (25 loc) · 661 Bytes

UDAF-in-google-bigquery.md

File metadata and controls

34 lines (25 loc) · 661 Bytes

Bigquery aggregate functions

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

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

Calling the UDAF

select geometric_mean(col1) from test_data;