数据仓库项目笔记5

用户留存分析

今日日期 前一天的一日留存 前两天的两日留存 每次求出每行的最后一列

用户留存含义: 当日的新增用户在所求日期也活跃
用户留存解析: 所需字段

日期	新增用户数	1天后留存数	2天后留存数

因为日期的不同, 几天后的列数不固定, 因此竖表转为横表更有利于设计

日期	新增用户数	天后(留存天数)	留存数

根据历史表 历史表设计 uid 代表用户
首登日期可求出日新 最后登录日期可求出日活(为留存和新鲜度打基础)

日期	用户id	用户首登日期	最后登录日期

首登日期的用户即为新增用户 以首登日期分组count即为日新数
留存天数: 首登日期在所求日期也活跃了(最后登录时间为所求日期) 那么就首登日期到所求日期的天数即为留存天数
留存数: 首登日期一样的最后登录时间为所求日期的总数 即为首登的留存数

create table yiee.dwd_dau_dtl(
uid string,
province string,
city string,
district string,
release_ch string,
manufacture string
)
select
first_login as dt,
count(1) as dnu_cnt,   -- 日新总数
datediff('2019-08-30',first_login) as retention_days,  --留存天数
count(if(last_login='2019-08-30',1,null)) as retention_cnts  --留存人数
from  demo_hisu_dtl
where datediff('2019-08-30',first_login) between 1 and 30
and last_login ='2019-08-30'
group by first_login, datediff('2019-08-30',first_login)
;

用户留存比率解析:

每日的留存在留存表中累加

留存数据	留存分析
0601,100,1,80	0602求出的1日留存
0601,100,2,60	0603求出的2日留存
0601,100,3,50	0604求出的3日留存
0601,100,4,60	0605求出的4日留存
0601,100,5,40	0606求出的5日留存
0602,80,1,80	0603求出的1日留存
0602,80,2,50	0604求出的2日留存
0602,80,3,40	0605求出的3日留存
0602,80,4,30	0606求出的4日留存
0603,120,1,110	0604求出的1日留存
0603,120,2,80	0605求出的2日留存
0603,120,3,90	0606求出的3日留存

竖表转横表(分组聚合)
不固定列表现: 根据不同日期的留存会有不同的留存天数
需要将相同的日期汇总求出当前日期之前30天不同日期的留存

create table yiee.demo_retention_dtl(
dt string,
dnu_cnt int,
retention_days int,
retention_cnts int
)
row format delimited fields terminated by ',';

load data local inpath '/root/data/demo_retention_dtl.dat' into table demo_retention_dtl;
0601,100,1,80
0601,100,2,60
0601,100,3,50
0601,100,4,60
0601,100,5,40
0602,80,1,80
0602,80,2,50
0602,80,3,40
0602,80,4,30
0603,120,1,110
0603,120,2,80
0603,120,3,90

select
dt,
dnu_cnt,
sum(if(retention_days=1,retention_cnts,0)) as ret_1_cnts,
sum(if(retention_days=2,retention_cnts,0)) as ret_2_cnts,
sum(if(retention_days=3,retention_cnts,0)) as ret_3_cnts,
sum(if(retention_days=4,retention_cnts,0)) as ret_4_cnts,
sum(if(retention_days=5,retention_cnts,0)) as ret_5_cnts,
sum(if(retention_days=6,retention_cnts,0)) as ret_6_cnts,
sum(if(retention_days=7,retention_cnts,0)) as ret_7_cnts,
sum(if(retention_days=14,retention_cnts,0)) as ret_14_cnts,
sum(if(retention_days=30,retention_cnts,0)) as ret_30_cnts
from demo_retention_dtl
group by dt,dnu_cnt

使用hive map实现
with tmp as(
select
dt,
dnu_cnt,
str_to_map(concat_ws(',',collect_set(concat_ws(':',cast(retention_days as string),cast(retention_cnts as string)))),',',':') as info
from demo_retention_dtl
group by dt,dnu_cnt
)

