Function translation

Basic differences

The following examples work through some of the basic differences between R and SQL.

• " and ' mean different things

  # In SQLite variable names are escaped by double quotes:
  translate_sql(x, con = con)
  #> <SQL> `x`
  # And strings are escaped by single quotes
  translate_sql("x", con = con)
  #> <SQL> 'x'

• And some functions have different argument orders:

  translate_sql(substr(x, 5, 10), con = con)
  #> <SQL> SUBSTR(`x`, 5, 6)
  translate_sql(log(x, 10), con = con)
  #> <SQL> LOG(10.0, `x`)

• R and SQL have different defaults for integers and reals. In R, 1 is a real, and 1L is an integer. In SQL, 1 is an integer, and 1.0 is a real

  translate_sql(1, con = con)
  #> <SQL> 1.0
  translate_sql(1L, con = con)
  #> <SQL> 1

Known functions

Modulo arithmetic

dbplyr translates %% to the SQL equivalents but note that it’s not precisely the same: most databases use truncated division where the modulo operator takes the sign of the dividend, where R using the mathematically preferred floored division with the modulo sign taking the sign of the divisor.

df <- tibble(
  x = c(10L, 10L, -10L, -10L), 
  y = c(3L, -3L, 3L, -3L)
)
mf <- tbl_memdb(df)

df %>% mutate(x %% y)
#> # A tibble: 4 × 3
#>       x     y `x%%y`
#>   <int> <int>  <int>
#> 1    10     3      1
#> 2    10    -3     -2
#> 3   -10     3      2
#> 4   -10    -3     -1
mf %>% mutate(x %% y)
#> # Source:   SQL [4 x 3]
#> # Database: sqlite 3.45.0 [:memory:]
#>       x     y `x%%y`
#>   <int> <int>  <int>
#> 1    10     3      1
#> 2    10    -3      1
#> 3   -10     3     -1
#> 4   -10    -3     -1

dbplyr no longer translates %/% because there’s no robust cross-database translation available.

translate_sql(mean(x), con = con)
#> <SQL> AVG(`x`) OVER ()
translate_sql(mean(x, na.rm = TRUE), con = con)
#> <SQL> AVG(`x`) OVER ()

translate_sql(mean(x, na.rm = TRUE), window = FALSE, con = con)
#> <SQL> AVG(`x`)

translate_sql(if (x > 5) "big" else "small", con = con)
#> <SQL> CASE WHEN (`x` > 5.0) THEN 'big' WHEN NOT (`x` > 5.0) THEN 'small' END
translate_sql(switch(x, a = 1L, b = 2L, 3L), con = con)
#> <SQL> CASE `x` WHEN ('a') THEN (1) WHEN ('b') THEN (2) ELSE (3) END

Unknown functions

Any function that dplyr doesn’t know how to convert is left as is. This means that database functions that are not covered by dplyr can often be used directly via translate_sql().

translate_sql(foofify(x, y), con = con)
#> <SQL> foofify(`x`, `y`)

translate_sql(FOOFIFY(x, y), con = con)
#> <SQL> FOOFIFY(`x`, `y`)

translate_sql(x %LIKE% "%foo%", con = con)
#> <SQL> `x` LIKE '%foo%'

translate_sql(x %||% y, con = con)
#> <SQL> `x` || `y`

translate_sql(sql("x!"), con = con)
#> <SQL> x!
translate_sql(x == sql("ANY VALUES(1, 2, 3)"), con = con)
#> <SQL> `x` = ANY VALUES(1, 2, 3)

mf <- memdb_frame(x = 1, y = 2)

mf %>% 
  transmute(factorial = sql("x!")) %>% 
  show_query()
#> <SQL>
#> SELECT x! AS `factorial`
#> FROM `dbplyr_zAmugrm8vk`

mf %>% 
  transmute(factorial = sql("CAST(x AS FLOAT)")) %>% 
  show_query()
#> <SQL>
#> SELECT CAST(x AS FLOAT) AS `factorial`
#> FROM `dbplyr_zAmugrm8vk`

