edgararuiz-zz · September 22, 2018 22:49 · Sep 22, 2018
diff --git a/purr_dbplyr_union.R b/purr_dbplyr_union.R
@@ -0,0 +1,51 @@
+library(dbplyr, warn.conflicts = FALSE)
+library(dplyr, warn.conflicts = FALSE)
+library(purrr, warn.conflicts = FALSE)
+library(DBI, warn.conflicts = FALSE)
+library(rlang, warn.conflicts = FALSE)
+
+con <- DBI::dbConnect(RSQLite::SQLite(), path = ":dbname:")
+
+db_mtcars <- copy_to(con, mtcars)
+
+cyls <- c(4, 6, 8)
+
+all <- cyls %>%
+  map(~{
+    db_mtcars %>%
+      filter(cyl == .x) %>%
+      summarise(mpg = mean(mpg, na.rm = TRUE)
+      )
+  }) %>%
+  reduce(function(x, y) union(x, y)) 
+
+all
+#> # Source:   lazy query [?? x 1]
+#> # Database: sqlite 3.22.0 []
+#>     mpg
+#>   <dbl>
+#> 1  15.1
+#> 2  19.7
+#> 3  26.7
+
+show_query(all)
+#> <SQL>
+#> SELECT AVG(`mpg`) AS `mpg`
+#> FROM (SELECT *
+#> FROM (SELECT *
+#> FROM `mtcars`)
+#> WHERE (`cyl` = 4.0))
+#> UNION
+#> SELECT AVG(`mpg`) AS `mpg`
+#> FROM (SELECT *
+#> FROM (SELECT *
+#> FROM `mtcars`)
+#> WHERE (`cyl` = 6.0))
+#> UNION
+#> SELECT AVG(`mpg`) AS `mpg`
+#> FROM (SELECT *
+#> FROM (SELECT *
+#> FROM `mtcars`)
+#> WHERE (`cyl` = 8.0))
+
+dbDisconnect(con)