数据仓库项目笔记6

用户活跃留存:

• 求出当日以前的所有日期在(当日-每个日期)天后留存
• 可以用当日的日活join所有的日期日活获取用户活跃留存
• 这样join表数巨多, 另辟蹊径 如果有一张表记录了每个用户的活跃状态的日期区间 就能解决以前历史表不能获取所有日期用户是否活跃的问题

用户活跃记录拉链表 状态的固定区间然后形成链(数据状态更新不频繁)

• 记录了每个用户的活跃状态的日期区间

拉链表设计: 需要uid , 用户首登日期, 用户连续活跃起始日, 用户连续活跃结束日

* 为了表示活跃是否中断 如果今日活跃则起始日到结束日('9999-12-31')为开区间
* 如果今日不活跃则起始日到结束日('2019-09-04')为闭区间
* 为求出拉链表就需要知道今日是否活跃(日活表) 然后join 前拉链表
结束日条件: 
	无区间 有日活 则开区间'9999-12-31'
	开区间 无日活 则封闭 今日-1
	开区间 有日活 和闭区间 无日活 均保留原始结束日
另一种特殊条件需要增加列
	闭区间 有日活 : 过滤掉有开区间的uid , 然后join 日活表 有日活的数据

测试数据
create table dws_user_continue_act(
uid string,
first_login string,
continue_start string,
continue_end string
)
row format delimited fields terminated by ','
;

vi x.dat
a,2019-05-20,2019-05-20,2019-05-26
a,2019-05-20,2019-05-29,2019-06-01
a,2019-05-20,2019-06-03,9999-12-31
b,2019-05-22,2019-05-22,2019-05-30
b,2019-05-22,2019-06-03,2019-06-05
c,2019-06-03,2019-06-03,9999-12-31
x,2019-06-03,2019-06-03,2019-06-04

load data local inpath '/root/x.dat' into table dws_user_continue_act;


6.9号日活
create table demo_dau_dtl(
uid string
)
partitioned by (dt string)
row format delimited fields terminated by ',';

vi dau.dat
a
b
d
load data local inpath '/root/dau.dat' into table demo_dau_dtl partition(dt='2019-06-09')

with dau as(
select dt,uid from demo_dau_dtl where dt='2019-06-09'
),
tmp as (
select
uid,first_login
from dws_user_continue_act
group by uid,first_login
having max(continue_end) != '9999-12-31'
)

--insert overwrite table dws_user_continue_act

select
if(a.uid is not null,a.uid,b.uid) as uid,
if(a.uid is not null,a.first_login,b.dt) as first_login,
if(a.uid is not null,a.continue_start,b.dt) as continue_start,
case 
  when a.uid is null then '9999-12-31'
  when a.continue_end='9999-12-31' and b.uid is null then date_sub('2019-06-09',1)
  else a.continue_end
end as continue_end
from 
  dws_user_continue_act a
full join
  dau b  
on a.uid=b.uid 

union all

select  
tmp.uid,
tmp.first_login,
dau.dt as continue_start,
'9999-12-31' as continue_end

from tmp  join  dau on tmp.uid=dau.uid
;

用户活跃留存解决方案:

±-------±---------------±------------------±----------------+
| t.uid | t.first_login | t.continue_start | t.continue_end |
±-------±---------------±------------------±----------------+
| a | 2019-05-20 | 2019-05-20 | 2019-05-26 |
| a | 2019-05-20 | 2019-05-29 | 2019-06-01 |
| a | 2019-05-20 | 2019-06-03 | 9999-12-31 |
| b | 2019-05-22 | 2019-05-22 | 2019-05-30 |
| b | 2019-05-22 | 2019-06-03 | 2019-06-05 |
| c | 2019-06-03 | 2019-06-03 | 9999-12-31 |
| x | 2019-06-03 | 2019-06-03 | 2019-06-04 |
±-------±---------------±------------------±----------------+
求出今日30日前所有日期 最后一列留存 即29日 1日留存 28日2日留存 等等
每一行需要分裂成多行来得出结果所以用map实现

with tmp as (
select 
explode(
map(
date_sub('2019-06-09',1),count(if(continue_start<=date_sub('2019-06-09',1) and continue_end='9999-12-31',1,null)),
date_sub('2019-06-09',2),count(if(continue_start<=date_sub('2019-06-09',2) and continue_end='9999-12-31',1,null)),
date_sub('2019-06-09',3),count(if(continue_start<=date_sub('2019-06-09',3) and continue_end='9999-12-31',1,null)),
date_sub('2019-06-09',4),count(if(continue_start<=date_sub('2019-06-09',4) and continue_end='9999-12-31',1,null)),
date_sub('2019-06-09',5),count(if(continue_start<=date_sub('2019-06-09',5) and continue_end='9999-12-31',1,null)),
date_sub('2019-06-09',6),count(if(continue_start<=date_sub('2019-06-09',6) and continue_end='9999-12-31',1,null)),
date_sub('2019-06-09',7),count(if(continue_start<=date_sub('2019-06-09',7) and continue_end='9999-12-31',1,null))
) 
) as(dt,cnts)
from  dws_user_continue_act

)

select
key as dt,
datediff('2019-06-09',key) as remain_days,
value as remain_cnts
from tmp
;