I did not found any example of returning new additional attributes in case of window function from PL/R documentation.
-- Input data: *************************************************************************************
CREATE TABLE ts_der_sample (custid int, month int, usage numeric, billing numeric);
INSERT INTO ts_der_sample VALUES (1,1,52,26);
INSERT INTO ts_der_sample VALUES (1,2,54,54);
INSERT INTO ts_der_sample VALUES (1,3,58,58);
INSERT INTO ts_der_sample VALUES (1,4,47,47);
INSERT INTO ts_der_sample VALUES (1,5,38,38);
INSERT INTO ts_der_sample VALUES (1,6,22,22);
INSERT INTO ts_der_sample VALUES (2,1,22,44);
INSERT INTO ts_der_sample VALUES (2,2,24,24);
INSERT INTO ts_der_sample VALUES (2,3,30,44);
INSERT INTO ts_der_sample VALUES (2,4,28,28);
INSERT INTO ts_der_sample VALUES (2,5,31,44);
INSERT INTO ts_der_sample VALUES (2,6,30,44);
INSERT INTO ts_der_sample VALUES (3,1,22,44);
INSERT INTO ts_der_sample VALUES (3,2,24,24);
INSERT INTO ts_der_sample VALUES (3,3,NULL,44);
INSERT INTO ts_der_sample VALUES (3,4,28,28);
INSERT INTO ts_der_sample VALUES (3,5,31,NULL);
INSERT INTO ts_der_sample VALUES (3,6,30,44);
-- PL/R function which gives output as array: ********************************************************
CREATE OR REPLACE FUNCTION TS_DER_01_31(numeric,character)
RETURNS numeric[] AS
$BODY$
# ///////////////////////////////////////////////////////////////////////////
# Type of derivate: Static aggregation
# Derivate: Largest mean shift between two consecutive windows
# Author: Jüri Kuusik
# Last modified: 13.03.2020
# ///////////////////////////////////////////////////////////////////////////
x <- farg1
var_name <- arg2
df <- data.frame(a=as.numeric(0), b=as.integer(0))
vecResult <- base::tryCatch(expr=as.numeric(feasts::shift_level_max(x)), error=function(e){c(NA,NA)})
df$a[1] <- vecResult[1]
df$b[1] <- vecResult[2]
names(df) <- paste0(c('TS_DER_01_30_size_','TS_DER_01_30_time_index_'),var_name)
return(df)
$BODY$
LANGUAGE 'plr' WINDOW;
-- test function:
select *,
TS_DER_01_31(usage,'usage') OVER w AS TS_DER_01_31_usage
from ts_der_sample where custid < 3
WINDOW w AS (PARTITION BY custid ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
select *,
TS_DER_01_31(usage,'usage') OVER w AS TS_DER_01_31_usage
from ts_der_sample where custid = 3
WINDOW w AS (PARTITION BY custid ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
-- Non working PL/R function for having output as additional attributes:
CREATE OR REPLACE FUNCTION TS_DER_01_31(numeric)
RETURNS table(TS_DER_01_31_size numeric, TS_DER_01_31_time_index numeric) AS
$BODY$
# ///////////////////////////////////////////////////////////////////////////
# Type of derivate: Static aggregation
# Derivate: Largest mean shift between two consecutive windows
# Author: Jüri Kuusik
# Last modified: 16.03.2020
# ///////////////////////////////////////////////////////////////////////////
x <- farg1
df <- data.frame(a=as.numeric(0), b=as.integer(0))
vecResult <- base::tryCatch(expr=as.numeric(feasts::shift_level_max(x)), error=function(e){c(NA,NA)})
df$a[1] <- vecResult[1]
df$b[1] <- vecResult[2]
return(df)
$BODY$
LANGUAGE 'plr' WINDOW;
-- test function:
select *,
TS_DER_01_31(usage) OVER w
from ts_der_sample where custid < 3
WINDOW w AS (PARTITION BY custid ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);
select *,
TS_DER_01_31(usage) OVER w
from ts_der_sample where custid = 3
WINDOW w AS (PARTITION BY custid ORDER BY month ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);