Teradata客户近2个月保单判定SQL转换为SAS SQL实现咨询

现有一张名为policies的表，数据结构和示例如下：

Date_begin_policy  Customer_id   Policy_id
01-09-2019             A            111
02-10-2019             A            123
09-07-2019             A            126

需要新增两列：has_policy_month_before、has_policy_month_less2，针对每条记录的date_begin_policy字段，分别校验对应客户在该日期往前1个月、往前2个月的区间内是否存在其他保单，预期输出结果如下：

Date_begin_policy  Customer_id   Policy_id  has_policy_month_before   has_policy_month_less2
01-09-2019             A             111           no                         yes
02-10-2019             A             123           yes                        no
09-07-2019             A             126           no                         no

原Teradata SQL实现逻辑需要适配SAS SQL运行，优先使用标准SQL兼容语法。

以下代码兼容SAS SQL语法，对Teradata特有函数做了等价替换：

• 日期截断到月初使用INTNX('MONTH', 日期值, 0, 'B')实现，等价原trunc(date, 'mm')
• 月份偏移使用INTNX('MONTH', 日期值, 偏移量, 'B')实现，等价原add_months
• 若Date_begin_policy为字符型存储，需先通过input()函数转为SAS日期值

proc sql noprint;
select 
    t1.Customer_id,
    t1.Policy_id,
    t1.Date_begin_policy,
    /* 校验往前1个月区间是否有保单 */
    case 
        when max(intnx('MONTH', input(t2.Date_begin_policy, ddmmyy10.), 0, 'B')) >= intnx('MONTH', input(t1.Date_begin_policy, ddmmyy10.), -1, 'B')
        then 'yes'
        else 'no'
    end as has_policy_month_before length=3,
    /* 校验往前2个月到前1个月之间的区间是否有保单 */
    case 
        when max(intnx('MONTH', input(t2.Date_begin_policy, ddmmyy10.), 0, 'B')) is not null 
             and max(intnx('MONTH', input(t2.Date_begin_policy, ddmmyy10.), 0, 'B')) < intnx('MONTH', input(t1.Date_begin_policy, ddmmyy10.), -1, 'B')
        then 'yes'
        else 'no'
    end as has_policy_month_less2 length=3
from policies t1
left join policies t2 
    on t2.Customer_id = t1.Customer_id
    /* 关联同客户近2个月（不含当月）的保单 */
    and intnx('MONTH', input(t2.Date_begin_policy, ddmmyy10.), 0, 'B') >= intnx('MONTH', input(t1.Date_begin_policy, ddmmyy10.), -2, 'B')
    and intnx('MONTH', input(t2.Date_begin_policy, ddmmyy10.), 0, 'B') < intnx('MONTH', input(t1.Date_begin_policy, ddmmyy10.), 0, 'B')
    /* 排除自身保单匹配 */
    and t2.Policy_id <> t1.Policy_id
where t1.Customer_id = 'A'
group by t1.Customer_id, t1.Policy_id, t1.Date_begin_policy
order by t1.Policy_id asc;
quit;

注：如果Date_begin_policy字段已经是SAS日期格式存储，可去掉input(xxx, ddmmyy10.)转换逻辑，直接使用字段即可。新增了max()聚合和group by逻辑避免同一条主记录关联到多条历史保单时出现重复行，和预期输出结果对齐。

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