Translate an expression to sql.

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

..., 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 | Grouping and ordering variables used for windowed functions. |

window | Use |

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)#> <SQL> "x" + 1.0translate_sql(sin(x) + tan(y))#> <SQL> SIN("x") + TAN("y")# Note that all variable names are escaped translate_sql(like == "x")#> <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))#> <SQL> "x" < 5.0 AND NOT(("y" >= 5.0))# xor() doesn't have a direct SQL equivalent translate_sql(xor(x, y))#> <SQL> "x" OR "y" AND NOT ("x" AND "y")# If is translated into case when translate_sql(if (x > 5) "big" else "small")#> <SQL> CASE WHEN ("x" > 5.0) THEN ('big') ELSE ('small') END# Infix functions are passed onto SQL with % removed translate_sql(first %like% "Had%")#> <SQL> "first" LIKE 'Had%'translate_sql(first %is% NULL)#> <SQL> "first" IStranslate_sql(first %in% c("John", "Roger", "Robert"))#> <SQL> "first" IN ('John', 'Roger', 'Robert')# And be careful if you really want integers translate_sql(x == 1)#> <SQL> "x" = 1.0translate_sql(x == 1L)#> <SQL> "x" = 1# If you have an already quoted object, use translate_sql_: x <- quote(y + 1 / sin(t)) translate_sql_(list(x))#> <SQL> "y" + 1.0 / SIN("t")# Windowed translation -------------------------------------------- # Known window functions automatically get OVER() translate_sql(mpg > mean(mpg))#> <SQL> "mpg" > avg("mpg") OVER ()# Suppress this with window = FALSE translate_sql(mpg > mean(mpg), window = FALSE)#> <SQL> "mpg" > AVG("mpg")# vars_group controls partition: translate_sql(mpg > mean(mpg), vars_group = "cyl")#> <SQL> "mpg" > avg("mpg") OVER (PARTITION BY "cyl")# and vars_order controls ordering for those functions that need it translate_sql(cumsum(mpg))#> Warning: Windowed expression 'sum("mpg")' does not have explicit order. #> Please use arrange() to make determinstic.#> <SQL> sum("mpg") OVER (ROWS UNBOUNDED PRECEDING)translate_sql(cumsum(mpg), vars_order = "mpg")#> <SQL> sum("mpg") OVER (ORDER BY "mpg" ROWS UNBOUNDED PRECEDING)