3  Supported expressions for database queries

In the previous chapter, Chapter 2, we saw that there are a core set of tidyverse functions that can be used with databases to extract data for analysis. The SQL code used in the previous chapter is consistent across database management systems, since it only involves basic operations such as joins and variable selection.

For more complex data pipelines, we will, however, often need to incorporate additional expressions within these functions. Because of differences across database management systems, the translated SQL can vary. Moreover, some expressions are only supported for some databases.

When writing code that should work across different database management systems, we need to keep in mind which expressions are supported where. To help with this, the sections below show the available SQL translations for common expressions we might want to use.

Let’s first load the packages which these expressions come from. In addition to base R types, bit64 adds support for integer64. The stringr package provides functions for working with strings, while clock has various functions for working with dates. Many other useful expressions will come from dplyr itself.

library(duckdb)
library(bit64)
library(dplyr)
library(dbplyr)
library(stringr)
library(clock)

3.1 Data types

Commonly used data types are consistently supported across database backends. We can use the base as.numeric(), as.integer(), as.character(), as.Date(), and as.POSIXct(). We can also use as.integer64() from the bit64 package to coerce to integer64, and the as_date() and as_datetime() from the clock package instead of as.Date() and as.POSIXct(), respectively.

con <- simulate_duckdb()
translate_sql(as.numeric(var), con = con)
<SQL> CAST(`var` AS NUMERIC)
translate_sql(as.integer(var), con = con)
<SQL> CAST(`var` AS INTEGER)
translate_sql(as.integer64(var), con = con)
<SQL> CAST(`var` AS BIGINT)
translate_sql(as.character(var), con = con)
<SQL> CAST(`var` AS TEXT)
translate_sql(as.Date(var), con = con)
<SQL> CAST(`var` AS DATE)
translate_sql(as_date(var), con = con)
<SQL> CAST(`var` AS DATE)
translate_sql(as.POSIXct(var), con = con)
<SQL> CAST(`var` AS TIMESTAMP)
translate_sql(as_datetime(var), con = con)
<SQL> CAST(`var` AS TIMESTAMP)
translate_sql(as.logical(var), con = con)
<SQL> CAST(`var` AS BOOLEAN)
con <- simulate_postgres()
translate_sql(as.numeric(var), con = con)
<SQL> CAST(`var` AS NUMERIC)
translate_sql(as.integer(var), con = con)
<SQL> CAST(`var` AS INTEGER)
translate_sql(as.integer64(var), con = con)
<SQL> CAST(`var` AS BIGINT)
translate_sql(as.character(var), con = con)
<SQL> CAST(`var` AS TEXT)
translate_sql(as.Date(var), con = con)
<SQL> CAST(`var` AS DATE)
translate_sql(as_date(var), con = con)
<SQL> CAST(`var` AS DATE)
translate_sql(as.POSIXct(var), con = con)
<SQL> CAST(`var` AS TIMESTAMP)
translate_sql(as_datetime(var), con = con)
<SQL> CAST(`var` AS TIMESTAMP)
translate_sql(as.logical(var), con = con)
<SQL> CAST(`var` AS BOOLEAN)
con <- simulate_mssql()
translate_sql(as.numeric(var), con = con)
<SQL> TRY_CAST(`var` AS FLOAT)
translate_sql(as.integer(var), con = con)
<SQL> TRY_CAST(TRY_CAST(`var` AS NUMERIC) AS INT)
translate_sql(as.integer64(var), con = con)
<SQL> TRY_CAST(TRY_CAST(`var` AS NUMERIC(38, 0)) AS BIGINT)
translate_sql(as.character(var), con = con)
<SQL> TRY_CAST(`var` AS VARCHAR(MAX))
translate_sql(as.Date(var), con = con)
<SQL> TRY_CAST(`var` AS DATE)
translate_sql(as_date(var), con = con)
<SQL> TRY_CAST(`var` AS DATE)
translate_sql(as.POSIXct(var), con = con)
<SQL> TRY_CAST(`var` AS DATETIME2)
translate_sql(as_datetime(var), con = con)
<SQL> TRY_CAST(`var` AS DATETIME2)
translate_sql(as.logical(var), con = con)
<SQL> TRY_CAST(`var` AS BIT)
con <- simulate_redshift()
translate_sql(as.numeric(var), con = con)
<SQL> CAST(`var` AS FLOAT)
translate_sql(as.integer(var), con = con)
<SQL> CAST(`var` AS INTEGER)
translate_sql(as.integer64(var), con = con)
<SQL> CAST(`var` AS BIGINT)
translate_sql(as.character(var), con = con)
<SQL> CAST(`var` AS TEXT)
translate_sql(as.Date(var), con = con)
<SQL> CAST(`var` AS DATE)
translate_sql(as_date(var), con = con)
<SQL> CAST(`var` AS DATE)
translate_sql(as.POSIXct(var), con = con)
<SQL> CAST(`var` AS TIMESTAMP)
translate_sql(as_datetime(var), con = con)
<SQL> CAST(`var` AS TIMESTAMP)
translate_sql(as.logical(var), con = con)
<SQL> CAST(`var` AS BOOLEAN)
con <- simulate_snowflake()
translate_sql(as.numeric(var), con = con)
<SQL> CAST(`var` AS DOUBLE)
translate_sql(as.integer(var), con = con)
<SQL> CAST(`var` AS INT)
translate_sql(as.integer64(var), con = con)
<SQL> CAST(`var` AS BIGINT)
translate_sql(as.character(var), con = con)
<SQL> CAST(`var` AS STRING)
translate_sql(as.Date(var), con = con)
<SQL> CAST(`var` AS DATE)
translate_sql(as_date(var), con = con)
<SQL> CAST(`var` AS DATE)
translate_sql(as.POSIXct(var), con = con)
<SQL> CAST(`var` AS TIMESTAMP)
translate_sql(as_datetime(var), con = con)
<SQL> CAST(`var` AS TIMESTAMP)
translate_sql(as.logical(var), con = con)
<SQL> CAST(`var` AS BOOLEAN)
con <- simulate_spark_sql()
translate_sql(as.numeric(var), con = con)
<SQL> CAST(`var` AS DOUBLE)
translate_sql(as.integer(var), con = con)
<SQL> CAST(`var` AS INT)
translate_sql(as.integer64(var), con = con)
<SQL> CAST(`var` AS BIGINT)
translate_sql(as.character(var), con = con)
<SQL> CAST(`var` AS STRING)
translate_sql(as.Date(var), con = con)
<SQL> CAST(`var` AS DATE)
translate_sql(as_date(var), con = con)
<SQL> CAST(`var` AS DATE)
translate_sql(as.POSIXct(var), con = con)
<SQL> CAST(`var` AS TIMESTAMP)
translate_sql(as_datetime(var), con = con)
<SQL> CAST(`var` AS TIMESTAMP)
translate_sql(as.logical(var), con = con)
<SQL> CAST(`var` AS BOOLEAN)

3.2 Comparison and logical operators

Base R comparison operators, such as <, <=, ==, >=, >, are also well supported in all database backends. Logical operators, such as & and |, can also be used as if the data were in R.

