Extend data by adding more columns.

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_40096587785086276899_0000000000
#>   AUC  R2   v zz
#> 1 0.6 0.2 0.2  6