I'm trying to create a windowing aggregate function using embedded R in monetdb. The function I have used is:
CREATE AGGREGATE r_sw(val double, part varchar(255), endtime timestamp, starttime timestamp) RETURNS double LANGUAGE R {
library(data.table)
library(zoo)
DT=data.table(ag=aggr_group,pa=part,va=val,et=endtime,st=starttime)
setorder(DT,pa,et)
DT[, o:=mapply(function(x,y) DT[(et>=x & pa==y),.N], DT$st, DT$pa)]
as.data.frame(DT[,.(s:=rollapply(va,o,sum), by=pa)]$s)
};
When attempting to select from the function I am getting an error claiming the aggregate doesn't exist:
Error: SELECT: no such operator 'r_sw'
SQLState: 22000
ErrorCode: 0
I think this is an issue with the number of parameters I am passing, and nothing to do with the R code. I have created aggregates with 2 parameters which work perfectly, but 3 or more seems to cause a problem. Is there something else I need to be doing to get this to work?
(EDIT)Steps to reproduce:
CREATE TABLE mytable (myval double, mypart varchar(255), myend timestamp, mystart timestamp);
INSERT INTO mytable VALUES (10,'A','2016-01-01 00:00:00','2016-01-07 00:00:00');
INSERT INTO mytable VALUES (200,'A','2016-01-04 00:00:00','2016-01-12 00:00:00');
SELECT mypart, r_sw(myval,mypart,myend,mystart) from mytable GROUP BY mypart;