con <- simulate_duckdb()
translate_sql(var_1 == var_2, con = con)
<SQL> `var_1` = `var_2`
translate_sql(var_1 >= var_2, con = con)
<SQL> `var_1` >= `var_2`
translate_sql(var_1 < 100, con = con)
<SQL> `var_1` < 100.0
translate_sql(var_1 %in% c("a", "b", "c"), con = con)
<SQL> `var_1` IN ('a', 'b', 'c')
translate_sql(!var_1 %in% c("a", "b", "c"), con = con)
<SQL> NOT(`var_1` IN ('a', 'b', 'c'))
translate_sql(is.na(var_1), con = con)
<SQL> (`var_1` IS NULL)
translate_sql(!is.na(var_1), con = con)
<SQL> NOT((`var_1` IS NULL))
translate_sql(var_1 >= 100 & var_1 < 200, con = con)
<SQL> `var_1` >= 100.0 AND `var_1` < 200.0
translate_sql(var_1 >= 100 | var_1 < 200, con = con)
<SQL> `var_1` >= 100.0 OR `var_1` < 200.0
con <- simulate_postgres()
translate_sql(var_1 == var_2, con = con)
<SQL> `var_1` = `var_2`
translate_sql(var_1 >= var_2, con = con)
<SQL> `var_1` >= `var_2`
translate_sql(var_1 < 100, con = con)
<SQL> `var_1` < 100.0
translate_sql(var_1 %in% c("a", "b", "c"), con = con)
<SQL> `var_1` IN ('a', 'b', 'c')
translate_sql(!var_1 %in% c("a", "b", "c"), con = con)
<SQL> NOT(`var_1` IN ('a', 'b', 'c'))
translate_sql(is.na(var_1), con = con)
<SQL> (`var_1` IS NULL)
translate_sql(!is.na(var_1), con = con)
<SQL> NOT((`var_1` IS NULL))
translate_sql(var_1 >= 100 & var_1 < 200, con = con)
<SQL> `var_1` >= 100.0 AND `var_1` < 200.0
translate_sql(var_1 >= 100 | var_1 < 200, con = con)
<SQL> `var_1` >= 100.0 OR `var_1` < 200.0
con <- simulate_mssql()
translate_sql(var_1 == var_2, con = con)
<SQL> `var_1` = `var_2`
translate_sql(var_1 >= var_2, con = con)
<SQL> `var_1` >= `var_2`
translate_sql(var_1 < 100, con = con)
<SQL> `var_1` < 100.0
translate_sql(var_1 %in% c("a", "b", "c"), con = con)
<SQL> `var_1` IN ('a', 'b', 'c')
translate_sql(!var_1 %in% c("a", "b", "c"), con = con)
<SQL> NOT(`var_1` IN ('a', 'b', 'c'))
translate_sql(is.na(var_1), con = con)
<SQL> (`var_1` IS NULL)
translate_sql(!is.na(var_1), con = con)
<SQL> NOT((`var_1` IS NULL))
translate_sql(var_1 >= 100 & var_1 < 200, con = con)
<SQL> `var_1` >= 100.0 AND `var_1` < 200.0
translate_sql(var_1 >= 100 | var_1 < 200, con = con)
<SQL> `var_1` >= 100.0 OR `var_1` < 200.0
con <- simulate_redshift()
translate_sql(var_1 == var_2, con = con)
<SQL> `var_1` = `var_2`
translate_sql(var_1 >= var_2, con = con)
<SQL> `var_1` >= `var_2`
translate_sql(var_1 < 100, con = con)
<SQL> `var_1` < 100.0
translate_sql(var_1 %in% c("a", "b", "c"), con = con)
<SQL> `var_1` IN ('a', 'b', 'c')
translate_sql(!var_1 %in% c("a", "b", "c"), con = con)
<SQL> NOT(`var_1` IN ('a', 'b', 'c'))
translate_sql(is.na(var_1), con = con)
<SQL> (`var_1` IS NULL)
translate_sql(!is.na(var_1), con = con)
<SQL> NOT((`var_1` IS NULL))
translate_sql(var_1 >= 100 & var_1 < 200, con = con)
<SQL> `var_1` >= 100.0 AND `var_1` < 200.0
translate_sql(var_1 >= 100 | var_1 < 200, con = con)
<SQL> `var_1` >= 100.0 OR `var_1` < 200.0
con <- simulate_snowflake()
translate_sql(var_1 == var_2, con = con)
<SQL> `var_1` = `var_2`
translate_sql(var_1 >= var_2, con = con)
<SQL> `var_1` >= `var_2`
translate_sql(var_1 < 100, con = con)
<SQL> `var_1` < 100.0
translate_sql(var_1 %in% c("a", "b", "c"), con = con)
<SQL> `var_1` IN ('a', 'b', 'c')
translate_sql(!var_1 %in% c("a", "b", "c"), con = con)
<SQL> NOT(`var_1` IN ('a', 'b', 'c'))
translate_sql(is.na(var_1), con = con)
<SQL> (`var_1` IS NULL)
translate_sql(!is.na(var_1), con = con)
<SQL> NOT((`var_1` IS NULL))
translate_sql(var_1 >= 100 & var_1 < 200, con = con)
<SQL> `var_1` >= 100.0 AND `var_1` < 200.0
translate_sql(var_1 >= 100 | var_1 < 200, con = con)
<SQL> `var_1` >= 100.0 OR `var_1` < 200.0
con <- simulate_spark_sql()
translate_sql(var_1 == var_2, con = con)
<SQL> `var_1` = `var_2`
translate_sql(var_1 >= var_2, con = con)
<SQL> `var_1` >= `var_2`
translate_sql(var_1 < 100, con = con)
<SQL> `var_1` < 100.0
translate_sql(var_1 %in% c("a", "b", "c"), con = con)
<SQL> `var_1` IN ('a', 'b', 'c')
translate_sql(!var_1 %in% c("a", "b", "c"), con = con)
<SQL> NOT(`var_1` IN ('a', 'b', 'c'))
translate_sql(is.na(var_1), con = con)
<SQL> (`var_1` IS NULL)
translate_sql(!is.na(var_1), con = con)
<SQL> NOT((`var_1` IS NULL))
translate_sql(var_1 >= 100 & var_1 < 200, con = con)
<SQL> `var_1` >= 100.0 AND `var_1` < 200.0
translate_sql(var_1 >= 100 | var_1 < 200, con = con)
<SQL> `var_1` >= 100.0 OR `var_1` < 200.0

3.3 Conditional statements

The base ifelse function, along with if_else and case_when from dplyr are translated for each database backend. As can be seen in the translations, case_when maps to the SQL CASE WHEN statement.

con <- simulate_duckdb()
translate_sql(ifelse(var == "a", 1L, 2L), con = con)
<SQL> CASE WHEN (`var` = 'a') THEN 1 WHEN NOT (`var` = 'a') THEN 2 END
translate_sql(if_else(var == "a", 1L, 2L), con = con)
<SQL> CASE WHEN (`var` = 'a') THEN 1 WHEN NOT (`var` = 'a') THEN 2 END
translate_sql(case_when(var == "a" ~ 1L, .default = 2L), con = con)
<SQL> CASE WHEN (`var` = 'a') THEN 1 ELSE 2 END
translate_sql(case_when(var == "a" ~ 1L, var == "b" ~ 2L, var == "c" ~ 3L, .default = NULL), 
              con = con)
<SQL> CASE
WHEN (`var` = 'a') THEN 1
WHEN (`var` = 'b') THEN 2
WHEN (`var` = 'c') THEN 3
END
translate_sql(case_when(var == "a" ~ 1L, var == "b" ~ 2L, var == "c" ~ 3L, .default = "something else"), 
              con = con)
<SQL> CASE
WHEN (`var` = 'a') THEN 1
WHEN (`var` = 'b') THEN 2
WHEN (`var` = 'c') THEN 3
ELSE 'something else'
END
con <- simulate_postgres()
translate_sql(ifelse(var == "a", 1L, 2L), con = con)
<SQL> CASE WHEN (`var` = 'a') THEN 1 WHEN NOT (`var` = 'a') THEN 2 END
translate_sql(if_else(var == "a", 1L, 2L), con = con)
<SQL> CASE WHEN (`var` = 'a') THEN 1 WHEN NOT (`var` = 'a') THEN 2 END
translate_sql(case_when(var == "a" ~ 1L, .default = 2L), con = con)
<SQL> CASE WHEN (`var` = 'a') THEN 1 ELSE 2 END
translate_sql(case_when(var == "a" ~ 1L, var == "b" ~ 2L, var == "c" ~ 3L, .default = NULL), 
              con = con)
