Hi Timo, Thank you for the quick response.
Neither COLLECT nor LISTAGG work because they only accept one column. I am trying to collect all the rows and columns into one object. Like a List<Row> for example. Later, I need make calculations upon all the rows that were just collected within a window. Maybe I need to use a subquery, ie, SELECT FROM (SELECT FROM)? > On Jan 5, 2021, at 6:10 AM, Timo Walther <twal...@apache.org> wrote: > > Hi Marco, > > nesting aggregated functions is not allowed in SQL. The exception could be > improved though. I guess the planner searches for a scalar function called > `MyUDTAGG` in your example and cannot find one. > > Maybe the built-in function `COLLECT` or `LISTAGG`is what you are looking for? > > https://ci.apache.org/projects/flink/flink-docs-release-1.12/dev/table/functions/systemFunctions.html > > Regards, > Timo > > > On 05.01.21 14:45, Marco Villalobos wrote: >> I am trying to use User defined Table Aggregate function directly in the >> SQL so that I could combine all the rows collected in a window into one >> object. >> GIVEN a User defined Table Aggregate function >> public class MyUDTAGG extends >> TableAggregateFunction<PurchaseWindow,PurchaseWindow> { >> public PurchaseWindow createAccumulator() { >> return new PurchaseWindow(); >> } >> public void accumulate(PurchaseWindow acc, String name, double cost) { >> acc.add(name, cost); >> } >> public void emitValue(PurchaseWindow acc, Collector<PurchaseWindow> >> out) { >> out.collect(acc); >> } >> } >> THAT it is registered as >> StreamTableEnvironment tEnv = ... >> tEnv.registerFunction("MyUDTAGG", new MyUDTAGG()); >> THEN is it possible to call it in an SQL query in this manner? >> SELECT MyUDTAGG(name, SUM(cost)) AS purchase_window >> FROM purchases >> GROUP BY TUMBLE(proactive, INTERVAL '1' DAY), name >> I am receiving an SQL validation error, "No match found for function >> signature ...". >> What am I doing wrong, or is there a better way to do this? >
