-- 1.表结构
-- create table visit_hist(
-- customer_id int comment '客户id'
-- ,visit_date int comment '访问日期'
-- )
-- 2.造测试数据
-- insert into visit_hist(customer_id,visit_date) values
-- (11,11),(11,7),(22,5),(66,8),(55,4),(11,35),(22,32),(33,31),(55,39)
-- with t1 as (
-- select customer_id
-- ,visit_date
-- from visit_hist vh
-- where visit_date <30
-- )
-- 每个客户观察日(第30日)之前的最大拜访日期
with t2 as (
select customer_id
,max(visit_date) as max_visit_date
from visit_hist vh
where visit_date <30
group by customer_id
)
-- select * from t2
-- 每个客户在观察日之前的最晚一个拜访日期之后30天内的拜访次数
,t3 as (select
t2.customer_id
,t2.max_visit_date
,sum(case when t_all.visit_date<t2.max_visit_date+30 then 1 else 0 end) as total_cnt_after
from t2
left join visit_hist t_all on t2.customer_id=t_all.customer_id
where t_all.visit_date >=30 and t_all.visit_date <90
group by t2.customer_id
)
-- 这一句会产生total_cnt_after等于0的行，主要是由case when判断产生
-- select * from t3;
-- 下面的left join由于主表是t2其中的customer_id比t3多，因此会导致t2的某些行total_cnt_after为null值
-- -- 对观察日之前有过拜访记录的客户打上标签
-- select t2.customer_id
-- ,t3.total_cnt_after
-- ,case when t3.total_cnt_after>0 then 1 else 0 end as is_active
-- from t2
-- left join t3 on t2.customer_id=t3.customer_id;
-- 打印MySQL版本
-- select version();