<SQL> CASE
WHEN (`var` = 'a') THEN 1
WHEN (`var` = 'b') THEN 2
WHEN (`var` = 'c') THEN 3
END
translate_sql(case_when(var == "a" ~ 1L, var == "b" ~ 2L, var == "c" ~ 3L, .default = "something else"), 
              con = con)
<SQL> CASE
WHEN (`var` = 'a') THEN 1
WHEN (`var` = 'b') THEN 2
WHEN (`var` = 'c') THEN 3
ELSE 'something else'
END
con <- simulate_mssql()
translate_sql(ifelse(var == "a", 1L, 2L), con = con)
<SQL> IIF(`var` = 'a', 1, 2)
translate_sql(if_else(var == "a", 1L, 2L), con = con)
<SQL> IIF(`var` = 'a', 1, 2)
translate_sql(case_when(var == "a" ~ 1L, .default = 2L), con = con)
<SQL> CASE WHEN (`var` = 'a') THEN 1 ELSE 2 END
translate_sql(case_when(var == "a" ~ 1L, var == "b" ~ 2L, var == "c" ~ 3L, .default = NULL), 
              con = con)
<SQL> CASE
WHEN (`var` = 'a') THEN 1
WHEN (`var` = 'b') THEN 2
WHEN (`var` = 'c') THEN 3
END
translate_sql(case_when(var == "a" ~ 1L, var == "b" ~ 2L, var == "c" ~ 3L, .default = "something else"), 
              con = con)
<SQL> CASE
WHEN (`var` = 'a') THEN 1
WHEN (`var` = 'b') THEN 2
WHEN (`var` = 'c') THEN 3
ELSE 'something else'
END
con <- simulate_redshift()
translate_sql(ifelse(var == "a", 1L, 2L), con = con)
<SQL> CASE WHEN (`var` = 'a') THEN 1 WHEN NOT (`var` = 'a') THEN 2 END
translate_sql(if_else(var == "a", 1L, 2L), con = con)
<SQL> CASE WHEN (`var` = 'a') THEN 1 WHEN NOT (`var` = 'a') THEN 2 END
translate_sql(case_when(var == "a" ~ 1L, .default = 2L), con = con)
<SQL> CASE WHEN (`var` = 'a') THEN 1 ELSE 2 END
translate_sql(case_when(var == "a" ~ 1L, var == "b" ~ 2L, var == "c" ~ 3L, .default = NULL), 
              con = con)
<SQL> CASE
WHEN (`var` = 'a') THEN 1
WHEN (`var` = 'b') THEN 2
WHEN (`var` = 'c') THEN 3
END
translate_sql(case_when(var == "a" ~ 1L, var == "b" ~ 2L, var == "c" ~ 3L, .default = "something else"), 
              con = con)
<SQL> CASE
WHEN (`var` = 'a') THEN 1
WHEN (`var` = 'b') THEN 2
WHEN (`var` = 'c') THEN 3
ELSE 'something else'
END
con <- simulate_snowflake()
translate_sql(ifelse(var == "a", 1L, 2L), con = con)
<SQL> CASE WHEN (`var` = 'a') THEN 1 WHEN NOT (`var` = 'a') THEN 2 END
translate_sql(if_else(var == "a", 1L, 2L), con = con)
<SQL> CASE WHEN (`var` = 'a') THEN 1 WHEN NOT (`var` = 'a') THEN 2 END
translate_sql(case_when(var == "a" ~ 1L, .default = 2L), con = con)
<SQL> CASE WHEN (`var` = 'a') THEN 1 ELSE 2 END
translate_sql(case_when(var == "a" ~ 1L, var == "b" ~ 2L, var == "c" ~ 3L, .default = NULL), 
              con = con)
<SQL> CASE
WHEN (`var` = 'a') THEN 1
WHEN (`var` = 'b') THEN 2
WHEN (`var` = 'c') THEN 3
END
translate_sql(case_when(var == "a" ~ 1L, var == "b" ~ 2L, var == "c" ~ 3L, .default = "something else"), 
              con = con)
<SQL> CASE
WHEN (`var` = 'a') THEN 1
WHEN (`var` = 'b') THEN 2
WHEN (`var` = 'c') THEN 3
ELSE 'something else'
END
con <- simulate_spark_sql()
translate_sql(ifelse(var == "a", 1L, 2L), con = con)
<SQL> CASE WHEN (`var` = 'a') THEN 1 WHEN NOT (`var` = 'a') THEN 2 END
translate_sql(if_else(var == "a", 1L, 2L), con = con)
<SQL> CASE WHEN (`var` = 'a') THEN 1 WHEN NOT (`var` = 'a') THEN 2 END
translate_sql(case_when(var == "a" ~ 1L, .default = 2L), con = con)
<SQL> CASE WHEN (`var` = 'a') THEN 1 ELSE 2 END
translate_sql(case_when(var == "a" ~ 1L, var == "b" ~ 2L, var == "c" ~ 3L, .default = NULL), 
              con = con)
<SQL> CASE
WHEN (`var` = 'a') THEN 1
WHEN (`var` = 'b') THEN 2
WHEN (`var` = 'c') THEN 3
END
translate_sql(case_when(var == "a" ~ 1L, var == "b" ~ 2L, var == "c" ~ 3L, .default = "something else"), 
              con = con)
<SQL> CASE
WHEN (`var` = 'a') THEN 1
WHEN (`var` = 'b') THEN 2
WHEN (`var` = 'c') THEN 3
ELSE 'something else'
END

3.4 Working with strings

Compared to the previous sections, there is much more variation in support of functions to work with strings across database management systems. In particular, although various useful stringr functions do have translations ubiquitously, it can be seen below that more translations are available for some databases compared to others.

