Custom multi-argument aggregate functions

Aniket_Schneide

I have a custom aggregate metric that is computed based on two different columns. It can be computed in SQL, but it is moderately complex. Something similar to: 100 * SUM(CASE WHEN c1 IN (0,1,2) THEN 1 ELSE 0) / SUM(CASE WHEN c1 = 0 AND c2 = 1 THEN 1 ELSE 0) I would like to abstract this into a UDF so that we can tweak the formula periodically and not have to update every query separately. Is there a clean way to do this, given that UDAFs can only take a single argument?

[Deleted User]

Hi, What you need is a User-Define SQL Function (https://my.vertica.com/docs/6.1.x/HTML/index.htm#15038.htm) and you can pass more than 1 argument. Look this simple example. dbadmin=> CREATE FUNCTION add2int(x int, y int) return int dbadmin-> as begin dbadmin-> return (x + y ) ; dbadmin-> end; CREATE FUNCTION dbadmin=> create table test(c1 int, c2 int) ; CREATE TABLE dbadmin=> copy test from stdin; Enter data to be copied followed by a newline. End with a backslash and a period on a line by itself. >> 1|2 >> 3|5 >> \. dbadmin=> select add2int(c1,c2) from test; add2int -------------- 3 8 (2 rows) Hope this helps, Eugenia

Aniket_Schneide

Hi Eugenia! I don't believe user-defined SQL functions can use aggregate functions: dbadmin=> create function mysum(x int) return int dbadmin-> as begin dbadmin-> return sum(x); dbadmin-> end; ROLLBACK 4257: Only simple "RETURN expression" is allowed

[Deleted User]

Hi all, Eugenia may have more ideas; I don't mean to interrupt. But, Aniket, you could also take a look at our User-Defined Transform API. It is in some sense our most-general SDK API; it requires a more-cumbersome SQL syntax, but it lets you be very expressive with the types of functions that you write. It's our generic catch-all API; with enough effort you can implement just about anything :-) Also, the User-Defined Analytics API is almost as powerful, and might be a better fit for your particular use case here. Adam