I would like to use dbplyr/dplyr to create a group-level aggregate variable. Here is the gist of the command:
q = tbl_copy %>%
group_by(group_var) %>%
mutate(x_agg = min(x))
But this produces the following error:
Error: Window function `min()` is not supported by this database
Oddly, when I use the summarise() verb, min() works just fine.
q = tbl_copy %>%
group_by(group_var) %>%
summarise(x_agg = min(x))
q %>% show_query()
<SQL>
SELECT `group_var`, `x`, MIN(`x`) AS `x_agg`
FROM `my_table`
GROUP BY `group_var`
What am I missing? How do I use group_by() with mutate() on a copy of a MySQL table?
UPDATE: A REPRODUCIBLE EXAMPLE
> con <- DBI::dbConnect(RSQLite::SQLite(), ":memory:")
> copy_to(con, mtcars)
>
> mtcars2 <- tbl(con, "mtcars")
> mtcars2 %>%
+ select(mpg,cyl) %>%
+ group_by(cyl) %>%
+ mutate(mpg_min = min(mpg))
Error: Window function `min()` is not supported by this database