con <- simulate_duckdb()
translate_sql(nchar(var), con = con)
<SQL> LENGTH(`var`)
translate_sql(nzchar(var), con = con)
<SQL> ((`var` IS NULL) OR `var` != '')
translate_sql(substr(var, 1, 2), con = con)
<SQL> SUBSTR(`var`, 1, 2)
translate_sql(trimws(var), con = con)
<SQL> LTRIM(RTRIM(`var`))
translate_sql(tolower(var), con = con)
<SQL> LOWER(`var`)
translate_sql(str_to_lower(var), con = con)
<SQL> LOWER(`var`)
translate_sql(toupper(var), con = con)
<SQL> UPPER(`var`)
translate_sql(str_to_upper(var), con = con)
<SQL> UPPER(`var`)
translate_sql(str_to_title(var), con = con)
<SQL> INITCAP(`var`)
translate_sql(str_trim(var), con = con)
<SQL> LTRIM(RTRIM(`var`))
translate_sql(str_squish(var), con = con)
<SQL> TRIM(REGEXP_REPLACE(`var`, '\s+', ' ', 'g'))
translate_sql(str_detect(var, "b"), con = con)
<SQL> REGEXP_MATCHES(`var`, 'b')
translate_sql(str_detect(var, "b", negate = TRUE), con = con)
<SQL> (NOT(REGEXP_MATCHES(`var`, 'b')))
translate_sql(str_detect(var, "[aeiou]"), con = con)
<SQL> REGEXP_MATCHES(`var`, '[aeiou]')
translate_sql(str_replace(var, "a", "b"), con = con)
<SQL> REGEXP_REPLACE(`var`, 'a', 'b')
translate_sql(str_replace_all(var, "a", "b"), con = con)
<SQL> REGEXP_REPLACE(`var`, 'a', 'b', 'g')
translate_sql(str_remove(var, "a"), con = con)
<SQL> REGEXP_REPLACE(`var`, 'a', '')
translate_sql(str_remove_all(var, "a"), con = con)
<SQL> REGEXP_REPLACE(`var`, 'a', '', 'g')
translate_sql(str_like(var, "a"), con = con)
<SQL> `var` LIKE 'a'
translate_sql(str_starts(var, "a"), con = con)
<SQL> REGEXP_MATCHES(`var`, '^(?:' || 'a' || ')')
translate_sql(str_ends(var, "a"), con = con)
<SQL> REGEXP_MATCHES(`var`, '(?:' || 'a' || ')$')
con <- simulate_postgres()
translate_sql(nchar(var), con = con)
<SQL> LENGTH(`var`)
translate_sql(nzchar(var), con = con)
<SQL> ((`var` IS NULL) OR `var` != '')
translate_sql(substr(var, 1, 2), con = con)
<SQL> SUBSTR(`var`, 1, 2)
translate_sql(trimws(var), con = con)
<SQL> LTRIM(RTRIM(`var`))
translate_sql(tolower(var), con = con)
<SQL> LOWER(`var`)
translate_sql(str_to_lower(var), con = con)
<SQL> LOWER(`var`)
translate_sql(toupper(var), con = con)
<SQL> UPPER(`var`)
translate_sql(str_to_upper(var), con = con)
<SQL> UPPER(`var`)
translate_sql(str_to_title(var), con = con)
<SQL> INITCAP(`var`)
translate_sql(str_trim(var), con = con)
<SQL> LTRIM(RTRIM(`var`))
translate_sql(str_squish(var), con = con)
<SQL> LTRIM(RTRIM(REGEXP_REPLACE(`var`, '\s+', ' ', 'g')))
translate_sql(str_detect(var, "b"), con = con)
<SQL> `var` ~ 'b'
translate_sql(str_detect(var, "b", negate = TRUE), con = con)
<SQL> !(`var` ~ 'b')
translate_sql(str_detect(var, "[aeiou]"), con = con)
<SQL> `var` ~ '[aeiou]'
translate_sql(str_replace(var, "a", "b"), con = con)
<SQL> REGEXP_REPLACE(`var`, 'a', 'b')
translate_sql(str_replace_all(var, "a", "b"), con = con)
<SQL> REGEXP_REPLACE(`var`, 'a', 'b', 'g')
translate_sql(str_remove(var, "a"), con = con)
<SQL> REGEXP_REPLACE(`var`, 'a', '')
translate_sql(str_remove_all(var, "a"), con = con)
<SQL> REGEXP_REPLACE(`var`, 'a', '', 'g')
translate_sql(str_like(var, "a"), con = con)
<SQL> `var` ILIKE 'a'
translate_sql(str_starts(var, "a"), con = con)
Error in `str_starts()`:
! Only fixed patterns are supported on database backends.
translate_sql(str_ends(var, "a"), con = con)
Error in `str_ends()`:
! Only fixed patterns are supported on database backends.
con <- simulate_mssql()
translate_sql(nchar(var), con = con)
<SQL> LEN(`var`)
translate_sql(nzchar(var), con = con)
<SQL> ((`var` IS NULL) OR `var` != '')
translate_sql(substr(var, 1, 2), con = con)
<SQL> SUBSTRING(`var`, 1, 2)
translate_sql(trimws(var), con = con)
<SQL> LTRIM(RTRIM(`var`))
translate_sql(tolower(var), con = con)
<SQL> LOWER(`var`)
translate_sql(str_to_lower(var), con = con)
<SQL> LOWER(`var`)
translate_sql(toupper(var), con = con)
<SQL> UPPER(`var`)
translate_sql(str_to_upper(var), con = con)
<SQL> UPPER(`var`)
translate_sql(str_to_title(var), con = con)
Error in `str_to_title()`:
! `str_to_title()` is not available in this SQL variant.
translate_sql(str_trim(var), con = con)
<SQL> LTRIM(RTRIM(`var`))
translate_sql(str_squish(var), con = con)
Error in `str_squish()`:
! `str_squish()` is not available in this SQL variant.
translate_sql(str_detect(var, "b"), con = con)
Error in `str_detect()`:
! Only fixed patterns are supported on database backends.
translate_sql(str_detect(var, "b", negate = TRUE), con = con)
Error in `str_detect()`:
! Only fixed patterns are supported on database backends.
translate_sql(str_detect(var, "[aeiou]"), con = con)
Error in `str_detect()`:
! Only fixed patterns are supported on database backends.
translate_sql(str_replace(var, "a", "b"), con = con)
Error in `str_replace()`:
! `str_replace()` is not available in this SQL variant.
translate_sql(str_replace_all(var, "a", "b"), con = con)
Error in `str_replace_all()`:
! `str_replace_all()` is not available in this SQL variant.
translate_sql(str_remove(var, "a"), con = con)
Error in `str_remove()`:
! `str_remove()` is not available in this SQL variant.
translate_sql(str_remove_all(var, "a"), con = con)
Error in `str_remove_all()`:
! `str_remove_all()` is not available in this SQL variant.
translate_sql(str_like(var, "a"), con = con)
<SQL> `var` LIKE 'a'
translate_sql(str_starts(var, "a"), con = con)
Error in `str_starts()`:
! Only fixed patterns are supported on database backends.
translate_sql(str_ends(var, "a"), con = con)
Error in `str_ends()`:
! Only fixed patterns are supported on database backends.
con <- simulate_redshift()
translate_sql(nchar(var), con = con)
<SQL> LENGTH(`var`)
translate_sql(nzchar(var), con = con)
<SQL> ((`var` IS NULL) OR `var` != '')
translate_sql(substr(var, 1, 2), con = con)
<SQL> SUBSTRING(`var`, 1, 2)
translate_sql(trimws(var), con = con)
<SQL> LTRIM(RTRIM(`var`))
translate_sql(tolower(var), con = con)
<SQL> LOWER(`var`)
translate_sql(str_to_lower(var), con = con)
<SQL> LOWER(`var`)
translate_sql(toupper(var), con = con)
<SQL> UPPER(`var`)
translate_sql(str_to_upper(var), con = con)
<SQL> UPPER(`var`)
translate_sql(str_to_title(var), con = con)
<SQL> INITCAP(`var`)
translate_sql(str_trim(var), con = con)
<SQL> LTRIM(RTRIM(`var`))
translate_sql(str_squish(var), con = con)
<SQL> LTRIM(RTRIM(REGEXP_REPLACE(`var`, '\s+', ' ', 'g')))
translate_sql(str_detect(var, "b"), con = con)
<SQL> `var` ~ 'b'
translate_sql(str_detect(var, "b", negate = TRUE), con = con)
<SQL> !(`var` ~ 'b')
translate_sql(str_detect(var, "[aeiou]"), con = con)
<SQL> `var` ~ '[aeiou]'
translate_sql(str_replace(var, "a", "b"), con = con)
Error in `str_replace()`:
! `str_replace()` is not available in this SQL variant.
translate_sql(str_replace_all(var, "a", "b"), con = con)
<SQL> REGEXP_REPLACE(`var`, 'a', 'b')
translate_sql(str_remove(var, "a"), con = con)
<SQL> REGEXP_REPLACE(`var`, 'a', '')
translate_sql(str_remove_all(var, "a"), con = con)
<SQL> REGEXP_REPLACE(`var`, 'a', '', 'g')
translate_sql(str_like(var, "a"), con = con)
<SQL> `var` ILIKE 'a'
translate_sql(str_starts(var, "a"), con = con)
Error in `str_starts()`:
! Only fixed patterns are supported on database backends.
translate_sql(str_ends(var, "a"), con = con)
Error in `str_ends()`:
! Only fixed patterns are supported on database backends.
con <- simulate_snowflake()
translate_sql(nchar(var), con = con)
<SQL> LENGTH(`var`)
translate_sql(nzchar(var), con = con)
<SQL> ((`var` IS NULL) OR `var` != '')
translate_sql(substr(var, 1, 2), con = con)
<SQL> SUBSTR(`var`, 1, 2)
translate_sql(trimws(var), con = con)
<SQL> LTRIM(RTRIM(`var`))
translate_sql(tolower(var), con = con)
<SQL> LOWER(`var`)
translate_sql(str_to_lower(var), con = con)
<SQL> LOWER(`var`)
translate_sql(toupper(var), con = con)
<SQL> UPPER(`var`)
translate_sql(str_to_upper(var), con = con)
<SQL> UPPER(`var`)
translate_sql(str_to_title(var), con = con)
<SQL> INITCAP(`var`)
translate_sql(str_trim(var), con = con)
<SQL> TRIM(`var`)
translate_sql(str_squish(var), con = con)
<SQL> REGEXP_REPLACE(TRIM(`var`), '\\s+', ' ')
translate_sql(str_detect(var, "b"), con = con)
<SQL> REGEXP_INSTR(`var`, 'b') != 0
translate_sql(str_detect(var, "b", negate = TRUE), con = con)
<SQL> REGEXP_INSTR(`var`, 'b') = 0
translate_sql(str_detect(var, "[aeiou]"), con = con)
<SQL> REGEXP_INSTR(`var`, '[aeiou]') != 0
translate_sql(str_replace(var, "a", "b"), con = con)
<SQL> REGEXP_REPLACE(`var`, 'a', 'b', 1.0, 1.0)
translate_sql(str_replace_all(var, "a", "b"), con = con)
<SQL> REGEXP_REPLACE(`var`, 'a', 'b')
translate_sql(str_remove(var, "a"), con = con)
<SQL> REGEXP_REPLACE(`var`, 'a', '', 1.0, 1.0)
translate_sql(str_remove_all(var, "a"), con = con)
<SQL> REGEXP_REPLACE(`var`, 'a')
translate_sql(str_like(var, "a"), con = con)
<SQL> `var` LIKE 'a'
translate_sql(str_starts(var, "a"), con = con)
<SQL> REGEXP_INSTR(`var`, 'a') = 1
translate_sql(str_ends(var, "a"), con = con)
<SQL> REGEXP_INSTR(`var`, 'a', 1, 1, 1) = (LENGTH(`var`) + 1)
con <- simulate_spark_sql()
translate_sql(nchar(var), con = con)
<SQL> LENGTH(`var`)
translate_sql(nzchar(var), con = con)
<SQL> ((`var` IS NULL) OR `var` != '')
translate_sql(substr(var, 1, 2), con = con)
<SQL> SUBSTR(`var`, 1, 2)
translate_sql(trimws(var), con = con)
<SQL> LTRIM(RTRIM(`var`))
translate_sql(tolower(var), con = con)
<SQL> LOWER(`var`)
translate_sql(str_to_lower(var), con = con)
<SQL> LOWER(`var`)
translate_sql(toupper(var), con = con)
<SQL> UPPER(`var`)
translate_sql(str_to_upper(var), con = con)
<SQL> UPPER(`var`)
translate_sql(str_to_title(var), con = con)
<SQL> INITCAP(`var`)
translate_sql(str_trim(var), con = con)
<SQL> LTRIM(RTRIM(`var`))
translate_sql(str_squish(var), con = con)
Error in `str_squish()`:
! `str_squish()` is not available in this SQL variant.
translate_sql(str_detect(var, "b"), con = con)
Error in `str_detect()`:
! Only fixed patterns are supported on database backends.
translate_sql(str_detect(var, "b", negate = TRUE), con = con)
Error in `str_detect()`:
! Only fixed patterns are supported on database backends.
translate_sql(str_detect(var, "[aeiou]"), con = con)
Error in `str_detect()`:
! Only fixed patterns are supported on database backends.
translate_sql(str_replace(var, "a", "b"), con = con)
Error in `str_replace()`:
! `str_replace()` is not available in this SQL variant.
translate_sql(str_replace_all(var, "a", "b"), con = con)
Error in `str_replace_all()`:
! `str_replace_all()` is not available in this SQL variant.
translate_sql(str_remove(var, "a"), con = con)
Error in `str_remove()`:
! `str_remove()` is not available in this SQL variant.
translate_sql(str_remove_all(var, "a"), con = con)
Error in `str_remove_all()`:
! `str_remove_all()` is not available in this SQL variant.
translate_sql(str_like(var, "a"), con = con)
<SQL> `var` LIKE 'a'
translate_sql(str_starts(var, "a"), con = con)
Error in `str_starts()`:
! Only fixed patterns are supported on database backends.
translate_sql(str_ends(var, "a"), con = con)
Error in `str_ends()`:
! Only fixed patterns are supported on database backends.

