Extend data by adding more columns. — extend

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

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

  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_19673537631902327278_0000000000
#>  ) tsql_19673537631902327278_0000000001
#>   AUC  R2   v   x
#> 1 0.6 0.2 0.8 0.8