BigQuery 用户定义的聚合函数? [英] BigQuery User Defined Aggregation Function?
本文介绍了BigQuery 用户定义的聚合函数?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我知道我可以定义一个用户定义的函数 以执行一些自定义计算.我也知道我可以使用开箱即用"聚合函数在使用 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
步骤如下:
- 使用
ARRAY
类型的输入参数创建 UDF,其中T
是您要聚合的值的类型. - 在带有
GROUP BY
子句的查询中使用ARRAY_AGG
函数生成一个T
数组并传递到您的UDF.莉>
- Create a UDF with an input parameter of type
ARRAY<T>
whereT
is the type of value you want to aggregate. - Use the
ARRAY_AGG
function in the query with theGROUP BY
clause to generate an array ofT
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屋!
查看全文