3.5 Working with dates

Like with strings, support for working with dates is somewhat mixed. In general, we would use functions from the clock package such as get_day(), get_month(), get_year() to extract parts from a date, add_days() to add or subtract days to a date, and date_count_between() to get the number of days between two date variables.

con <- simulate_duckdb()
translate_sql(get_day(date_1), con = con)
<SQL> DATE_PART('day', `date_1`)
translate_sql(get_month(date_1), con = con)
<SQL> DATE_PART('month', `date_1`)
translate_sql(get_year(date_1), con = con)
<SQL> DATE_PART('year', `date_1`)
translate_sql(add_days(date_1, 1), con = con)
<SQL> DATE_ADD(`date_1`, INTERVAL (1.0) day)
translate_sql(add_years(date_1, 1), con = con)
<SQL> DATE_ADD(`date_1`, INTERVAL (1.0) year)
translate_sql(date_count_between(date_1, date_2, "day"), con = con)
<SQL> DATEDIFF('day', `date_1`, `date_2`)
con <- simulate_postgres()
translate_sql(get_day(date_1), con = con)
<SQL> DATE_PART('day', `date_1`)
translate_sql(get_month(date_1), con = con)
<SQL> DATE_PART('month', `date_1`)
translate_sql(get_year(date_1), con = con)
<SQL> DATE_PART('year', `date_1`)
translate_sql(add_days(date_1, 1), con = con)
<SQL> (`date_1` + 1.0*INTERVAL'1 day')
translate_sql(add_years(date_1, 1), con = con)
<SQL> (`date_1` + 1.0*INTERVAL'1 year')
translate_sql(date_count_between(date_1, date_2, "day"), con = con)
<SQL> `date_2` - `date_1`
con <- simulate_mssql()
translate_sql(get_day(date_1), con = con)
<SQL> DATEPART(DAY, `date_1`)
translate_sql(get_month(date_1), con = con)
<SQL> DATEPART(MONTH, `date_1`)
translate_sql(get_year(date_1), con = con)
<SQL> DATEPART(YEAR, `date_1`)
translate_sql(add_days(date_1, 1), con = con)
<SQL> DATEADD(DAY, 1.0, `date_1`)
translate_sql(add_years(date_1, 1), con = con)
<SQL> DATEADD(YEAR, 1.0, `date_1`)
translate_sql(date_count_between(date_1, date_2, "day"), con = con)
<SQL> DATEDIFF(DAY, `date_1`, `date_2`)
con <- simulate_redshift()
translate_sql(get_day(date_1), con = con)
<SQL> DATE_PART('day', `date_1`)
translate_sql(get_month(date_1), con = con)
<SQL> DATE_PART('month', `date_1`)
translate_sql(get_year(date_1), con = con)
<SQL> DATE_PART('year', `date_1`)
translate_sql(add_days(date_1, 1), con = con)
<SQL> DATEADD(DAY, 1.0, `date_1`)
translate_sql(add_years(date_1, 1), con = con)
<SQL> DATEADD(YEAR, 1.0, `date_1`)
translate_sql(date_count_between(date_1, date_2, "day"), con = con)
<SQL> DATEDIFF(DAY, `date_1`, `date_2`)
con <- simulate_snowflake()
translate_sql(get_day(date_1), con = con)
<SQL> DATE_PART(DAY, `date_1`)
translate_sql(get_month(date_1), con = con)
<SQL> DATE_PART(MONTH, `date_1`)
translate_sql(get_year(date_1), con = con)
<SQL> DATE_PART(YEAR, `date_1`)
translate_sql(add_days(date_1, 1), con = con)
<SQL> DATEADD(DAY, 1.0, `date_1`)
translate_sql(add_years(date_1, 1), con = con)
<SQL> DATEADD(YEAR, 1.0, `date_1`)
translate_sql(date_count_between(date_1, date_2, "day"), con = con)
<SQL> DATEDIFF(DAY, `date_1`, `date_2`)
con <- simulate_spark_sql()
translate_sql(get_day(date_1), con = con)
<SQL> DATE_PART('DAY', `date_1`)
translate_sql(get_month(date_1), con = con)
<SQL> DATE_PART('MONTH', `date_1`)
translate_sql(get_year(date_1), con = con)
<SQL> DATE_PART('YEAR', `date_1`)
translate_sql(add_days(date_1, 1), con = con)
<SQL> DATE_ADD(`date_1`, 1.0)
translate_sql(add_years(date_1, 1), con = con)
<SQL> ADD_MONTHS(`date_1`, 1.0 * 12.0)
translate_sql(date_count_between(date_1, date_2, "day"), con = con)
<SQL> DATEDIFF(`date_2`, `date_1`)

