if_else_op: Build a 'relop' node simulating a per-row...

In rquery: Relational Query Generator for Data Manipulation at Scale

Build a relop node simulating a per-row block-if(){}else{}.

Description

This device uses expression-ifelse(,,) to simulate the more powerful per-row block-if(){}else{}. The difference is expression-ifelse(,,) can choose per-row what value to express, whereas block-if(){}else{} can choose per-row where to assign multiple values. By simulation we mean: a sequence of quoted mutate expressions are emitted that implement the transform. These expressions can then be optimized into a minimal number of no-dependency blocks by extend_se for efficient execution. The idea is the user can write legible code in this notation, and the translation turns it into safe and efficient code suitable for execution either on data.frames or at a big data scale using RPostgreSQL or sparklyr.

Usage

if_else_op(
  source,
  testexpr,
  ...,
  thenexprs = NULL,
  elseexprs = NULL,
  env = parent.frame()
)

Arguments

source	optree relop node or data.frame.
testexpr	character containing the test expression.
...	force later arguments to bind by name.
thenexprs	named character then assignments (altering columns, not creating).
elseexprs	named character else assignments (altering columns, not creating).
env	environment to look to.

Details

Note: ifebtest_* is a reserved column name for this procedure.

Value

operator tree or data.frame.

See Also

if_else_block

Examples

if (requireNamespace("DBI", quietly = TRUE) && requireNamespace("RSQLite", quietly = TRUE)) {
  # Example: clear one of a or b in any row where both are set.
  my_db <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
  d <- rq_copy_to(
    my_db,
    'd',
    data.frame(i = c(1, 2, 3, 4, 5, 6, 7, 8, 9, 10),
               a = c(0, 0, 1, 1, 1, 1, 1, 1, 1, 1),
               b = c(0, 1, 0, 1, 1, 1, 1, 1, 1, 1),
               edited = NA),
    temporary=TRUE, overwrite=TRUE)

  optree <- d %.>%
    if_else_op(.,
               testexpr = qe((a+b)>1),
               thenexprs = qae(a %:=% 0,
                               b %:=% 0,
                               edited %:=% 1),
               elseexprs = qae(edited %:=% 0))
  cat(format(optree))

  sql <- to_sql(optree, my_db)
  cat(sql)

  print(DBI::dbGetQuery(my_db, sql))

  DBI::dbDisconnect(my_db)
}

rquery documentation built on Aug. 20, 2023, 9:06 a.m.