BigQuery用户定义的聚合函数？

本文介绍了BigQuery用户定义的聚合函数？的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

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

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

解决方案

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

步骤如下：

创建一个带有输入的UDF参数类型 ARRAY 其中 T 是您要聚合的值的类型。 b $ b

使用 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:

Create a UDF with an input parameter of type ARRAY<T> where T is the type of value you want to aggregate.

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;