translate_sql {dbplyr} | R Documentation |
Translate an expression to SQL
Description
dbplyr translates commonly used base functions including logical
(!
, &
, |
), arithmetic (^
), and comparison (!=
) operators, as well
as common summary (mean()
, var()
), and transformation (log()
)
functions. All other functions will be preserved as is. R's infix functions
(e.g. %like%
) will be converted to their SQL equivalents (e.g. LIKE
).
Learn more in vignette("translation-function")
.
Usage
translate_sql(
...,
con,
vars_group = NULL,
vars_order = NULL,
vars_frame = NULL,
window = TRUE
)
translate_sql_(
dots,
con,
vars_group = NULL,
vars_order = NULL,
vars_frame = NULL,
window = TRUE,
context = list()
)
Arguments
... , dots |
Expressions to translate. |
con |
An optional database connection to control the details of
the translation. The default, |
vars_group , vars_order , vars_frame |
Parameters used in the |
window |
Use |
context |
Use to carry information for special translation cases. For example, MS SQL needs a different conversion for is.na() in WHERE vs. SELECT clauses. Expects a list. |
Examples
con <- simulate_dbi()
# Regular maths is translated in a very straightforward way
translate_sql(x + 1, con = con)
translate_sql(sin(x) + tan(y), con = con)
# Note that all variable names are escaped
translate_sql(like == "x", con = con)
# In ANSI SQL: "" quotes variable _names_, '' quotes strings
# Logical operators are converted to their sql equivalents
translate_sql(x < 5 & !(y >= 5), con = con)
# xor() doesn't have a direct SQL equivalent
translate_sql(xor(x, y), con = con)
# If is translated into case when
translate_sql(if (x > 5) "big" else "small", con = con)
# Infix functions are passed onto SQL with % removed
translate_sql(first %like% "Had%", con = con)
translate_sql(first %is% NA, con = con)
translate_sql(first %in% c("John", "Roger", "Robert"), con = con)
# And be careful if you really want integers
translate_sql(x == 1, con = con)
translate_sql(x == 1L, con = con)
# If you have an already quoted object, use translate_sql_:
x <- quote(y + 1 / sin(t))
translate_sql_(list(x), con = simulate_dbi())
# Windowed translation --------------------------------------------
# Known window functions automatically get OVER()
translate_sql(mpg > mean(mpg), con = con)
# Suppress this with window = FALSE
translate_sql(mpg > mean(mpg), window = FALSE, con = con)
# vars_group controls partition:
translate_sql(mpg > mean(mpg), vars_group = "cyl", con = con)
# and vars_order controls ordering for those functions that need it
translate_sql(cumsum(mpg), con = con)
translate_sql(cumsum(mpg), vars_order = "mpg", con = con)