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

问题描述

我知道我可以定义一个用户定义函数以执行一些自定义计算。我也知道我可以使用'开箱即用'汇总函数，当使用 GROUP BY 子句时，将值集合减少为单个值。

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

解决方案

原来，这只是一小块'胶水' - 也就是 ARRAY_AGG 功能

步骤如下：

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

作为具体的例子：

  CREATE TEMP FUNCTION aggregate_fruits（fruits ARRAY< STRING> ）
 RETURNS STRING 
 LANGUAGE js AS
 return我的水果袋包含这些项目：+ fruits.join（，）; 
; 
 
 WITH水果AS 
（SELECTapple水果
 UNION ALL SELECT梨水果
 UNION ALL SELECT bananaAS水果）
 
 SELECT aggregate_fruits（ARRAY_AGG（水果））
 FROM fruits; 
  

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.

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

解决方案

Turns out that this IS possible with a tiny little bit of 'glue' - namely the ARRAY_AGG function

The steps are as follows:

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.

As a concrete example:

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屋！