在BigQuery中为窗口聚合模拟UDF的解决方法? [英] Workarounds for emulating UDFs for window aggregates in BigQuery?
问题描述
我正在尝试在BigQuery中编写自定义聚合函数.在PGSQL中,我可以编写用户定义的聚合函数,该函数可以与 over
子句,但我无法为BigQuery编写任何此类聚合函数-是否有可能编写一个函数,该函数接受分区列的整个数组并返回基于进行一些自定义计算?
I'm trying to write custom aggregate functions in BigQuery. In PGSQL, I can write user-defined aggregate functions which can be used with over
clause, but I'm not able to write any such aggregate functions for BigQuery - would it be possible to write a function that takes in an entire array of a column of a partition and return a value based on some custom calculation?
我尝试过的例子:
CREATE OR REPLACE FUNCTION temp_db.temp_func(arr ARRAY<int64>)
RETURNS int64 LANGUAGE js AS """
return arr.length*10 //example calculation
//actual result involves looping over the array and doing few calculations
""";
select s_id, temp_db.temp_func(s_price) over (partition by s_id order by s_date rows 40 preceding) as temp_col
from temp_db.s_table;
这将导致错误:查询错误:函数temp_db.temp_func不支持[6:19]处的OVER子句
现有的聚合函数不足以解决我的问题目的,因此我需要能够对自定义窗口大小执行自定义计算.BigQuery中对此有任何解决方法吗?
The existing aggregate functions are not sufficient for my purpose so I need to be able to execute custom calculation over custom window sizes. Are there any workarounds for this in BigQuery?
推荐答案
CREATE OR REPLACE FUNCTION temp_db.temp_func(arr ARRAY<int64>)
RETURNS int64 LANGUAGE js AS """
return arr.length*10 //example calculation
//actual result involves looping over the array and doing few calculations
""";
select s_id, temp_db.temp_func(ARRAY_AGG(s_price) over (partition by s_id order by s_date rows 40 preceding)) as temp_col
from temp_db.s_table;
这篇关于在BigQuery中为窗口聚合模拟UDF的解决方法?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!