如何在R中复现SQL式左连接以获取保单前两年已赚保费

精通SQL但对R完全陌生，公司要求用Athena处理大型保险数据集，但Athena性能不足，添加两列时崩溃。已在R中获取数据集CW_Data，包含字段如Policy_Number、Policy_Effective_Date、Policy_Earned_Premium，需要实现SQL中左连接逻辑：匹配相同Policy_Number，并获取当前记录Policy_Effective_Date减1年、减2年对应的Policy_Earned_Premium，分别作为Policy_Prior_Year_Earned_Premium和Policy_Second_Prior_Year_Earned_Premium。

原Athena中无法运行的简化SQL代码：

All_Info as (
Select
       PC.Policy_Number
      ,PC.Policy_Effective_Date
      ,PC.Policy_EP
from Policy_Characteristics as PC

       left join Almost_All_Info as AAI
       on  AAI.Policy_Number         = PC.Policy_Number
       and AAI.Policy_Effective_Date = date_add('year', -1, PC.Policy_Effective_Date)
       
       left join All_Segments as AST
       on  AST.Policy_Number         = PC.Policy_Number
       and AST.Policy_Effective_Date = date_add('year', -2, PC.Policy_Effective_Date)
Group by 
       PC.Policy_Number
      ,PC.Policy_Effective_Date
      ,PC.Policy_EP
)

推荐使用dplyr包（语法与SQL高度契合，适合SQL用户快速上手）结合lubridate包（处理日期）实现，步骤如下：

1. 安装并加载依赖包 #

首次运行需安装包，之后直接加载即可：

# 安装依赖包（仅首次运行）
install.packages(c("dplyr", "lubridate"))

# 加载包
library(dplyr)
library(lubridate)

2. 构建关联数据集并执行左连接 #

对应SQL中的两次LEFT JOIN逻辑，先分别生成偏移1年、2年的数据集，再与原数据集关联：

# 生成偏移1年的数据集：保存保单号、偏移后的日期、对应保费
prior_year_data <- CW_Data %>%
  mutate(Prior_Effective_Date = Policy_Effective_Date %m-% years(1)) %>%
  select(Policy_Number, Prior_Effective_Date, Policy_Prior_Year_Earned_Premium = Policy_Earned_Premium)

# 生成偏移2年的数据集：保存保单号、偏移后的日期、对应保费
second_prior_year_data <- CW_Data %>%
  mutate(Second_Prior_Effective_Date = Policy_Effective_Date %m-% years(2)) %>%
  select(Policy_Number, Second_Prior_Effective_Date, Policy_Second_Prior_Year_Earned_Premium = Policy_Earned_Premium)

# 原数据集左连接两个偏移数据集，对应SQL的ON条件
result <- CW_Data %>%
  left_join(prior_year_data, 
            by = c("Policy_Number" = "Policy_Number", 
                   "Policy_Effective_Date" = "Prior_Effective_Date")) %>%
  left_join(second_prior_year_data, 
            by = c("Policy_Number" = "Policy_Number", 
                   "Policy_Effective_Date" = "Second_Prior_Effective_Date"))

3. 分组聚合（可选，对应SQL的GROUP BY） #

如果需要对结果去重或聚合，可添加分组逻辑：

result_aggregated <- result %>%
  group_by(Policy_Number, Policy_Effective_Date, Policy_Earned_Premium) %>%
  summarise(
    Policy_Prior_Year_Earned_Premium = first(Policy_Prior_Year_Earned_Premium),
    Policy_Second_Prior_Year_Earned_Premium = first(Policy_Second_Prior_Year_Earned_Premium),
    .groups = "drop"
  )

关键说明 #

• %m-% years(1)：精确计算日期减1年，对应SQL的date_add('year', -1, ...)
• left_join：行为与SQL的LEFT JOIN完全一致，无匹配记录时对应列显示NA
• select重命名字段：对应SQL中字段别名的设置

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