优化基于不等式的Left Join查询（SAS SQL场景）

假设表结构如下：

• 表1（policy_table）：policy_id（保单ID）、state（州）、effective_date（保单生效日期）
• 表2（factor_table）：state（州）、factor_eff_date（因子生效日期）、factor_value（因子值）

方案一：数据步有序合并（百万级数据首选） #

利用SAS数据步的原生顺序处理优势，避免SQL不等式Join产生的大量中间结果，速度远超传统关联查询：

/* 先对两张表按【州+生效日期】排序，为合并做准备 */
proc sort data=policy_table out=policy_sorted;
    by state effective_date;
run;

proc sort data=factor_table out=factor_sorted;
    by state factor_eff_date;
run;

/* 逐行合并，跟踪每个州的最新有效因子 */
data policy_with_factor;
    merge policy_sorted (in=in_policy) factor_sorted;
    by state;
    /* 只保留保单记录 */
    if in_policy;
    /* 持续保留当前州的最新匹配因子 */
    retain current_factor;
    /* 当因子生效日期早于/等于保单生效日期时，更新当前因子 */
    if factor_eff_date <= effective_date then current_factor = factor_value;
    /* 切换到新州时，若没有匹配因子则重置为null */
    else if first.state then current_factor = .;
    /* 输出带最新因子的保单记录 */
    output;
    /* 换州前清空当前因子缓存 */
    if last.state then call missing(current_factor);
run;

方案二：SQL窗口函数+索引优化 #

如果偏好SQL写法，通过窗口函数筛选最新因子，配合索引大幅提升查找效率：

/* 给表2创建复合索引，加速州+日期的查找 */
proc datasets lib=work nolist;
    modify factor_table;
    index create state_factor_idx=(state factor_eff_date);
quit;

/* 用窗口函数标记每个州的最新因子，再关联保单表 */
proc sql;
    create table policy_with_factor as
    select 
        p.policy_id,
        p.state,
        p.effective_date,
        f.factor_value
    from policy_table p
    left join (
        select 
            state,
            factor_eff_date,
            factor_value,
            /* 按州分组，因子生效日期倒序排，最新的标记为1 */
            row_number() over (partition by state order by factor_eff_date desc) as rn
        from factor_table
    ) f
        on p.state = f.state
        and f.factor_eff_date <= p.effective_date
    where f.rn = 1 or f.rn is null; /* 只取最新匹配的因子，无匹配则返回null */
quit;

方案三：预处理因子区间表 #

适合因子更新不频繁的场景，提前将因子转化为生效区间，把范围匹配转化为更高效的区间关联：

/* 预处理表2，生成每个因子的生效时间区间 */
proc sort data=factor_table out=factor_sorted;
    by state factor_eff_date;
run;

data factor_interval;
    set factor_sorted;
    by state;
    /* 上一个因子的生效日期+1天作为当前因子的起始区间（可根据业务调整规则） */
    prev_eff_date = lag(factor_eff_date);
    if first.state then factor_start_date = .; /* 第一个因子从最早日期开始生效 */
    else factor_start_date = prev_eff_date + 1;
    factor_end_date = factor_eff_date;
    drop prev_eff_date;
run;

/* 关联保单表和区间表，匹配对应区间的因子 */
proc sql;
    create table policy_with_factor as
    select 
        p.policy_id,
        p.state,
        p.effective_date,
        f.factor_value
    from policy_table p
    left join factor_interval f
        on p.state = f.state
        and (
            (p.effective_date between f.factor_start_date and f.factor_end_date)
            or (f.factor_start_date is . and p.effective_date <= f.factor_end_date)
        );
quit;

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