[R] Support mutate/summarize with implicit join

[asfimport]

mtcars %>%
  group_by(cyl) %>%
  mutate(x = hp - mean(hp)

essentially means something like

mtcars %>%
  left_join(mtcars %>%
    group_by(cyl) %>%
    summarize(tmp = mean(hp))
  ) %>%
  mutate(x = hp - tmp) %>%
  select(-tmp)

Apparently you can do the same inside summarize() too (though IDK if that's behavior we want to encourage). Once we can do joins, we can support these queries.

Reporter: Neal Richardson / @nealrichardson

Related issues:

• [R] Warning/error if dplyr::summarise() called when column is renamed but the original should be displayed in output too (is duplicated by)

_{Note: This issue was originally created as ARROW-13926. Please see the migration documentation for further details.}

[asfimport]

Ian Cook / @ianmcook: FWIW, this is perhaps better construed as an implicit window function (i.e. an OVER expression in SQL). When you do this type of operation with dbplyr, the SQL it generates uses an OVER expression:

mtcars_db <- dbplyr::memdb_frame(mtcars)
mtcars_db %>%
  group_by(cyl) %>%
  transmute(x = hp - mean(hp)) %>%
  show_query()

#> <SQL>
#> SELECT `hp` - AVG(`hp`) OVER (PARTITION BY `cyl`) AS `x`
#> FROM `dbplyr_002`