3.6 Data aggregation

Within the context of using summarise(), we can get aggregated results across entire columns using functions such as n(), n_distinct(), sum(), min(), max(), mean(), and sd(). As can be seen below, the SQL for these calculations is similar across different database management systems.

lazy_frame(x = c(1, 2), con = simulate_duckdb()) |>
  summarise(
    n = n(),
    n_unique = n_distinct(x),
    sum = sum(x, na.rm = TRUE),
    sum_is_1 = sum(x == 1, na.rm = TRUE),
    min = min(x, na.rm = TRUE),
    mean = mean(x, na.rm = TRUE),
    max = max(x, na.rm = TRUE),
    sd = sd(x, na.rm = TRUE)
  ) |> 
  show_query()
<SQL>
SELECT
  COUNT(*) AS `n`,
  COUNT(DISTINCT row(`x`)) AS `n_unique`,
  SUM(`x`) AS `sum`,
  SUM(`x` = 1.0) AS `sum_is_1`,
  MIN(`x`) AS `min`,
  AVG(`x`) AS `mean`,
  MAX(`x`) AS `max`,
  STDDEV(`x`) AS `sd`
FROM `df`
lazy_frame(x = c(1, 2), con = simulate_postgres()) |>
  summarise(
    n = n(),
    n_unique = n_distinct(x),
    sum = sum(x, na.rm = TRUE),
    sum_is_1 = sum(x == 1, na.rm = TRUE),
    min = min(x, na.rm = TRUE),
    mean = mean(x, na.rm = TRUE),
    max = max(x, na.rm = TRUE),
    sd = sd(x, na.rm = TRUE)
  ) |> 
  show_query()
<SQL>
SELECT
  COUNT(*) AS `n`,
  COUNT(DISTINCT `x`) AS `n_unique`,
  SUM(`x`) AS `sum`,
  SUM(`x` = 1.0) AS `sum_is_1`,
  MIN(`x`) AS `min`,
  AVG(`x`) AS `mean`,
  MAX(`x`) AS `max`,
  STDDEV_SAMP(`x`) AS `sd`
FROM `df`
lazy_frame(x = c(1, 2), con = simulate_mssql()) |>
  summarise(
    n = n(),
    n_unique = n_distinct(x),
    sum = sum(x, na.rm = TRUE),
    sum_is_1 = sum(x == 1, na.rm = TRUE),
    min = min(x, na.rm = TRUE),
    mean = mean(x, na.rm = TRUE),
    max = max(x, na.rm = TRUE),
    sd = sd(x, na.rm = TRUE)
  ) |> 
  show_query()
<SQL>
SELECT
  COUNT_BIG(*) AS `n`,
  COUNT(DISTINCT `x`) AS `n_unique`,
  SUM(`x`) AS `sum`,
  SUM(CAST(IIF(`x` = 1.0, 1, 0) AS BIT)) AS `sum_is_1`,
  MIN(`x`) AS `min`,
  AVG(`x`) AS `mean`,
  MAX(`x`) AS `max`,
  STDEV(`x`) AS `sd`
FROM `df`
lazy_frame(x = c(1, 2), con = simulate_redshift()) |>
  summarise(
    n = n(),
    n_unique = n_distinct(x),
    sum = sum(x, na.rm = TRUE),
    sum_is_1 = sum(x == 1, na.rm = TRUE),
    min = min(x, na.rm = TRUE),
    mean = mean(x, na.rm = TRUE),
    max = max(x, na.rm = TRUE),
    sd = sd(x, na.rm = TRUE)
  ) |> 
  show_query()
<SQL>
SELECT
  COUNT(*) AS `n`,
  COUNT(DISTINCT `x`) AS `n_unique`,
  SUM(`x`) AS `sum`,
  SUM(`x` = 1.0) AS `sum_is_1`,
  MIN(`x`) AS `min`,
  AVG(`x`) AS `mean`,
  MAX(`x`) AS `max`,
  STDDEV_SAMP(`x`) AS `sd`
FROM `df`
lazy_frame(x = c(1, 2), con = simulate_snowflake()) |>
  summarise(
    n = n(),
    n_unique = n_distinct(x),
    sum = sum(x, na.rm = TRUE),
    sum_is_1 = sum(x == 1, na.rm = TRUE),
    min = min(x, na.rm = TRUE),
    mean = mean(x, na.rm = TRUE),
    max = max(x, na.rm = TRUE),
    sd = sd(x, na.rm = TRUE)
  ) |> 
  show_query()
<SQL>
SELECT
  COUNT(*) AS `n`,
  COUNT(DISTINCT `x`) AS `n_unique`,
  SUM(`x`) AS `sum`,
  SUM(`x` = 1.0) AS `sum_is_1`,
  MIN(`x`) AS `min`,
  AVG(`x`) AS `mean`,
  MAX(`x`) AS `max`,
  STDDEV(`x`) AS `sd`
FROM `df`
lazy_frame(x = c(1, 2), con = simulate_spark_sql()) |>
  summarise(
    n = n(),
    n_unique = n_distinct(x),
    sum = sum(x, na.rm = TRUE),
    sum_is_1 = sum(x == 1, na.rm = TRUE),
    min = min(x, na.rm = TRUE),
    mean = mean(x, na.rm = TRUE),
    max = max(x, na.rm = TRUE),
    sd = sd(x, na.rm = TRUE)
  ) |> 
  show_query()
<SQL>
SELECT
  COUNT(*) AS `n`,
  COUNT(DISTINCT `x`) AS `n_unique`,
  SUM(`x`) AS `sum`,
  SUM(`x` = 1.0) AS `sum_is_1`,
  MIN(`x`) AS `min`,
  AVG(`x`) AS `mean`,
  MAX(`x`) AS `max`,
  STDDEV_SAMP(`x`) AS `sd`
FROM `df`

3.7 Window functions

Window functions differ from data aggregation functions in that they perform calculations across rows that are related to the current row, rather than collapsing multiple rows into a single result. For these operations, we use mutate() instead of summarise().

