按社区计算首次销售后三年内的年度累计销售额

需求与痛点 #

• 需求：按社区统计首次销售发生后三年内的年度累计销售额
• 痛点：现有数据每行对应唯一用户，包含Sale（销售标记，1为有销售、0为无销售）和Date_of_sale（销售日期，无销售则为NA）列，需补全无销售的年份，且仅保留首次销售后三年内的销售数据

数据样例 #

library(tibble)
dat <- tibble(
  Person = c(1, 2, 3, 4, 5, 6),
  Neighbourhood = c("XYZ", "XYZ", "XYZ", "XYZ", "ABC", "ABC"),
  Date_of_sale =  structure(c(17987, NA, 19275, 17564, 18052, NA), class = "Date"),
  Sale = c(1, 0, 1, 1, 1, 0)
)

期望输出 #

解决方案 #

使用dplyr做分组处理、lubridate处理日期逻辑，步骤如下：

1. 提取各社区的首次销售日期作为时间基准
2. 为每个有效销售记录匹配其相对于首次销售的年份，过滤超出三年的记录
3. 生成每个社区的三年时间序列，补全无销售年份的0值后计算累计销售额

完整代码：

library(dplyr)
library(lubridate)

# 1. 获取各社区首次销售日期
first_sale_dates <- dat %>%
  filter(Sale == 1) %>%
  group_by(Neighbourhood) %>%
  summarise(first_sale = min(Date_of_sale, na.rm = TRUE)) %>%
  ungroup()

# 2. 匹配销售记录所属年份，过滤三年外的数据
sales_with_year <- dat %>%
  filter(Sale == 1) %>%
  left_join(first_sale_dates, by = "Neighbourhood") %>%
  mutate(
    year_diff = interval(first_sale, Date_of_sale) %/% years(1) + 1,
    year_label = paste0("Year ", year_diff)
  ) %>%
  filter(year_diff <= 3) %>%
  group_by(Neighbourhood, year_label) %>%
  summarise(annual_sales = n()) %>%
  ungroup()

# 3. 补全无销售年份，计算累计销售额
result <- first_sale_dates %>%
  select(Neighbourhood) %>%
  expand_grid(year_label = paste0("Year ", 1:3)) %>%
  left_join(sales_with_year, by = c("Neighbourhood", "year_label")) %>%
  mutate(annual_sales = replace_na(annual_sales, 0)) %>%
  group_by(Neighbourhood) %>%
  arrange(year_label) %>%
  mutate(Cumulative_Sales = cumsum(annual_sales)) %>%
  select(Neighbourhood, Year = year_label, Cumulative_Sales) %>%
  ungroup()

# 查看结果
print(result)

运行后输出与期望一致：

# A tibble: 6 × 3
  Neighbourhood Year   Cumulative_Sales
  <chr>         <chr>             <int>
1 ABC           Year 1                1
2 ABC           Year 2                1
3 ABC           Year 3                1
4 XYZ           Year 1                1
5 XYZ           Year 2                2
6 XYZ           Year 3                2

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