options(dplyr.strict_sql = TRUE)
translate_sql(glob(x, y), con = con)
#> Error in `glob()`:
#> ! Don't know how to translate `glob()`

Window functions

Things get a little trickier with window functions, because SQL’s window functions are considerably more expressive than the specific variants provided by base R or dplyr. They have the form [expression] OVER ([partition clause] [order clause] [frame_clause]):

• The expression is a combination of variable names and window functions. Support for window functions varies from database to database, but most support the ranking functions, lead, lag, nth, first, last, count, min, max, sum, avg and stddev.

• The partition clause specifies how the window function is broken down over groups. It plays an analogous role to GROUP BY for aggregate functions, and group_by() in dplyr. It is possible for different window functions to be partitioned into different groups, but not all databases support it, and neither does dplyr.

• The order clause controls the ordering (when it makes a difference). This is important for the ranking functions since it specifies which variables to rank by, but it’s also needed for cumulative functions and lead. Whenever you’re thinking about before and after in SQL, you must always tell it which variable defines the order. If the order clause is missing when needed, some databases fail with an error message while others return non-deterministic results.

• The frame clause defines which rows, or frame, that are passed to the window function, describing which rows (relative to the current row) should be included. The frame clause provides two offsets which determine the start and end of frame. There are three special values: -Inf means to include all preceding rows (in SQL, “unbounded preceding”), 0 means the current row (“current row”), and Inf means all following rows (“unbounded following”). The complete set of options is comprehensive, but fairly confusing, and is summarised visually below.

  

  Of the many possible specifications, there are only three that commonly used. They select between aggregation variants:

  • Recycled: BETWEEN UNBOUND PRECEEDING AND UNBOUND FOLLOWING

  • Cumulative: BETWEEN UNBOUND PRECEEDING AND CURRENT ROW

  • Rolling: BETWEEN 2 PRECEEDING AND 2 FOLLOWING

  dplyr generates the frame clause based on whether your using a recycled aggregate or a cumulative aggregate.

To see how individual window functions are translated to SQL, we can again use translate_sql():

translate_sql(mean(G), con = con)
#> <SQL> AVG(`G`) OVER ()
translate_sql(rank(G), con = con)
#> <SQL> CASE
#> WHEN (NOT((`G` IS NULL))) THEN RANK() OVER (PARTITION BY (CASE WHEN ((`G` IS NULL)) THEN 1 ELSE 0 END) ORDER BY `G`)
#> END
translate_sql(ntile(G, 2), con = con)
#> <SQL> NTILE(2) OVER (ORDER BY `G`)
translate_sql(lag(G), con = con)
#> <SQL> LAG(`G`, 1, NULL) OVER ()

If the tbl has been grouped or arranged previously in the pipeline, then dplyr will use that information to set the “partition by” and “order by” clauses. For interactive exploration, you can achieve the same effect by setting the vars_group and vars_order arguments to translate_sql()

translate_sql(cummean(G), vars_order = "year", con = con)
#> <SQL> AVG(`G`) OVER (ORDER BY `year` ROWS UNBOUNDED PRECEDING)
translate_sql(rank(), vars_group = "ID", con = con)
#> <SQL> RANK() OVER (PARTITION BY `ID`)

There are some challenges when translating window functions between R and SQL, because dplyr tries to keep the window functions as similar as possible to both the existing R analogues and to the SQL functions. This means that there are three ways to control the order clause depending on which window function you’re using:

• For ranking functions, the ordering variable is the first argument: rank(x), ntile(y, 2). If omitted or NULL, will use the default ordering associated with the tbl (as set by arrange()).

• Accumulating aggregates only take a single argument (the vector to aggregate). To control ordering, use order_by().

• Aggregates implemented in dplyr (lead, lag, nth_value, first_value, last_value) have an order_by argument. Supply it to override the default ordering.

The three options are illustrated in the snippet below:

mutate(players,
  min_rank(yearID),
  order_by(yearID, cumsum(G)),
  lead(G, order_by = yearID)
)

Currently there is no way to order by multiple variables, except by setting the default ordering with arrange(). This will be added in a future release.