Make a theta_join node.

Source: R/theta_join.R

Theta join is a join on an arbitrary predicate.

theta_join_se(
  a,
  b,
  expr,
  ...,
  jointype = "INNER",
  suffix = c("_a", "_b"),
  env = parent.frame()
)

Arguments

a

source to select from.
b

source to select from.
expr

quoted join conditions
...

force later arguments to be by name
jointype

type of join ('INNER', 'LEFT', 'RIGHT', 'FULL').
suffix

character length 2, suffices to disambiguate columns.
env

environment to look for values in.

Value

theta_join node.

Examples


if (requireNamespace("DBI", quietly = TRUE) && requireNamespace("RSQLite", quietly = TRUE)) {
  my_db <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
  d1 <- rq_copy_to(my_db, 'd1',
                    data.frame(AUC = 0.6, R2 = 0.2))
  d2 <- rq_copy_to(my_db, 'd2',
                    data.frame(AUC2 = 0.4, R2 = 0.3))
  optree <- theta_join_se(d1, d2, "AUC >= AUC2")
  cat(format(optree))
  sql <- to_sql(optree, my_db)
  cat(sql)
  print(DBI::dbGetQuery(my_db, sql))
  DBI::dbDisconnect(my_db)
}

#> mk_td("d1", c(
#>   "AUC",
#>   "R2")) %.>%
#>  theta_join(.,
#>   mk_td("d2", c(
#>     "AUC2",
#>     "R2")),
#>   j= INNER; on= rquery_thetajoin_condition_1 := AUC >= AUC2)
#> SELECT
#>  `tsql_92503365928285462658_0000000000`.`AUC` AS `AUC`,
#>  `tsql_92503365928285462658_0000000000`.`R2` AS `R2_a`,
#>  `tsql_92503365928285462658_0000000001`.`R2` AS `R2_b`,
#>  `tsql_92503365928285462658_0000000001`.`AUC2` AS `AUC2`
#> FROM (
#>  SELECT
#>   `AUC`,
#>   `R2`
#>  FROM
#>   `d1`
#> ) `tsql_92503365928285462658_0000000000`
#> INNER JOIN (
#>  SELECT
#>   `AUC2`,
#>   `R2`
#>  FROM
#>   `d2`
#> ) `tsql_92503365928285462658_0000000001` ON AUC >= AUC2
#>   AUC R2_a R2_b AUC2
#> 1 0.6  0.2  0.3  0.4