For instance, we can use window functions like cumsum() and cummean() to calculate running totals and averages, or lag() and lead() to help compare rows to their preceding or following rows.

Given that window functions compare rows to rows before or after them, we will often use arrange() or window_order() to specify the order of rows. This will translate into an ORDER BY clause in the SQL. In addition, we may well also want to apply window functions within some specific groupings in our data. Using group_by() would result in a PARTITION BY clause in the translated SQL so that the window function operates on each group independently.

con <- simulate_duckdb()
lazy_frame(x = c(10, 20, 30), z = c(1, 2, 3), con = con) |> 
  window_order(z) |> 
  mutate(
    sum_x = cumsum(x),
    mean_x = cummean(x),
    lag_x = lag(x), 
    lead_x = lead(x)
  ) |> 
  show_query()
<SQL>
SELECT
  `df`.*,
  SUM(`x`) OVER (ORDER BY `z` ROWS UNBOUNDED PRECEDING) AS `sum_x`,
  AVG(`x`) OVER (ORDER BY `z` ROWS UNBOUNDED PRECEDING) AS `mean_x`,
  LAG(`x`, 1, NULL) OVER (ORDER BY `z`) AS `lag_x`,
  LEAD(`x`, 1, NULL) OVER (ORDER BY `z`) AS `lead_x`
FROM `df`
lazy_frame(x = c(10, 20), y = c("a", "b"), z = c(1, 2), con = con) |>
  window_order(z) |> 
  group_by(y) |> 
  mutate(
    sum_x = cumsum(x),
    mean_x = cummean(x),
    lag_x = lag(x), 
    lead_x = lead(x)
  ) |> 
  show_query()
<SQL>
SELECT
  `df`.*,
  SUM(`x`) OVER (PARTITION BY `y` ORDER BY `z` ROWS UNBOUNDED PRECEDING) AS `sum_x`,
  AVG(`x`) OVER (PARTITION BY `y` ORDER BY `z` ROWS UNBOUNDED PRECEDING) AS `mean_x`,
  LAG(`x`, 1, NULL) OVER (PARTITION BY `y` ORDER BY `z`) AS `lag_x`,
  LEAD(`x`, 1, NULL) OVER (PARTITION BY `y` ORDER BY `z`) AS `lead_x`
FROM `df`
con <- simulate_postgres()
lazy_frame(x = c(10, 20, 30), z = c(1, 2, 3), con = con) |> 
  window_order(z) |> 
  mutate(
    sum_x = cumsum(x),
    mean_x = cummean(x),
    lag_x = lag(x), 
    lead_x = lead(x)
  ) |> 
  show_query()
<SQL>
SELECT
  `df`.*,
  SUM(`x`) OVER `win1` AS `sum_x`,
  AVG(`x`) OVER `win1` AS `mean_x`,
  LAG(`x`, 1, NULL) OVER `win2` AS `lag_x`,
  LEAD(`x`, 1, NULL) OVER `win2` AS `lead_x`
FROM `df`
WINDOW
  `win1` AS (ORDER BY `z` ROWS UNBOUNDED PRECEDING),
  `win2` AS (ORDER BY `z`)
lazy_frame(x = c(10, 20), y = c("a", "b"), z = c(1, 2), con = con) |>
  window_order(z) |> 
  group_by(y) |> 
  mutate(
    sum_x = cumsum(x),
    mean_x = cummean(x),
    lag_x = lag(x), 
    lead_x = lead(x)
  ) |> 
  show_query()
<SQL>
SELECT
  `df`.*,
  SUM(`x`) OVER `win1` AS `sum_x`,
  AVG(`x`) OVER `win1` AS `mean_x`,
  LAG(`x`, 1, NULL) OVER `win2` AS `lag_x`,
  LEAD(`x`, 1, NULL) OVER `win2` AS `lead_x`
FROM `df`
WINDOW
  `win1` AS (PARTITION BY `y` ORDER BY `z` ROWS UNBOUNDED PRECEDING),
  `win2` AS (PARTITION BY `y` ORDER BY `z`)
con <- simulate_mssql()
lazy_frame(x = c(10, 20, 30), z = c(1, 2, 3), con = con) |> 
  window_order(z) |> 
  mutate(
    sum_x = cumsum(x),
    mean_x = cummean(x),
    lag_x = lag(x), 
    lead_x = lead(x)
  ) |> 
  show_query()
<SQL>
SELECT
  `df`.*,
  SUM(`x`) OVER (ORDER BY `z` ROWS UNBOUNDED PRECEDING) AS `sum_x`,
  AVG(`x`) OVER (ORDER BY `z` ROWS UNBOUNDED PRECEDING) AS `mean_x`,
  LAG(`x`, 1, NULL) OVER (ORDER BY `z`) AS `lag_x`,
  LEAD(`x`, 1, NULL) OVER (ORDER BY `z`) AS `lead_x`
FROM `df`
lazy_frame(x = c(10, 20), y = c("a", "b"), z = c(1, 2), con = con) |>
  window_order(z) |> 
  group_by(y) |> 
  mutate(
    sum_x = cumsum(x),
    mean_x = cummean(x),
    lag_x = lag(x), 
    lead_x = lead(x)
  ) |> 
  show_query()
<SQL>
SELECT
  `df`.*,
  SUM(`x`) OVER (PARTITION BY `y` ORDER BY `z` ROWS UNBOUNDED PRECEDING) AS `sum_x`,
  AVG(`x`) OVER (PARTITION BY `y` ORDER BY `z` ROWS UNBOUNDED PRECEDING) AS `mean_x`,
  LAG(`x`, 1, NULL) OVER (PARTITION BY `y` ORDER BY `z`) AS `lag_x`,
  LEAD(`x`, 1, NULL) OVER (PARTITION BY `y` ORDER BY `z`) AS `lead_x`
FROM `df`
con <- simulate_redshift()
lazy_frame(x = c(10, 20, 30), z = c(1, 2, 3), con = con) |> 
  window_order(z) |> 
  mutate(
    sum_x = cumsum(x),
    mean_x = cummean(x),
    lag_x = lag(x), 
    lead_x = lead(x)
  ) |> 
  show_query()
<SQL>
SELECT
  `df`.*,
  SUM(`x`) OVER (ORDER BY `z` ROWS UNBOUNDED PRECEDING) AS `sum_x`,
  AVG(`x`) OVER (ORDER BY `z` ROWS UNBOUNDED PRECEDING) AS `mean_x`,
  LAG(`x`, 1) OVER (ORDER BY `z`) AS `lag_x`,
  LEAD(`x`, 1) OVER (ORDER BY `z`) AS `lead_x`
FROM `df`
lazy_frame(x = c(10, 20), y = c("a", "b"), z = c(1, 2), con = con) |>
  window_order(z) |> 
  group_by(y) |> 
  mutate(
    sum_x = cumsum(x),
    mean_x = cummean(x),
    lag_x = lag(x), 
    lead_x = lead(x)
  ) |> 
  show_query()
<SQL>
SELECT
  `df`.*,
  SUM(`x`) OVER (PARTITION BY `y` ORDER BY `z` ROWS UNBOUNDED PRECEDING) AS `sum_x`,
  AVG(`x`) OVER (PARTITION BY `y` ORDER BY `z` ROWS UNBOUNDED PRECEDING) AS `mean_x`,
  LAG(`x`, 1) OVER (PARTITION BY `y` ORDER BY `z`) AS `lag_x`,
  LEAD(`x`, 1) OVER (PARTITION BY `y` ORDER BY `z`) AS `lead_x`
FROM `df`
con <- simulate_snowflake()
lazy_frame(x = c(10, 20, 30), z = c(1, 2, 3), con = con) |> 
  window_order(z) |> 
  mutate(
    sum_x = cumsum(x),
    mean_x = cummean(x),
    lag_x = lag(x), 
    lead_x = lead(x)
  ) |> 
  show_query()
