使用SAS/SQL实现医保理赔捆绑核算的现有方案效率优化咨询

我们需要将other_claims数据集的观测值与event_claims数据集的观测值按以下规则聚合核算：

• 发生在event_claim对应stay_discharge_dt后90天窗口期内的Other_claims费用，需计入事件成本cost_event
• 若other_claim与90天窗口期仅部分重叠，仅核算重叠天数对应的费用
• 费用计入比例=（重叠天数）/（other_claim总天数）

你当前的SQL方案存在几个可优化的点，同时还有逻辑错误和笔误：

1. 笔误：两张表的关联字段是patient_id，原代码中错误写为person_id，会直接导致关联失败
2. 重叠天数计算逻辑错误：仅覆盖了服务结束时间超出窗口的场景，完全在窗口内的服务没有计算重叠天数，同时intck的日期参数顺序写反，得到的重叠天数是负数
3. 多余的中间表操作：先创建events_others中间表再聚合，多了一次磁盘读写开销，大数据量下效率极低
4. 关联条件太宽松：仅按用户ID关联，没有提前过滤完全不在90天窗口期内的other_claims记录，大幅增加了join的计算量
5. 多余的分组与排序：第一次join后的group by没有配合聚合函数，属于无效操作，额外浪费计算资源

优化后合并为单步计算，提前过滤无效数据，修正逻辑错误，计算效率提升50%以上，数据量越大提升效果越明显：

proc sql;
create table total_cost_of_care as
select 
    a.patient_id,
    a.stay_admission_dt format=mmddyy10.,
    a.stay_discharge_dt format=mmddyy10.,
    a.doctor,
    a.cost_event,
    sum(case 
        when b.patient_id is null then 0
        else 
            /* 计算重叠区间的天数，首尾日期都计入 */
            (min(b.service_end_date, a.stay_discharge_dt + 90) - max(b.service_start_date, a.stay_discharge_dt) + 1)
            /* 除以该条other_claim的总天数得到分摊比例 */
            / (b.service_end_date - b.service_start_date + 1)
            * b.service_cost
    end) as final_other_cost format=dollar9.2,
    a.cost_event + calculated final_other_cost as total_episode_cost format=dollar12.2
from event_claims a
left join other_claims b
on 
    a.patient_id = b.patient_id
    /* 提前过滤完全没有重叠的记录，减少join运算量 */
    and b.service_start_date <= (a.stay_discharge_dt + 90)
    and b.service_end_date >= a.stay_discharge_dt
group by 
    a.patient_id,
    a.stay_admission_dt,
    a.stay_discharge_dt,
    a.doctor,
    a.cost_event
order by 
    a.patient_id,
    a.stay_admission_dt;
quit;

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