查找客户在过去2个月内是否已经拥有SQL Teradata到SQL SAS的产品

Question

希望你一切安好!

我有一个名为"policies"它有以下数据:

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

和我想建立2列"has_policy_month_before"one_answers";has_policy_month_less2"对于每个date_begin_policy检查客户是否已经在date_begin_policy -1month和date_begin_policy - 2 month中有其他策略:

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中这样做:

select t1.customer_id
, t1.policy_id
, t1.date_begin_policy
, case
when trunc(t2.date_begin_policy, 'mm') >= add_months(trunc(t1.date_begin_policy, 'mm'), -1)
then 'yes'
else 'no'
end as has_policy_month_before
, case
when trunc(t2.date_begin_policy, 'mm') <  add_months(trunc(t1.date_begin_policy, 'mm'), -1)
then 'yes'
else 'no'
end as has_policy_month_less2
from mvt_data as t1
left join mvt_data as t2  on t2.customer_id                     = t1.customer_id
and trunc(t2.date_begin_policy, 'mm') >= add_months(trunc(t1.date_begin_policy, 'mm'), -2)
and trunc(t2.date_begin_policy, 'mm') <  trunc(t1.date_begin_policy, 'mm')
where t1.customer_id = 'A'
order by t1.policy_id asc;

，但问题是add_months函数和trunc函数在SAS中不像这样。有人能帮帮我吗?

请注意，我真的希望这样做在sql，而不是sas语法最好。

感谢

Answer 1

我无法访问任何teradata环境进行测试，但您可以尝试这样做:

select
p.Date_begin_policy,
p.Customer_id,
p.Policy_id,
case 
when lag(p.Policy_id, 1) over (partition by p.Customer_id order by p.Date_begin_policy) is null 
then 'no'
else 'yes'
end as has_policy_month_before,
case 
when lag(p.Policy_id, 2) over (partition by p.Customer_id order by p.Date_begin_policy) is null 
then 'no'
else 'yes'
end as has_policy_month_less2
from policies p