select
dt,
dnu_cnt,
info['1'] as ret_1,
info['2'] as ret_2,
info['3'] as ret_3,
info['4'] as ret_4,
info['5'] as ret_5,
info['6'] as ret_6,
info['7'] as ret_7,
info['14'] as ret_14,
info['30'] as ret_30

from  tmp

用户新鲜度分析

用户新鲜度含义:当日活跃用户中 以前日期新增用户占比
所需字段

日期	活跃总数	1天前占比	2天前占比

转竖表

日期	活跃总数	新鲜度	新鲜人数

活跃总数: 最后登录日期为所求日期的总数
新鲜度: 最后登录日期减首次登录日期
新鲜人数: 最后登录日期为所求日期 的相同首次登录日期

with tmp as (
select 
last_login dt,
datediff(last_login,first_login) fresh,
count(1) fresh_cnts
from 
demo_hisu_dtl
where last_login='2019-08-30' and datediff('2019-08-31',first_login) between 1 and 30
group by last_login, first_login

)

insert into table dws_active_fresh_dtl
select
b.dt,    -- 计算日期
a.dau_amt,   -- 活跃总数
b.fresh,     -- 新鲜度
b.fresh_cnts  -- 新鲜人数
from
(
select 
count(1) dau_amt , last_login
from 
demo_hisu_dtl
where last_login ='2019-08-30'
group by last_login
) a
join 

(select dt,fresh,   fresh_cnts from tmp) b
on a.last_login = b.dt

用户留存数: 几日前新增用户在所求日期的留存
用户留存日: 几日前
用户新鲜数: 在所求日期的活跃用户中在几日前的新增的用户
用户新鲜度: 几日前
所以用户留存数和新鲜度是一样的

用另一种实现方式 hive map 炸裂 实现

–新用户留存
–用户新鲜度
使用map 炸裂实现

with tmp as
(select 

explode(
map(
date_sub('2019-08-30',1), count(if((first_login=date_sub('2019-08-30',1) and last_login = '2019-08-30'),1,null)),
date_sub('2019-08-30',2), count(if((first_login=date_sub('2019-08-30',2) and last_login = '2019-08-30'),1,null)),
date_sub('2019-08-30',3), count(if((first_login=date_sub('2019-08-30',3) and last_login = '2019-08-30'),1,null)),
date_sub('2019-08-30',4), count(if((first_login=date_sub('2019-08-30',4) and last_login = '2019-08-30'),1,null)),
date_sub('2019-08-30',5), count(if((first_login=date_sub('2019-08-30',5) and last_login = '2019-08-30'),1,null)),
date_sub('2019-08-30',6), count(if((first_login=date_sub('2019-08-30',6) and last_login = '2019-08-30'),1,null)),
date_sub('2019-08-30',7), count(if((first_login=date_sub('2019-08-30',7) and last_login = '2019-08-30'),1,null))
)) as (key, value)


from
demo_hisu_dtl)

select 
key as dt,
datediff('2019-08-30', key) as retention_days,
value as retention_cnts
from tmp


方式三:
insert into yiee.dws_user_retention_dtl
select
first_login as dt,
count(1) as dnu_cnt,   -- 日新总数
datediff('2019-08-30',first_login) as retention_days,  --留存天数
count(if(last_login='2019-08-30',1,null)) as retention_cnts  --留存人数
from  yiee.demo_hisu_dtl
where datediff('2019-08-30',first_login) between 1 and 30
and last_login ='2019-08-30'
group by first_login, datediff('2019-08-30',first_login)
;

方式四: 求用户活跃总人数
select
last_login,
concat(datediff(last_login,first_login),'天前新用户') as days,
count(1)  fresh_cnts,
sum(count(1)) over(partition by last_login )as dau_amt-- 所有新鲜人数汇总一起就是今天的活跃总数
from
demo_hisu_dtl
where last_login = '2019-08-30'
group by  last_login,first_login;