BigQuery 用户定义的聚合函数? [英] BigQuery User Defined Aggregation Function?

问题描述

我知道我可以定义一个用户定义的函数 以执行一些自定义计算.我也知道我可以使用开箱即用"聚合函数在使用 GROUP BY 子句时将一组值减少为单个值.

I know I can define a User Defined Function in order to perform some custom calculation. I also know I can use the 'out-of-the-box' aggregation functions to reduce a collection of values to a single value when using a GROUP BY clause.

是否可以定义自定义的用户定义的聚合函数以与 GROUP BY 子句一起使用?

Is it possible to define a custom user-defined, Aggregation Function to use with a GROUP BY clause?

推荐答案

事实证明这是可能的(只要我们寻求聚合的组在内存中具有合理的大小)，只需一点胶水" -即ARRAY_AGG函数

Turns out that this IS possible (as long as the groups we seek to aggregate are of a reasonable size in memory) with a little bit of 'glue' - namely the ARRAY_AGG function

步骤如下:

1. 使用 ARRAY 类型的输入参数创建 UDF，其中 T 是您要聚合的值的类型.
2. 在带有GROUP BY 子句的查询中使用ARRAY_AGG 函数生成一个T 数组并传递到您的UDF.

1. Create a UDF with an input parameter of type ARRAY<T> where T is the type of value you want to aggregate.
2. Use the ARRAY_AGG function in the query with the GROUP BY clause to generate an array of T and pass into your UDF.

举个具体的例子:

CREATE TEMP FUNCTION aggregate_fruits(fruits ARRAY<STRING>)
RETURNS STRING
LANGUAGE js AS """
return "my fruit bag contains these items: " + fruits.join(",");
""";

WITH fruits AS
(SELECT "apple" AS fruit
UNION ALL SELECT "pear" AS fruit
UNION ALL SELECT "banana" AS fruit)

SELECT aggregate_fruits(ARRAY_AGG(fruit))
FROM fruits

这篇关于BigQuery 用户定义的聚合函数?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！