Create a node similar to a Codd extend relational operator (add derived columns).

extend_se(source, assignments, ..., partitionby = NULL, orderby = NULL,
  reverse = NULL, display_form = NULL, env = parent.frame())

Arguments

source

source to select from.

assignments

new column assignment expressions.

...

force later arguments to bind by name

partitionby

partitioning (window function) terms.

orderby

ordering (in window function) terms.

reverse

reverse ordering (in window function) terms.

display_form

chacter presentation form

env

environment to look for values in.

Value

extend node.

Details

Partitionby and orderby can only be used with a database that supports window-functions (such as PostgreSQL, Spark and so on).

Examples

if (requireNamespace("DBI", quietly = TRUE) && requireNamespace("RSQLite", quietly = TRUE)) { my_db <- DBI::dbConnect(RSQLite::SQLite(), ":memory:") d <- rq_copy_to(my_db, 'd', data.frame(AUC = 0.6, R2 = 0.2)) optree <- extend_se(d, c("v" %:=% "AUC + R2", "x" %:=% "pmax(AUC,v)")) cat(format(optree)) sql <- to_sql(optree, my_db) cat(sql) print(DBI::dbGetQuery(my_db, sql)) # SQLite can not run the following query optree2 <- extend_se(d, "v" %:=% "rank()", partitionby = "AUC", orderby = "R2") sql2 <- to_sql(optree2, my_db) cat(sql2) DBI::dbDisconnect(my_db) }
#> table(`d`; #> AUC, #> R2) %.>% #> extend(., #> v := AUC + R2) %.>% #> extend(., #> x := pmax(AUC, v)) #> SELECT #> `AUC`, #> `R2`, #> `v`, #> ( CASE WHEN ( `AUC` ) IS NULL THEN ( `v` ) WHEN ( `v` ) IS NULL THEN ( `AUC` ) WHEN ( `AUC` ) >= ( `v` ) THEN ( `AUC` ) ELSE ( `v` ) END ) AS `x` #> FROM ( #> SELECT #> `AUC`, #> `R2`, #> `AUC` + `R2` AS `v` #> FROM ( #> SELECT #> `AUC`, #> `R2` #> FROM #> `d` #> ) tsql_22643126905802424410_0000000000 #> ) tsql_22643126905802424410_0000000001 #> AUC R2 v x #> 1 0.6 0.2 0.8 0.8 #> SELECT #> `AUC`, #> `R2`, #> rank ( ) OVER ( PARTITION BY `AUC` ORDER BY `R2` ) AS `v` #> FROM ( #> SELECT #> `AUC`, #> `R2` #> FROM #> `d` #> ) tsql_07951046575409495350_0000000000