I am trying to use the R UDF to create a cross tab and have given the R function and the Vertica commands below:
Code: Select all
generateCrosstab <- function(x)
{
var1 = x[,1]
var2 = x[,2]
crossTab <- as.data.frame.matrix(table(var1,var2))
crossTab
}
crossTab_factory <- function()
{
inlist <- rep("int", 2)
outlist <- c("int", "int")
list(name=generateCrosstab, udxtype=c("transform"), intype=inlist, outtype=outlist)
}
DROP LIBRARY crossTab CASCADE;
CREATE LIBRARY crossTab AS '/home/dbadmin/test/crossTab.r' LANGUAGE 'R';
CREATE TRANSFORM FUNCTION generateCrosstab AS NAME 'crossTab_factory' LIBRARY crossTab ;
SELECT generateCrosstab(column1, column2) OVER() FROM testdb.table;
Code: Select all
col0 | col1
--------+--------
466881 | 655735
21047 | 28336
(2 rows)
Code: Select all
setosa versicolor virginica
setosa 50 0 0
versicolor 0 50 0
virginica 0 0 50
Ravi