CODEWITHSUNDEEP
sqlgoogle-bigqueryuser-defined-functions
redarah
redarah

Reputation: 162

Can you have an aggregate function within a UDF in BigQuery SQL?

When I try to use a UDF that has an aggregate function, I get the following error:

Aggregate function APPROX_QUANTILES not allowed in templated SQL function call

Here's an example function:

CREATE OR REPLACE FUNCTION my_function(x ANY TYPE) AS (
APPROX_QUANTILES(x, 100)[OFFSET(25)]
);

Is there any way to include an aggregate function (in my case, APPROX_QUANTILES), within a UDF in BigQuery?

Upvotes: 0

Views: 457

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 172974

Try below instead

CREATE OR REPLACE FUNCTION my_function(x ANY TYPE) AS ((
  SELECT APPROX_QUANTILES(y, 100)[OFFSET(25)]
  FROM UNNEST(x) y
));

Upvotes: 2

Related Questions