# 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)
## ---------------------------------------------
Run the code above in your browser using DataCamp Workspace