dplyr (version 0.4.0)

translate_sql: Translate an expression to sql.

Description

Translate an expression to sql.

Usage

translate_sql(..., tbl = NULL, env = parent.frame(), variant = NULL,
  window = FALSE)
translate_sql_q(expr, tbl = NULL, env = parent.frame(), variant = NULL,
  window = FALSE)

Arguments

...

unevaluated expression to translate

tbl

An optional tbl. If supplied, will be used to automatically figure out the SQL variant to use.

env

environment in which to evaluate expression.

variant

used to override default variant provided by source useful for testing/examples

window

If variant not supplied, used to determine whether the variant is window based or not.

expr

list of quoted objects to translate

Base translation

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.

SQLite translation

The SQLite variant currently only adds one additional function: a mapping from sd to the SQL aggregation function stdev.

Examples

# 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 --------------------------------------------
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)