本文介绍了BigQuery用户定义的聚合函数?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
创建一个带有输入的UDF参数类型 ARRAY 其中 T 是您要聚合的值的类型。 b $ b 使用 GROUP BY 子句在查询中使用 ARRAY_AGG 函数生成一个 T 并传入您的UDF。 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.
我知道我可以定义一个用户定义函数以执行一些自定义计算。我也知道我可以使用'开箱即用'汇总函数,当使用 GROUP BY 子句时,将值集合减少为单个值。
是否可以定义自定义用户定义的聚合函数以便与 GROUP BY 子句一起使用?
解决方案原来,这只是一小块'胶水' - 也就是 ARRAY_AGG 功能
步骤如下:
作为具体的例子:
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:
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用户定义的聚合函数?
发布评论