Calculating accumulations in advance an querying multiple tables (which are variations of the raw data) sound like an unnecessarily complicated workflow, once your raw data is imported into R, performing aggregations and data wrangling is pretty simple, see this example.
library(dplyr)
library(lubridate)
library(tibbletime)
# Sample data mimicking your DB
set.seed(1234)
sample_df <- data.frame(
date = seq.Date(as.Date("2019-01-01"), as.Date("2019-04-30"), 1),
a = rnorm(120),
b = rnorm(120),
c = rnorm(120)
)
# Monthly aggregation
sample_df %>%
as_tbl_time(date) %>%
collapse_by("month", side = "start", clean = TRUE) %>%
group_by(date) %>%
summarise_all(sum) %>%
mutate(month = month(date, label = TRUE, abbr = FALSE)) %>%
select(month, everything(), -date)
#> # A tibble: 4 x 4
#> month a b c
#> <ord> <dbl> <dbl> <dbl>
#> 1 January -7.79 2.88 3.39
#> 2 February -16.5 4.20 4.57
#> 3 March 3.00 4.31 0.800
#> 4 April 3.35 2.17 -1.09
# Quarterly aggregation
sample_df %>%
as_tbl_time(date) %>%
collapse_by("quarterly", side = "start", clean = TRUE) %>%
group_by(date) %>%
summarise_all(sum) %>%
mutate(quarter = quarter(date)) %>%
select(quarter, everything(), -date)
#> # A tibble: 2 x 4
#> quarter a b c
#> <int> <dbl> <dbl> <dbl>
#> 1 1 -21.3 11.4 8.76
#> 2 2 3.35 2.17 -1.09
Created on 2019-09-06 by the reprex package (v0.3.0.9000)