在R中按ID统计另一表中处于指定日期区间内的行数

首先需要确保日期列是Date类型（而非字符型），否则无法正确比较日期。以下是两种实现方式，完全匹配你提供的DAX逻辑：

步骤1：准备示例数据 #

# 转换日期格式为Date类型（原数据为dd/mm/yyyy格式）
table1 <- data.frame(
  contact_number = c(123, 1234),
  start_date = as.Date(c("01/01/2023", "01/01/2022"), format = "%d/%m/%Y"),
  end_date = as.Date(c("01/01/2024", "01/01/2023"), format = "%d/%m/%Y")
)

table2 <- data.frame(
  contact_number = c(123, 123, 1234),
  visit_type = c(1, 2, 2),
  date = as.Date(c("02/01/2023", "05/01/2023", "05/01/2022"), format = "%d/%m/%Y")
)

方法1：使用dplyr（Tidyverse风格） #

这是高效的向量化实现方式：

library(dplyr)

# 关联表并筛选符合条件的记录，然后分组计数
result <- table1 %>%
  # 按contact_number关联两个表
  left_join(table2, by = "contact_number") %>%
  # 匹配DAX的左闭右开日期条件
  filter(date >= start_date & date < end_date) %>%
  # 按表1的核心字段分组，统计行数
  group_by(contact_number, start_date, end_date) %>%
  summarise(visits = n(), .groups = "drop") %>%
  # 右连接回原表，确保无匹配记录的contact_number显示0
  right_join(table1, by = c("contact_number", "start_date", "end_date")) %>%
  mutate(visits = replace_na(visits, 0)) %>%
  select(names(table1), visits) # 对齐期望输出的列顺序

方法2：Base R实现（完全对应DAX逐行逻辑） #

如果需要严格复刻DAX的逐行筛选计算逻辑，可使用循环：

# 初始化visits列
table1$visits <- 0

# 遍历table1的每一行，对应DAX逐行计算逻辑
for (i in seq(nrow(table1))) {
  current_cn <- table1$contact_number[i]
  current_start <- table1$start_date[i]
  current_end <- table1$end_date[i]
  
  # 筛选table2中符合条件的行并计数，对应DAX的FILTER+COUNTROWS
  table1$visits[i] <- sum(
    table2$contact_number == current_cn &
    table2$date >= current_start &
    table2$date < current_end
  )
}

result_base <- table1

验证结果 #

两种方法都会生成你期望的输出：

与DAX逻辑的对应关系 #

• DAX中的CALCULATE(COUNTROWS('Table 2'), FILTER(...))对应R中筛选匹配contact_number且日期在区间内的行，然后计数
• 严格保留DAX的左闭右开日期条件：date >= start_date且date < end_date

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