1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
|
% Generated by roxygen2: do not edit by hand
% Please edit documentation in R/translate-sql.R
\name{translate_sql}
\alias{translate_sql}
\alias{translate_sql_}
\title{Translate an expression to SQL}
\usage{
translate_sql(
...,
con = NULL,
vars = character(),
vars_group = NULL,
vars_order = NULL,
vars_frame = NULL,
window = TRUE
)
translate_sql_(
dots,
con = NULL,
vars_group = NULL,
vars_order = NULL,
vars_frame = NULL,
window = TRUE,
context = list()
)
}
\arguments{
\item{..., dots}{Expressions to translate. \code{translate_sql()}
automatically quotes them for you. \code{translate_sql_()} expects
a list of already quoted objects.}
\item{con}{An optional database connection to control the details of
the translation. The default, \code{NULL}, generates ANSI SQL.}
\item{vars}{Deprecated. Now call \code{\link[=partial_eval]{partial_eval()}} directly.}
\item{vars_group, vars_order, vars_frame}{Parameters used in the \code{OVER}
expression of windowed functions.}
\item{window}{Use \code{FALSE} to suppress generation of the \code{OVER}
statement used for window functions. This is necessary when generating
SQL for a grouped summary.}
\item{context}{Use to carry information for special translation cases. For example, MS SQL needs a different conversion for is.na() in WHERE vs. SELECT clauses. Expects a list.}
}
\description{
dbplyr translates commonly used base functions including logical
(\code{!}, \code{&}, \code{|}), arithmetic (\code{^}), and comparison (\code{!=}) operators, as well
as common summary (\code{mean()}, \code{var()}), and transformation (\code{log()})
functions. All other functions will be preserved as is. R's infix functions
(e.g. \verb{\%like\%}) will be converted to their SQL equivalents (e.g. \code{LIKE}).
Learn more in \code{vignette("translation-function")}.
}
\examples{
# 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\% NA)
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), con = simulate_dbi())
# 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")
}
|
