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

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

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

Arguments

source

source to select from.

...

new column assignment expressions.

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).

Supports bquote() .()-style name abstraction with the extenson that - promotes strings to names (please see here: https://github.com/WinVector/rquery/blob/master/Examples/Substitution/Substitution.md).

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."

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)) NEWCOL <- as.name("v") NEWVALUE = "zz" optree <- extend(d, .(NEWCOL) %:=% ifelse(AUC>0.5, R2, 1.0), .(NEWVALUE) %:=% 6) cat(format(optree)) sql <- to_sql(optree, my_db) cat(sql) print(DBI::dbGetQuery(my_db, sql)) DBI::dbDisconnect(my_db) }
#> mk_td("d", c( #> "AUC", #> "R2")) %.>% #> extend(., #> v := ifelse(AUC > 0.5, R2, 1), #> zz := 6) #> SELECT #> `AUC`, #> `R2`, #> ( CASE WHEN ( `AUC` > 0.5 ) THEN ( `R2` ) WHEN NOT ( `AUC` > 0.5 ) THEN ( 1 ) ELSE NULL END ) AS `v`, #> 6 AS `zz` #> FROM ( #> SELECT #> `AUC`, #> `R2` #> FROM #> `d` #> ) tsql_00283200735910636206_0000000000 #> AUC R2 v zz #> 1 0.6 0.2 0.2 6