“ 上一节我们讲了关于用户行为数仓中用户活跃主题、用户新增主题、用户留存主题这三个方面的指标计算通用思路。这一节我们来解决剩下的几个主题的相关指标思路。”
用户行为数据仓库中的6个主题：
用户活跃主题
用户新增主题
用户留存主题
沉默用户数
本周回流用户数
流失用户数
最近连续3周活跃用户数
最近七天内连续三天活跃用户数
为了分析沉默用户、本周回流用户数、流失用户、最近连续3周活跃用户、最近七天内连续三天活跃用户数，需要准备隔了一个星期的2天的数据，比如这里，我们准备了2019-02-12、2019-02-20日的数据。
用Shell脚本调度 ODS层log数据导入 DWS层明细中；
01
—
沉默用户数
沉默用户：指的是只在安装当天启动过，且启动时间是在一周前
日活明细表-->设备分组且日期小于当天，统计日期数为1，且最小日期小于一个星期前
DWS层
使用日活明细表dws_uv_detail_day作为DWS层数据
02
—
本周回流用户数
本周回流=本周活跃-本周新增-上周活跃
日活明细表-->设备分组且日期小于当天，统计日期数为1，且最小日期小于一个星期前
1）建表语句
hive (gmall)>
drop table if exists ads_back_count;
create external table ads_back_count(
`dt` string COMMENT '统计日期',
`wk_dt` string COMMENT '统计日期所在周',
`wastage_count` bigint COMMENT '回流设备数'
)
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_back_count';
2）导入数据：
hive (gmall)>
insert into table ads_back_count
select
'2019-02-20' dt,
concat(date_add(next_day('2019-02-20','MO'),-7),'_',date_add(next_day('2019-02-20','MO'),-1)) wk_dt,
count(*)
from
(
select t1.mid_id
from
(
selectmid_id
from dws_uv_detail_wk
where wk_dt=concat(date_add(next_day('2019-02-20','MO'),-7),'_',date_add(next_day('2019-02-20','MO'),-1))
)t1
left join
(
select mid_id
from dws_new_mid_day
where create_date<=date_add(next_day('2019-02-20','MO'),-1) and create_date>=date_add(next_day('2019-02-20','MO'),-7)
)t2
on t1.mid_id=t2.mid_id
left join
(
select mid_id
from dws_uv_detail_wk
where wk_dt=concat(date_add(next_day('2019-02-20','MO'),-7*2),'_',date_add(next_day('2019-02-20','MO'),-7-1))
)t3
on t1.mid_id=t3.mid_id
where t2.mid_id is null and t3.mid_id is null
)t4;
3）查询结果
hive (gmall)> select * from ads_back_count;
03
—
流失用户数
流失用户：最近7天未登录我们称之为流失用户
最大活跃分区小于7天
04
—
最近连续3周活跃用户数
最近3周连续活跃的用户：通常是周一对前3周的数据做统计，该数据一周计算一次。
使用周活明细表dws_uv_detail_wk作为DWS层数据
设备号聚合 周分区范围在3周之间，且统计数等于3
1）建表语句
hive (gmall)>
drop table if exists ads_continuity_wk_count;
create external table ads_continuity_wk_count(
`dt` string COMMENT '统计日期,一般用结束周周日日期,如果每天计算一次,可用当天日期',
`wk_dt` string COMMENT '持续时间',
`continuity_count` bigint
)
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_continuity_wk_count';
2）导入2019-02-20所在周的数据
hive (gmall)>
insert into table ads_continuity_wk_count
select
'2019-02-20',
concat(date_add(next_day('2019-02-20','MO'),-7*3),'_',date_add(next_day('2019-02-20','MO'),-1)),
count(*)
from
(
select mid_id
from dws_uv_detail_wk
where wk_dt>=concat(date_add(next_day('2019-02-20','MO'),-7*3),'_',date_add(next_day('2019-02-20','MO'),-7*2-1))
and wk_dt<=concat(date_add(next_day('2019-02-20','MO'),-7),'_',date_add(next_day('2019-02-20','MO'),-1))
group by mid_id
having count(*)=3
)t1;
3）查询
hive (gmall)> select * from ads_continuity_wk_count;
05
—
最近七天内连续三天活跃用户数
最近7天内连续3天活跃用户数
日活明细表dws_uv_detail_day作为DWS层数据
算法：
1.查询出最近7天的活跃用户，并对用户活跃日期进行排名；
2.计算用户活跃日期及排名之间的差值；
3.对同用户及差值分组，统计差值个数；
4.将差值相同个数大于等于3的数据取出；
5.对数据去重
1）建表语句
hive (gmall)>
drop table if exists ads_continuity_uv_count;
create external table ads_continuity_uv_count(
`dt` string COMMENT '统计日期',
`wk_dt` string COMMENT '最近7天日期',
`continuity_count` bigint
) COMMENT '连续活跃设备数'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_continuity_uv_count';
2）写出导入数据的SQL语句
hive (gmall)>
insert into table ads_continuity_uv_count
select
'2019-02-12',
concat(date_add('2019-02-12',-6),'_','2019-02-12'),
count(*)
from
(
select mid_id
from
(
select mid_id
from
(
select
mid_id,
date_sub(dt,rank) date_dif
from
(
select
mid_id,
dt,
rank() over(partition by mid_id order by dt) rank
from dws_uv_detail_day
where dt>=date_add('2019-02-12',-6) and dt<='2019-02-12'
)t1
)t2
group by mid_id,date_dif
having count(*)>=3
)t3
group by mid_id
)t4;
(5)查询
hive (gmall)> select * from ads_continuity_uv_count;
比较难的：
回流用户
留存用户
最近7天内连续3天活跃用户
这三个是比较难一点的，要多看几遍
总结：这一节我们讲了用户行为数仓中，剩下的几个数仓指标的计算：
沉默用户数
本周回流用户数
流失用户数
最近连续3周活跃用户数
最近七天内连续三天活跃用户数
下一节我们来看下，经典的SQL面试题，整理整理关于数仓面试的SQL实战，几个经典问题。
#我是媛姐，一枚有多年大数据经验的程序媛，打过螺丝搬过砖，关注数仓，关注分析。愿你我走得更远！