<SQL>
SELECT
  `df`.*,
  SUM(`x`) OVER (ORDER BY `z` ROWS UNBOUNDED PRECEDING) AS `sum_x`,
  AVG(`x`) OVER (ORDER BY `z` ROWS UNBOUNDED PRECEDING) AS `mean_x`,
  LAG(`x`, 1, NULL) OVER (ORDER BY `z`) AS `lag_x`,
  LEAD(`x`, 1, NULL) OVER (ORDER BY `z`) AS `lead_x`
FROM `df`
lazy_frame(x = c(10, 20), y = c("a", "b"), z = c(1, 2), con = con) |>
  window_order(z) |> 
  group_by(y) |> 
  mutate(
    sum_x = cumsum(x),
    mean_x = cummean(x),
    lag_x = lag(x), 
    lead_x = lead(x)
  ) |> 
  show_query()
<SQL>
SELECT
  `df`.*,
  SUM(`x`) OVER (PARTITION BY `y` ORDER BY `z` ROWS UNBOUNDED PRECEDING) AS `sum_x`,
  AVG(`x`) OVER (PARTITION BY `y` ORDER BY `z` ROWS UNBOUNDED PRECEDING) AS `mean_x`,
  LAG(`x`, 1, NULL) OVER (PARTITION BY `y` ORDER BY `z`) AS `lag_x`,
  LEAD(`x`, 1, NULL) OVER (PARTITION BY `y` ORDER BY `z`) AS `lead_x`
FROM `df`
con <- simulate_spark_sql()
lazy_frame(x = c(10, 20, 30), z = c(1, 2, 3), con = con) |> 
  window_order(z) |> 
  mutate(
    sum_x = cumsum(x),
    mean_x = cummean(x),
    lag_x = lag(x), 
    lead_x = lead(x)
  ) |> 
  show_query()
<SQL>
SELECT
  `df`.*,
  SUM(`x`) OVER `win1` AS `sum_x`,
  AVG(`x`) OVER `win1` AS `mean_x`,
  LAG(`x`, 1, NULL) OVER `win2` AS `lag_x`,
  LEAD(`x`, 1, NULL) OVER `win2` AS `lead_x`
FROM `df`
WINDOW
  `win1` AS (ORDER BY `z` ROWS UNBOUNDED PRECEDING),
  `win2` AS (ORDER BY `z`)
lazy_frame(x = c(10, 20), y = c("a", "b"), z = c(1, 2), con = con) |>
  window_order(z) |> 
  group_by(y) |> 
  mutate(
    sum_x = cumsum(x),
    mean_x = cummean(x),
    lag_x = lag(x), 
    lead_x = lead(x)
  ) |> 
  show_query()
<SQL>
SELECT
  `df`.*,
  SUM(`x`) OVER `win1` AS `sum_x`,
  AVG(`x`) OVER `win1` AS `mean_x`,
  LAG(`x`, 1, NULL) OVER `win2` AS `lag_x`,
  LEAD(`x`, 1, NULL) OVER `win2` AS `lead_x`
FROM `df`
WINDOW
  `win1` AS (PARTITION BY `y` ORDER BY `z` ROWS UNBOUNDED PRECEDING),
  `win2` AS (PARTITION BY `y` ORDER BY `z`)

Although arrange() and window_order() both involve ordering, they serve different purposes.

  • arrange(): changes the order of the final output of a table, by ordering it as the user specified.

  • window_order(): defines the order within window functions. It controlls how functions (e.g., lag(), lead(), rank(), cumsum()) are applied across rows.

In conclusion, for all dialects, arrange() changes the output row order, while window_order() changes how window functions calculate values.

3.8 Calculating quantiles, including the median

So far we’ve seen that we can perform various data manipulations and calculate summary statistics for different database management systems using the same R code. Although the translated SQL has been different, the databases all supported similar approaches to perform these queries.

A case where this is not true is when we are interested in summarising distributions of the data and estimating quantiles. For example, let’s take estimating the median as an example. Some databases only support calculating the median as an aggregation function similar to how min, mean, and max were calculated above. However, some others only support it as a window function like lead and lag above. Unfortunately, this means that for some databases, quantiles can only be calculated using the summarise aggregation approach, while in others only the mutate window approach can be used.

con <- simulate_duckdb()
lazy_frame(x = c(1, 2), con = con) |>
  summarise(median = median(x, na.rm = TRUE)) |> 
  show_query()
<SQL>
SELECT MEDIAN(`x`) AS `median`
FROM `df`
lazy_frame(x = c(1, 2), con = con) |>
  mutate(median = median(x, na.rm = TRUE)) |> 
  show_query()
<SQL>
SELECT `df`.*, MEDIAN(`x`) OVER () AS `median`
FROM `df`
con <- simulate_postgres()
lazy_frame(x = c(1, 2), con = con) |>
  summarise(median = median(x, na.rm = TRUE)) |> 
  show_query()
<SQL>
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY `x`) AS `median`
FROM `df`
lazy_frame(x = c(1, 2), con = con) |>
  mutate(median = median(x, na.rm = TRUE)) |> 
  show_query()
Error in `median()`:
! Translation of `median()` in `mutate()` is not supported for
  PostgreSQL.
ℹ Use a combination of `summarise()` and `left_join()` instead:
  `df %>% left_join(summarise(<col> = median(x, na.rm = TRUE)))`.
con <- simulate_mssql()
lazy_frame(x = c(1, 2), con = con) |>
  summarise(median = median(x, na.rm = TRUE)) |> 
  show_query()
Error in `median()`:
! Translation of `median()` in `summarise()` is not supported for SQL
  Server.
ℹ Use a combination of `distinct()` and `mutate()` for the same result:
  `mutate(<col> = median(x, na.rm = TRUE)) %>% distinct(<col>)`
lazy_frame(x = c(1, 2), con = con) |>
  mutate(median = median(x, na.rm = TRUE)) |> 
  show_query()
<SQL>
SELECT
  `df`.*,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY `x`) OVER () AS `median`
FROM `df`
con <- simulate_redshift()
lazy_frame(x = c(1, 2), con = con) |>
  summarise(median = median(x, na.rm = TRUE)) |> 
  show_query()
<SQL>
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY `x`) AS `median`
FROM `df`
lazy_frame(x = c(1, 2), con = con) |>
  mutate(median = median(x, na.rm = TRUE)) |> 
  show_query()
Error in `median()`:
! Translation of `median()` in `mutate()` is not supported for
  PostgreSQL.
ℹ Use a combination of `summarise()` and `left_join()` instead:
  `df %>% left_join(summarise(<col> = median(x, na.rm = TRUE)))`.
con <- simulate_snowflake()
lazy_frame(x = c(1, 2), con = con) |>
  summarise(median = median(x, na.rm = TRUE)) |> 
  show_query()
<SQL>
SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY `x`) AS `median`
FROM `df`
lazy_frame(x = c(1, 2), con = con) |>
  mutate(median = median(x, na.rm = TRUE)) |> 
  show_query()
<SQL>
SELECT
  `df`.*,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY `x`) OVER () AS `median`
FROM `df`
con <- simulate_spark_sql()
lazy_frame(x = c(1, 2), con = con) |>
  summarise(median = median(x, na.rm = TRUE)) |> 
  show_query()
<SQL>
SELECT MEDIAN(`x`) AS `median`
FROM `df`
lazy_frame(x = c(1, 2), con = con) |>
  mutate(median = median(x, na.rm = TRUE)) |> 
  show_query()
<SQL>
SELECT `df`.*, MEDIAN(`x`) OVER () AS `median`
FROM `df`