User-Defined Aggregate Function (UDAF) Now in Preview on BigQuery

A User-Defined Aggregate Function (UDAF) in BigQuery is a custom function that allows you to create an aggregate function using an expression containing SQL code. It processes multiple rows of input data to generate a single summarized output value. UDAFs can accept columns to be aggregated and columns that will not be aggregated, returning the result of the calculation as a single value.

To create a SQL User-Defined Function (UDF) in BigQuery, use the following syntax:

CREATE [ OR REPLACE ] [ TEMPORARY | TEMP ] FUNCTION [ IF NOT EXISTS ]
    [[project_name.]dataset_name.]function_name
    ([named_parameter[, ...]])
  [RETURNS data_type]
  AS (sql_expression)
  [OPTIONS (function_option_list)]

• named_parameter consists of param_name and param_type.
• RETURNS data_type is optional for SQL UDFs as BigQuery can infer the result type from the SQL function body.
• OPTIONS allows for additional function options.

To create a JavaScript UDF in BigQuery, use the following syntax:

CREATE [OR REPLACE] [TEMPORARY | TEMP] FUNCTION [IF NOT EXISTS]
    [[project_name.]dataset_name.]function_name
    ([named_parameter[, ...]])
  RETURNS data_type
  [determinism_specifier]
  LANGUAGE js
  [OPTIONS (function_option_list)]
  AS javascript_code

• named_parameter includes param_name and param_type.
• RETURNS data_type is required for JavaScript UDFs.
• determinism_specifier indicates whether the function is DETERMINISTIC or NOT DETERMINISTIC.
• javascript_code is the actual JavaScript code for the function.

The difference between DETERMINISTIC and NOT DETERMINISTIC functions in BigQuery is based on the predictability of the function's return value:

• DETERMINISTIC: The function always returns the same result when passed the same arguments, allowing BigQuery to potentially cache the query result.

• NOT DETERMINISTIC: The function does not always return the same result for the same arguments, meaning the results are not cacheable by BigQuery.