## dplyover examples in dplyr and sql
## I use dplyr::across() somewhat unnecessarily but is to show that these translate over fine
library(dbplyr)
library(dplyr, warn.conflicts = FALSE)
con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
aaa <- tibble(a = 1:25)
copy_to(con, aaa)
aaa2 <- tbl(con, "aaa")
copy_to(con, iris)
iris2 <- tbl(con, "iris")
# over() example
over_ex <- aaa2 %>%
mutate(across(a, list(lag1 = lag,
lead1 = lead,
lag2 = ~lag(.x, 2),
lead2 = ~lead(.x, 2),
lag3 = ~lag(.x, 3),
lead3 = ~lead(.x, 3))))
over_ex
#> # Source: lazy query [?? x 7]
#> # Database: sqlite 3.30.1 [:memory:]
#> a a_lag1 a_lead1 a_lag2 a_lead2 a_lag3 a_lead3
#> <int> <int> <int> <int> <int> <int> <int>
#> 1 1 NA 2 NA 3 NA 4
#> 2 2 1 3 NA 4 NA 5
#> 3 3 2 4 1 5 NA 6
#> 4 4 3 5 2 6 1 7
#> 5 5 4 6 3 7 2 8
#> 6 6 5 7 4 8 3 9
#> 7 7 6 8 5 9 4 10
#> 8 8 7 9 6 10 5 11
#> 9 9 8 10 7 11 6 12
#> 10 10 9 11 8 12 7 13
#> # ... with more rows
show_query(over_ex)
#> <SQL>
#> SELECT `a`, LAG(`a`, 1, NULL) OVER () AS `a_lag1`, LEAD(`a`, 1, NULL) OVER () AS `a_lead1`, LAG(`a`, 2, NULL) OVER () AS `a_lag2`, LEAD(`a`, 2.0, NULL) OVER () AS `a_lead2`, LAG(`a`, 3, NULL) OVER () AS `a_lag3`, LEAD(`a`, 3.0, NULL) OVER () AS `a_lead3`
#> FROM `aaa`
# crossover() example
crossover_ex <- iris2 %>%
transmute(across(
starts_with("sepal"),
list(
lag1 = lag,
lag2 = ~ lag(.x, 2),
lag3 = ~ lag(.x, 3),
lag4 = ~ lag(.x, 4),
lag5 = ~ lag(.x, 5)
)))
crossover_ex %>%
glimpse()
#> Rows: ??
#> Columns: 10
#> Database: sqlite 3.30.1 [:memory:]
#> $ Sepal.Length_lag1 <dbl> NA, 5.1, 4.9, 4.7, 4.6, 5.0, 5.4, 4.6, 5.0, 4.4, 4.9~
#> $ Sepal.Length_lag2 <dbl> NA, NA, 5.1, 4.9, 4.7, 4.6, 5.0, 5.4, 4.6, 5.0, 4.4,~
#> $ Sepal.Length_lag3 <dbl> NA, NA, NA, 5.1, 4.9, 4.7, 4.6, 5.0, 5.4, 4.6, 5.0, ~
#> $ Sepal.Length_lag4 <dbl> NA, NA, NA, NA, 5.1, 4.9, 4.7, 4.6, 5.0, 5.4, 4.6, 5~
#> $ Sepal.Length_lag5 <dbl> NA, NA, NA, NA, NA, 5.1, 4.9, 4.7, 4.6, 5.0, 5.4, 4.~
#> $ Sepal.Width_lag1 <dbl> NA, 3.5, 3.0, 3.2, 3.1, 3.6, 3.9, 3.4, 3.4, 2.9, 3.1~
#> $ Sepal.Width_lag2 <dbl> NA, NA, 3.5, 3.0, 3.2, 3.1, 3.6, 3.9, 3.4, 3.4, 2.9,~
#> $ Sepal.Width_lag3 <dbl> NA, NA, NA, 3.5, 3.0, 3.2, 3.1, 3.6, 3.9, 3.4, 3.4, ~
#> $ Sepal.Width_lag4 <dbl> NA, NA, NA, NA, 3.5, 3.0, 3.2, 3.1, 3.6, 3.9, 3.4, 3~
#> $ Sepal.Width_lag5 <dbl> NA, NA, NA, NA, NA, 3.5, 3.0, 3.2, 3.1, 3.6, 3.9, 3.~
show_query(crossover_ex)
#> <SQL>
#> SELECT LAG(`Sepal.Length`, 1, NULL) OVER () AS `Sepal.Length_lag1`, LAG(`Sepal.Length`, 2, NULL) OVER () AS `Sepal.Length_lag2`, LAG(`Sepal.Length`, 3, NULL) OVER () AS `Sepal.Length_lag3`, LAG(`Sepal.Length`, 4, NULL) OVER () AS `Sepal.Length_lag4`, LAG(`Sepal.Length`, 5, NULL) OVER () AS `Sepal.Length_lag5`, LAG(`Sepal.Width`, 1, NULL) OVER () AS `Sepal.Width_lag1`, LAG(`Sepal.Width`, 2, NULL) OVER () AS `Sepal.Width_lag2`, LAG(`Sepal.Width`, 3, NULL) OVER () AS `Sepal.Width_lag3`, LAG(`Sepal.Width`, 4, NULL) OVER () AS `Sepal.Width_lag4`, LAG(`Sepal.Width`, 5, NULL) OVER () AS `Sepal.Width_lag5`
#> FROM `iris`
# across2() example
across2_ex <- iris2 %>%
transmute(sepal_product = Sepal.Length * Sepal.Width,
sepal_sum = Sepal.Length + Sepal.Width,
petal_product = Petal.Length * Petal.Width,
petal_sum = Petal.Length + Petal.Width)
across2_ex
#> # Source: lazy query [?? x 4]
#> # Database: sqlite 3.30.1 [:memory:]
#> sepal_product sepal_sum petal_product petal_sum
#> <dbl> <dbl> <dbl> <dbl>
#> 1 17.8 8.6 0.28 1.6
#> 2 14.7 7.9 0.28 1.6
#> 3 15.0 7.9 0.26 1.5
#> 4 14.3 7.7 0.3 1.7
#> 5 18 8.6 0.28 1.6
#> 6 21.1 9.3 0.68 2.1
#> 7 15.6 8 0.42 1.7
#> 8 17 8.4 0.3 1.7
#> 9 12.8 7.3 0.28 1.6
#> 10 15.2 8 0.15 1.6
#> # ... with more rows
show_query(across2_ex)
#> <SQL>
#> SELECT `Sepal.Length` * `Sepal.Width` AS `sepal_product`, `Sepal.Length` + `Sepal.Width` AS `sepal_sum`, `Petal.Length` * `Petal.Width` AS `petal_product`, `Petal.Length` + `Petal.Width` AS `petal_sum`
#> FROM `iris`Created on 2021-10-05 by the reprex package (v2.0.0)