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