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() )
| 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. |
extend node.
Partitionby and orderby can only be used with a database that supports window-functions (such as PostgreSQL, Spark and so on).
Note: if any window/aggregation functions are present then at least one of partitionby or orderby must be non empty. For this purpose partitionby=1 is allowed and means "single partition on the constant 1."
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) }#> mk_td("d", c( #> "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_26662150363605981880_0000000000 #> ) tsql_26662150363605981880_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_02298472059817593826_0000000000