Translate an expression to sql
Translate an expression to 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() )
..., dots |
Expressions to translate. |
con |
An optional database connection to control the details of
the translation. The default, |
vars |
Deprecated. Now call |
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. |
The base translator, base_sql, provides custom mappings for for
commonly used base functions including logical (!, &, |),
arithmetic (^), and comparison (!=) operators, as well as common
summary (mean(), var()) and manipulation 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).
You can use this to access SQL string concatenation: || is mapped to
OR, but %||% is mapped to ||. To suppress this behaviour, and force
errors immediately when dplyr doesn't know how to translate a function it
encounters, using set the dplyr.strict_sql option to TRUE.
You can also use sql() to insert a raw sql string.
The SQLite variant currently only adds one additional function: a mapping
from sd() to the SQL aggregation function STDEV.
# Regular maths is translated in a very straightforward way
translate_sql(x + 1)
translate_sql(sin(x) + tan(y))
# Note that all variable names are escaped
translate_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))
# xor() doesn't have a direct SQL equivalent
translate_sql(xor(x, y))
# If is translated into case when
translate_sql(if (x > 5) "big" else "small")
# Infix functions are passed onto SQL with % removed
translate_sql(first %like% "Had%")
translate_sql(first %is% NA)
translate_sql(first %in% c("John", "Roger", "Robert"))
# And be careful if you really want integers
translate_sql(x == 1)
translate_sql(x == 1L)
# 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))
# Suppress this with window = FALSE
translate_sql(mpg > mean(mpg), window = FALSE)
# vars_group controls partition:
translate_sql(mpg > mean(mpg), vars_group = "cyl")
# and vars_order controls ordering for those functions that need it
translate_sql(cumsum(mpg))
translate_sql(cumsum(mpg), vars_order = "mpg")Please choose more modern alternatives, such as Google Chrome or Mozilla Firefox.