merge_date_ranges_db: Merge connected portfolio periods in DuckDB

In insurancerating: Actuarial Tools for Insurance Pricing Models

Merge connected portfolio periods in DuckDB

Description

Construct the temporal portfolio reduction performed by merge_date_ranges() as a lazy DuckDB query. Overlapping or adjacent periods are merged inside DuckDB, so only the consolidated periods need to be copied into R.

Usage

merge_date_ranges_db(
  data,
  period_start,
  period_end,
  group_by,
  aggregate_cols = NULL,
  aggregate_fun = c("sum", "mean", "min", "max"),
  merge_gap_days = 1
)

Arguments

data	A lazy DuckDB table created with dplyr::tbl().
period_start	Character string naming the period-start column.
period_end	Character string naming the period-end column.
group_by	Character vector identifying the policy, risk or segment within which periods may be combined.
aggregate_cols	Optional character vector naming numeric columns to aggregate over each merged period.
aggregate_fun	Character string specifying the SQL aggregation for aggregate_cols: "sum", "mean", "min", or "max".
merge_gap_days	Non-negative whole number. The interpretation is the same as in merge_date_ranges(): 1 merges overlapping and directly adjacent periods, while 0 merges overlapping periods only.

Details

Merging connected intervals is a SQL gaps-and-islands calculation. The query uses ordered window functions to compare each start date with the latest preceding end date, assigns an interval identifier, and then aggregates each resulting interval.

Date arithmetic differs between database systems. This implementation is therefore deliberately restricted to DuckDB. It does not download the source table and does not create a permanent database table.

Value

A lazy DuckDB table. Use dbplyr::sql_render() to inspect the SQL or dplyr::collect() to import the consolidated periods into R.

Author(s)

Martin Haringa

See Also

merge_date_ranges(), rating_grid_db(), rating_grid()

Examples

## Not run: 
con <- DBI::dbConnect(duckdb::duckdb())
portfolio_db <- dplyr::tbl(con, "portfolio_periods")

periods_db <- merge_date_ranges_db(
  portfolio_db,
  period_start = "period_start",
  period_end = "period_end",
  group_by = c("policy_id", "coverage"),
  aggregate_cols = c("earned_exposure", "earned_premium")
)

periods <- dplyr::collect(periods_db)
DBI::dbDisconnect(con, shutdown = TRUE)

## End(Not run)

insurancerating documentation built on Sept. 1, 2026, 5:08 p.m.