R dbplyr translate_sql 將表達式轉換為 SQL

translate_sql(
  ...,
  con = NULL,
  vars = character(),
  vars_group = NULL,
  vars_order = NULL,
  vars_frame = NULL,
  window = TRUE
)

translate_sql_(
  dots,
  con = NULL,
  vars_group = NULL,
  vars_order = NULL,
  vars_frame = NULL,
  window = TRUE,
  context = list()
)

# Regular maths is translated in a very straightforward way
translate_sql(x + 1)
#> <SQL> `x` + 1.0
translate_sql(sin(x) + tan(y))
#> <SQL> SIN(`x`) + TAN(`y`)

# Note that all variable names are escaped
translate_sql(like == "x")
#> <SQL> `like` = 'x'
# In ANSI SQL: "" quotes variable _names_, '' quotes strings

# Logical operators are converted to their sql equivalents
translate_sql(x < 5 & !(y >= 5))
#> <SQL> `x` < 5.0 AND NOT((`y` >= 5.0))
# xor() doesn't have a direct SQL equivalent
translate_sql(xor(x, y))
#> <SQL> `x` OR `y` AND NOT (`x` AND `y`)

# If is translated into case when
translate_sql(if (x > 5) "big" else "small")
#> <SQL> CASE WHEN (`x` > 5.0) THEN 'big' WHEN NOT (`x` > 5.0) THEN 'small' END

# Infix functions are passed onto SQL with % removed
translate_sql(first %like% "Had%")
#> <SQL> `first` like 'Had%'
translate_sql(first %is% NA)
#> <SQL> `first` is NULL
translate_sql(first %in% c("John", "Roger", "Robert"))
#> <SQL> `first` IN ('John', 'Roger', 'Robert')

# And be careful if you really want integers
translate_sql(x == 1)
#> <SQL> `x` = 1.0
translate_sql(x == 1L)
#> <SQL> `x` = 1

# If you have an already quoted object, use translate_sql_:
x <- quote(y + 1 / sin(t))
translate_sql_(list(x), con = simulate_dbi())
#> <SQL> `y` + 1.0 / SIN(`t`)

# Windowed translation --------------------------------------------
# Known window functions automatically get OVER()
translate_sql(mpg > mean(mpg))
#> Warning: Missing values are always removed in SQL aggregation functions.
#> Use `na.rm = TRUE` to silence this warning
#> This warning is displayed once every 8 hours.
#> <SQL> `mpg` > AVG(`mpg`) OVER ()

# Suppress this with window = FALSE
translate_sql(mpg > mean(mpg), window = FALSE)
#> <SQL> `mpg` > AVG(`mpg`)

# vars_group controls partition:
translate_sql(mpg > mean(mpg), vars_group = "cyl")
#> <SQL> `mpg` > AVG(`mpg`) OVER (PARTITION BY `cyl`)

# and vars_order controls ordering for those functions that need it
translate_sql(cumsum(mpg))
#> Warning: Windowed expression `SUM(`mpg`)` does not have explicit order.
#> ℹ Please use `arrange()` or `window_order()` to make deterministic.
#> <SQL> SUM(`mpg`) OVER (ROWS UNBOUNDED PRECEDING)
translate_sql(cumsum(mpg), vars_order = "mpg")
#> <SQL> SUM(`mpg`) OVER (ORDER BY `mpg` ROWS UNBOUNDED PRECEDING)