Can a UDAnF be called multiple times in a SELECT list?
Hello,
We have a need for something like the User Defined Transform Function (UDTF) group_concat that can be found in the Vertica-Extension-Packages repository.
However, we want to be able to call that function on mutiple columns from the same table.
e.g., we want to do:
select
id,
group_concat(colX) over (partition by id order by mytime),
group concat(colY) over (partition by id order by mytime)
from my_example_table;
Since we can't call the UDTF multiple times in the same select list, we are thinking of reimplementing group_concat as a user defined analytic function (UDAnF). So, we have two questions:
1. Can a UDAnF be called multiple times in a select list? The documentation for UDTF is clear but it is not for UDAnF.
2. Assuming yes to question #1, is there a good example of a UDAnF that you can point us to that takes an expression and returns a single value per partition group (like first_value or lag)? The example in the documentation is for Rank and doesn't illustrate some of the complexities we expect to encounter.
Thanks in advance for any help.
We have a need for something like the User Defined Transform Function (UDTF) group_concat that can be found in the Vertica-Extension-Packages repository.
However, we want to be able to call that function on mutiple columns from the same table.
e.g., we want to do:
select
id,
group_concat(colX) over (partition by id order by mytime),
group concat(colY) over (partition by id order by mytime)
from my_example_table;
Since we can't call the UDTF multiple times in the same select list, we are thinking of reimplementing group_concat as a user defined analytic function (UDAnF). So, we have two questions:
1. Can a UDAnF be called multiple times in a select list? The documentation for UDTF is clear but it is not for UDAnF.
2. Assuming yes to question #1, is there a good example of a UDAnF that you can point us to that takes an expression and returns a single value per partition group (like first_value or lag)? The example in the documentation is for Rank and doesn't illustrate some of the complexities we expect to encounter.
Thanks in advance for any help.
0
Comments
The short answer is "Yes". This is certainly one of the limitations of UDT's, and one of the limitations that UDAnF's address.
There is an Analytics Lag implementation installed as part of our SDK examples, in /opt/vertica/examples/AnalyticFunctions/Lag.cpp on any machine with Vertica installed. If you are looking for specific bits of nuance beyond that, feel free to ask here.
As an alternative, have you considered a UDAgg function? They are even more flexible. If you want an example, there is actually a group_concat example in particular up on Vertica's github site:
https://github.com/vertica/Vertica-Extension-Packages/blob/master/strings_package/src/GroupConcat.cpp
(Go up a few levels or just check out the whole repository to see the corresponding makefile. As always, patches welcome!)
If you don't have the SDK examples or headers installed locally, they are bundled with the Vertica Community Edition, available from my.vertica.com. We recommend that all initial UDx development be done locally -- we find that the initial development cycle is much faster when all you need is your laptop or workstation, and we hope that the CE makes it easy to set up a quick basic environment to try out and test your code.
Adam
Have look at Concatenate UDAF in vertica examples which comes with vertica installation
that's the mysql equivalent. you can just directly install it.
more /opt/vertica/sdk/examples/AggregateFunctions/Concatenate.cpp
-- Shell comppile
cd /opt/vertica/sdk/examples/AggregateFunctions/
g++ -D HAVE_LONG_INT_64 -I /opt/vertica/sdk/include -Wall -shared -Wno-unused-value \
-fPIC -o Concatenate.so Concatenate.cpp /opt/vertica/sdk/include/Vertica.cpp
-- Create LIBRARY
CREATE LIBRARY AggregateFunctionsConcatenate AS '/opt/vertica/sdk/examples/AggregateFunctions/Concatenate.so';
CREATE AGGREGATE FUNCTION agg_group_concat AS LANGUAGE 'C++' NAME 'ConcatenateFactory' LIBRARY AggregateFunctionsConcatenate;