translate_sql {dplyr} | R Documentation |
Translate an expression to sql.
translate_sql(..., tbl = NULL, env = parent.frame(), variant = NULL, window = FALSE) translate_sql_q(expr, tbl = NULL, env = parent.frame(), variant = NULL, window = FALSE)
... |
unevaluated expression to translate |
tbl |
An optional |
env |
environment in which to evaluate expression. |
variant |
used to override default variant provided by source useful for testing/examples |
window |
If |
expr |
list of quoted objects to translate |
The base translator, base_sql
,
provides custom mappings for !
(to NOT), &&
and &
to
AND
, ||
and |
to OR
, ^
to POWER
,
%>%
to %
, ceiling
to CEIL
, mean
to
AVG
, var
to VARIANCE
, tolower
to LOWER
,
toupper
to UPPER
and nchar
to length
.
c
and :
keep their usual R behaviour so you can easily create
vectors that are passed to sql.
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)) # Logical operators are converted to their sql equivalents translate_sql(x < 5 & !(y >= 5)) # If is translated into select case 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% NULL) translate_sql(first %in% c("John", "Roger", "Robert")) # Note that variable names will be escaped if needed translate_sql(like == 7) # 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_q: x <- quote(y + 1 / sin(t)) translate_sql(x) translate_sql_q(list(x)) # Translation with data source -------------------------------------------- ## Not run: flights <- tbl(nycflights13_sqlite(), "flights") # Note distinction between integers and reals translate_sql(month == 1, tbl = flights) translate_sql(month == 1L, tbl = flights) # Know how to translate most simple mathematical expressions translate_sql(month %in% 1:3, tbl = flights) translate_sql(month >= 1L & month <= 3L, tbl = flights) translate_sql((month >= 1L & month <= 3L) | carrier == "AA", tbl = flights) # Some R functions don't have equivalents in SQL: where possible they # will be translated to the equivalent translate_sql(xor(month <= 3L, carrier == "AA"), tbl = flights) # Local variables will be automatically inserted into the SQL x <- 5L translate_sql(month == x, tbl = flights) # By default all computation will happen in sql translate_sql(month < 1 + 1, source = flights) # Use local to force local evaluation translate_sql(month < local(1 + 1), source = flights) # This is also needed if you call a local function: inc <- function(x) x + 1 translate_sql(month == inc(x), source = flights) translate_sql(month == local(inc(x)), source = flights) # Windowed translation -------------------------------------------- planes <- arrange(group_by(flights, tailnum), desc(DepTime)) translate_sql(dep_time > mean(dep_time), tbl = planes, window = TRUE) translate_sql(dep_time == min(dep_time), tbl = planes, window = TRUE) translate_sql(rank(), tbl = planes, window = TRUE) translate_sql(rank(dep_time), tbl = planes, window = TRUE) translate_sql(ntile(dep_time, 2L), tbl = planes, window = TRUE) translate_sql(lead(dep_time, 2L), tbl = planes, window = TRUE) translate_sql(cumsum(dep_time), tbl = planes, window = TRUE) translate_sql(order_by(dep_time, cumsum(dep_time)), tbl = planes, window = TRUE) ## End(Not run)