translate_sql: Translate an expression to sql.

Description

Translate an expression to sql.

Usage

translate_sql(..., con = NULL, vars = character(), vars_group = NULL,
  vars_order = NULL, window = TRUE)
translate_sql_(dots, con = NULL, vars = character(), vars_group = NULL,
  vars_order = NULL, window = TRUE)

Arguments

..., dots

Expressions to translate. sql_translate automatically quotes them for you. sql_translate_ expects a list of already quoted objects.

con

An optional database connection to control the details of the translation. The default, NULL, generates ANSI SQL.

vars

A character vector giving variable names in the remote data source. If this is supplied, translate_sql will call partial_eval to interpolate in the values from local variables.

vars_group, vars_order

Grouping and ordering variables used for windowed functions.

window

Use FALSE to suppress generation of the OVER statement used for window functions. This is necessary when generating SQL for a grouped summary.

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

Run this code

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

# Translation with known variables ------------------------------------------

# If the variables in the dataset are known, translate_sql will interpolate
# in literal values from the current environment
x <- 10
translate_sql(mpg > x)
translate_sql(mpg > x, vars = names(mtcars))

# By default all computations happens in sql
translate_sql(cyl == 2 + 2, vars = names(mtcars))
# Use local to force local evaluation
translate_sql(cyl == local(2 + 2), vars = names(mtcars))

# This is also needed if you call a local function:
inc <- function(x) x + 1
translate_sql(mpg > inc(x), vars = names(mtcars))
translate_sql(mpg > local(inc(x)), vars = names(mtcars))

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

Run the code above in your browser using DataCamp Workspace