User Defined Analytic Functions with framing windows using RANGE?
Hello
I want to implement a UDAnFs, in the documentation it specifies that they do not support framing windows using ROWS. ( https://my.vertica.com/docs/7.0.x/HTML/Content/Authoring/ProgrammersGuide/UserDefinedFunctions/UserDefinedAnalyticFunctions/DeployingAndUsingUserDefinedAnalyticFunctions.htm)
It does not specify that cannot do it with the option RANGE.
I want to have an UDAnFs that does some operations with a frame window(or sliding window) of a table. I want to do a query like this:
SELECT id, signal, position,
my_udf(value) OVER (PARTITION BY id, signal ORDER BY position RANGE BETWEEN 90 PRECEDING AND 89 FOLLOWING)
FROM myTable;
Im using the Range but still the error is: "windowing not supported for User Defined Analytic functions".
In another section of the documentation it specifies the following about UDAnFs:
"Require the OVER() clause. However, depending on the function, the window_frame_clause and window_order_clause might not apply. For example, when used with analytic aggregate functions like SUM(x), you can use the OVER() clause without supplying any of the windowing clauses; in this case, the aggregate returns the same aggregated value for each row of the result set."
So it's not clear what are the options to use the windowing option.
Does anyone knows or has encouted this problem?? or there is a workaround?
Regards.
Comments
The user defined analytic functions do not support any form of window clauses in the over(). The only thing you can specify there are partition by and order by.
Hope this helps,
Eugenia