Closed
Description
library(dplyr, warn.conflicts = FALSE)
library(DBI)
df <-
expand.grid(id = LETTERS[1:6], time = 1:10) %>%
arrange(id) %>%
mutate(val = rnorm(n = n()))
df %>%
group_by(id) %>%
arrange(time) %>%
mutate(cumavg = cummean(val)) %>%
arrange(id, time)
#> # A tibble: 60 x 4
#> # Groups: id [6]
#> id time val cumavg
#> <fct> <int> <dbl> <dbl>
#> 1 A 1 0.994 0.994
#> 2 A 2 0.250 0.622
#> 3 A 3 0.128 0.457
#> 4 A 4 0.848 0.555
#> 5 A 5 0.429 0.530
#> 6 A 6 -2.09 0.0941
#> 7 A 7 0.709 0.182
#> 8 A 8 2.13 0.426
#> 9 A 9 0.893 0.478
#> 10 A 10 -1.03 0.327
#> # ... with 50 more rows
pg <- dbConnect(RPostgres::Postgres())
df_pg <- copy_to(pg, df)
df_pg %>%
group_by(id) %>%
arrange(time) %>%
mutate(cumavg = cummean(val)) %>%
arrange(id, time) %>%
show_query()
#> <SQL>
#> SELECT "id", "time", "val", mean("val") OVER (PARTITION BY "id" ORDER BY "time" ROWS UNBOUNDED PRECEDING) AS "cumavg"
#> FROM (SELECT *
#> FROM "df"
#> ORDER BY "time") "qnxnacfsyq"
#> ORDER BY "id", "time"
Created on 2018-09-10 by the reprex package (v0.2.0).