如何统计过去三年每月存在多份同时生效制裁的客户数量

我来帮你解决这个统计每月存在两份及以上同时生效制裁客户的问题。先明确核心需求：我们的客户制裁记录系统中，部分客户存在多份同时生效的制裁（即至少两条记录的时间范围有重叠，判断标准为某条记录的start_date早于另一条的end_date），需要统计2019-2022年每个月这类客户的数量。

样本数据说明 #

你提供的样本数据生成代码如下，它创建了200个客户、每个客户5条随机时间的制裁记录，同时生成了start_month_year字段：

set.seed(147)
sanc <- data.frame(
  client = rep(1:200, each = 5),
  start_date = sample(seq(as.Date("2019-01-01"), as.Date("2022-01-01"), by = "day"), 1000),
  end_date = sample(seq(as.Date("2019-01-01"), as.Date("2022-01-01"), by = "day"), 1000)
)
sanc$start_month_year = format(as.Date(sanc$start_date, "%Y-%m-%d"), "%Y-%m")

注：样本中end_date为随机生成，可能出现end_date早于对应start_date的情况，这是样本特性，无需额外处理。

解决方案思路与代码 #

你的初始思路方向正确，我将其细化为可落地的R代码实现，同时处理跨月重叠的场景：

library(dplyr)
library(lubridate)

# 步骤1：识别存在多份同时生效制裁的客户
overlap_clients <- sanc %>%
  group_by(client) %>%
  arrange(start_date) %>%
  # 检查当前记录的start_date是否早于前一条的end_date（即存在时间重叠）
  mutate(has_overlap = any(start_date < lag(end_date, default = first(start_date) - 1))) %>%
  filter(has_overlap) %>%
  ungroup() %>%
  distinct(client)

# 步骤2：统计每个月有重叠情况的客户数量
monthly_stats <- sanc %>%
  inner_join(overlap_clients, by = "client") %>%
  rowwise() %>%
  # 生成每条制裁记录覆盖的所有月份（解决跨月重叠的统计问题）
  mutate(covered_months = list(seq(floor_date(start_date, "month"), 
                                   floor_date(end_date, "month"), 
                                   by = "month"))) %>%
  unnest(covered_months) %>%
  mutate(month_year = format(covered_months, "%Y-%m")) %>%
  group_by(month_year) %>%
  # 统计每个月的独立客户数
  summarise(client_count = n_distinct(client)) %>%
  arrange(month_year)

# 转换为你期望的"MM-YYYY"格式并输出
monthly_stats %>%
  mutate(month_year = format(as.Date(month_year), "%m-%Y")) %>%
  arrange(as.Date(month_year, "%m-%Y")) %>%
  print(n = Inf)

代码逻辑解释 #

1. 识别重叠客户：按客户分组后，先对记录按start_date排序，再检查是否存在任意两条记录的时间范围重叠，筛选出有重叠情况的客户。
2. 生成覆盖月份：对每个重叠客户的每条制裁记录，生成其时间范围覆盖的所有月份，确保跨月的重叠情况也能被统计到对应月份中。
3. 月度统计：按月份分组，统计每个月有多少独立客户存在多份同时生效的制裁，最后调整月份格式为你需要的MM-YYYY样式。

内容的提问来源于stack exchange，提问作者Mus