Support 'Range between' along with 'rows between' in window_frame

[talegari]

Right now, dbplyr::window_frame checks if from and to are numbers.

  check_number_whole(from, allow_infinite = TRUE)
  check_number_whole(to, allow_infinite = TRUE)

It would be great to have support interval arguments for from/to so that dbplyr::window_frame will support 'Range between' queries. Here is an modified example from duckdb's window functions page:

SELECT "plant", "date",
    min("mwh") OVER seven AS "min_mwh",
FROM "history"
WINDOW seven AS (
    PARTITION BY "plant"
    ORDER BY "date" ASC
    RANGE BETWEEN INTERVAL 1 MONTHS PRECEDING
              AND INTERVAL 3 DAYS FOLLOWING)
ORDER BY 1, 2;

Since, lubridate provides period/interval objects like lubridate::days(), dbplyr should ideally translate the below code to the sql code above:

history_tbl |>
  group_by(plant) |>
  window_order(date) |>
  window_frame(from = months(1), to = days(3)) |> # slider like syntax
  mutate(min_mwh = min(mwh)) |>
  ungroup() |>
  arrange(plant, date)

[hadley]

I don't think this is a simple change to window_frame() because you can also use numeric values in RANGE mode. So this would likely need to either be a separate function or we'd need to add a mode argument to window_frame().

• Duckdb docs: https://duckdb.org/docs/sql/window_functions#range-framing
• PostgreSQL docs: https://www.postgresql.org/docs/current/sql-expressions.html#SYNTAX-WINDOW-FUNCTIONS. Note example of RANGE BETWEEN '1 day' PRECEDING AND '10 days' FOLLOWING so uses a different syntax to duckdb. (But that might just be the example; see https://www.postgresql.org/docs/current/functions-datetime.html for examples using INTERVAL.