hive_练习0419

--第一步：去重t1，并保留最新状态得到所有用户的状态

--第二步：去重t2，并保留最新状态
create table t2_temp as
select
    t1.updatetime, t1.userid, t2.statu, t2.pt_d
from
(
select
     updatetime
    ,userid
from (
select
    MAX(updatetime) over(partition by userid) as updatetime
   ,userid
from  default.t2_stastus_new_user)tt1
group by updatetime
    ,userid)t1
left join
(
select
    updatetime,userid,statu,pt_d
from  default.t2_stastus_new_user
where pt_d<= '0407') t2
on t1.userid=t2.userid and t1.updatetime=t2.updatetime

---第三步  t2_temp left join t1
求用户数

select
    pt_d
    ,bind_user_flag+bind_new_user_flag  as bind_users
    ,unbind_user_flag
    ,bind_user_flag+bind_new_user_flag-unbind_user_flag  as in_bind_users
from (
select
    pt_d
    ,sum(`if`(bind_user_flag>0,1,0)) as bind_user_flag
    ,sum(`if`(bind_new_flag>0,1,0)) as bind_new_user_flag
    ,sum(`if`(unbind_flag>0,1,0)) as unbind_user_flag
from (
select
       pt_d
    ,updatetime
     ,userid
    ,statu
     ,t3.statu_flag
     ,sum(`if`((statu = '1' and statu_flag = '0'),1,0)) as bind_user_flag
     ,sum(`if`((statu = '1' and statu_flag is null),1,0)) as bind_new_flag
     ,sum(`if`((statu = '0' and t3.statu_flag = '1'),1,0)) as unbind_flag
from
(select  t2.updatetime,t2.userid,t2.statu,t1.statu as statu_flag,t2.pt_d
from
(select
    updatetime,userid,statu,pt_d
from t2_temp) t2
left join
(
    select  updatetime,userid,statu
    from  t1_stastus_all_user
    )t1
on t2.userid = t1.userid)t3
group by pt_d
    ,updatetime
     ,userid
    ,statu
     ,statu_flag
order by userid)t4
group by  pt_d)t5