首先注意一点在dwt层数据已经都经过了聚合,在ads层所有的id都是唯一存在的不需要再group by
dws要根据表来具体分析,如果是日活,不同分区很有可能有重复id
活跃设备数(日、周、月)
table ads_uv_count(
`dt` string COMMENT '统计日期',
`day_count` bigint COMMENT '当日用户数量',
`wk_count` bigint COMMENT '当周用户数量',
`mn_count` bigint COMMENT '当月用户数量',
`is_weekend` string COMMENT 'Y,N 是否是周末,用于得到本周最终结果',
`is_monthend` string COMMENT 'Y,N 是否是月末,用于得到本月最终结果'
) COMMENT '活跃设备数'
导入SQL
因为报表是每天产生的,假设今天是2020-04-28,所以dt='2020-04-28'
是否是星期天的判断if(date_add(next_day('2020-04-28','MO'),-1)='2020-04-28','Y','N')
即4-28的下一个周一再减一天是否是4-28,是即为周天
是否是月末判断if(last_day('2020-04-28')='if(last_day('2020-04-28')='2020-03-10','Y','N')','Y','N')
即当前日期当月的最后一天是否是今天,是即为月末
查每天即最后登录=今天
查每周即最后登录=<本周末 >=本周一
查每月即最后登录月=本月
insert into table ads_uv_count
select
'2020-04-28' dt,
daycount.ct,
wkcount.ct,
mncount.ct,
if(date_add(next_day('2020-04-28','MO'),-1)='2020-04-28','Y','N') ,
if(last_day('2020-04-28')='2020-04-28','Y','N')
from
(
select
'2020-04-28' dt,
count(*) ct
from dwt_uv_topic
where login_date_last='2020-04-28'
)daycount join
(
select
'2020-04-28' dt,
count (*) ct
from dwt_uv_topic
where login_date_last>=date_add(next_day('2020-04-28','MO'),-7)
and login_date_last<= date_add(next_day('2020-04-28','MO'),-1)
) wkcount on daycount.dt=wkcount.dt
join
(
select
'2020-04-28' dt,
count (*) ct
from dwt_uv_topic
where
date_format(login_date_last,'yyyy-MM')=date_format('2020-04-28','yyyy-MM')
)mncount on daycount.dt=mncount.dt;
新增设备数
table ads_new_mid_count
(
`create_date` string comment '创建时间' ,
`new_mid_count` BIGINT comment '新增设备数量'
) COMMENT '每日新增设备信息数量'
导入SQL
很简单就是查今天'2020-04-28'是首次登录
insert into table ads_new_mid_count
select
'2020-04-28',
count(*)
from dwt_uv_topic
where login_date_first='2020-04-28'
沉默用户数
沉默用户:只在安装当天启动过,且启动时间是在7 天前
table ads_silent_count(
`dt` string COMMENT '统计日期',
`silent_count` bigint COMMENT '沉默设备数'
)
导入SQL
即首次登录即最后登录,且最后一次登录是7天前
insert into table ads_silent_count
select
'2020-04-28',
count(*)
from dwt_uv_topic
where login_date_first=login_date_last
and login_date_last<=date_add('2020-04-28',-7);
本周回流用户数
本周回流用户:上周未活跃,本周活跃的设备,且不是本周新增设备
table ads_back_count(
`dt` string COMMENT '统计日期',
`wk_dt` string COMMENT '统计日期所在周',
`wastage_count` bigint COMMENT '回流设备数'
)
导入SQL
分成两部分
第一部分查询dwt_uv_topic,在本周活跃且不是本周的新增设备id
第二部分查询dws_uv_detail_daycount,查出上周所有的设备id
本周活跃id left join 上周所有活跃id,右表为空的值即代表上周不活跃但是本周活跃
insert into table ads_back_count
select
'2020-04-28',
count(*)
from
(
select
mid_id
from
dwt_uv_topic
where login_date_last>=date_add(next_day('2020-04-28','MO'),-7)
and login_date_last<= date_add(next_day('2020-04-28','MO'),-1)
and login_date_first<date_add(next_day('2020-04-28','MO'),-7)
)current_wk
left join
(
select
mid_id
from
dws_uv_detail_daycount
where dt>=date_add(next_day('2020-04-28','MO'),-7*2)
and dt<= date_add(next_day('2020-04-28','MO'),-7-1)
group by mid_id
)last_wk
on current_wk.mid_id=last_wk.mid_id
where last_wk.mid_id is null;
流失用户数
流失用户:最近7 天未活跃的设备
table ads_wastage_count(
`dt` string COMMENT '统计日期',
`wastage_count` bigint COMMENT '流失设备数'
)
导入SQL
最后一次登录在七天前的用户
select
'2020-04-28',
count(*)
from dwt_uv_topic
where login_date_last<=date_add('2020-04-28',-7);
留存率
留存用户:某段时间内的新增用户(活跃用户),经过一段时间后,又继续使用应用的被认为是留存用户。
留存率:留存用户占当时新增用户(活跃用户)的比例即为留存率
table ads_user_retention_day_rate
(
`stat_date` string comment '统计日期',
`create_date` string comment '设备新增日期',
`retention_day` int comment '截止当前日期留存天数',
`retention_count` bigint comment '留存数量',
`new_mid_count` bigint comment '设备新增数量',
`retention_ratio` decimal(10,2) comment '留存率'
) COMMENT '每日用户留存情况'
统计近三天留存率,导入sql
首先明确相对日期2020-04-28
其次明确统计日期2020-04-27
求04-27的一天留存数,即4-27是首次登录,且4-28是末次登录
求4-27的新增数,即4-27是首次登录
前二者相除得最后结果
以此类推
。。。。。。
insert into table ads_user_retention_day_rate
select
'2020-04-28',--统计日期
date_add('2020-04-28',-1),--新增日期'2020-04-27'
1,--留存天数
sum(if(login_date_first=date_add('2020-04-28',-1)
and login_date_last='2020-04-28',1,0)),--2020-04-27 的1 日留存数
sum(if(login_date_first=date_add('2020-04-28',-1),1,0)),--2020-04-27 新增
sum(if(login_date_first=date_add('2020-04-28',-1)
and login_date_last='2020-04-28',1,0))/sum(if(login_date_first=date_add('2020-04-28',-
1),1,0))*100-- 2020-04-27 相对 2020-04-28 1天留存率
from dwt_uv_topic
union all
select
'2020-04-28',--统计日期
date_add('2020-04-28',-2),--新增日期
2,--留存天数
sum(if(login_date_first=date_add('2020-04-28',-2) and
login_date_last='2020-04-28',1,0)),--2020-04-26 的2 日留存数
sum(if(login_date_first=date_add('2020-04-28',-2),1,0)),--2020-04-26 新增
sum(if(login_date_first=date_add('2020-04-28',-2) and
login_date_last='2020-04-28',1,0))/sum(if(login_date_first=date_add('2020-04-28',-
2),1,0))*100-- 2020-04-26 相对 2020-04-28 2天留存率
from dwt_uv_topic
union all
select
'2020-04-28',--统计日期
date_add('2020-04-28',-3),--新增日期
3,--留存天数
sum(if(login_date_first=date_add('2020-04-28',-3) and
login_date_last='2020-04-28',1,0)),--2020-04-25 的3 日留存数
sum(if(login_date_first=date_add('2020-04-28',-3),1,0)),--2020-04-25 新增
sum(if(login_date_first=date_add('2020-04-28',-3) and
login_date_last='2020-04-28',1,0))/sum(if(login_date_first=date_add('2020-04-28',-
3),1,0))*100-- 2020-04-25 相对 2020-04-28 2天留存率
from dwt_uv_topic;
最近连续三周活跃用户数
table ads_continuity_wk_count(
`dt` string COMMENT '统计日期,一般用结束周周日日期,如果每天计算一次,可用当天日
期',
`wk_dt` string COMMENT '持续时间',
`continuity_count` bigint COMMENT '活跃次数'
)
导入sql
要求连续三周活跃,则每周都要有活跃,这需要一个设备的历史数据
历史数据dwt层不存在,只有dws层有
要连续三周活跃,则分解成近三周每周都活跃
先去重得到最近三周的活跃设备(记得求每周都要group by,因为dws_uvdetail_daycount表的细粒度只到天级,本周很可能有重复mid_id)
union all 三张表得到三周活跃设备信息(未去重,这时候的细粒度来到了周级,而三周的规模表很可能存在重复)
去重并加上条件having count(*)=3得到三周都存在的mid_id
对结果进行count(*),得到最后结果
insert into table ads_continuity_wk_count
select
'2020-04-29',
concat(date_add(next_day('2020-04-29','MO'),-7*3),'_',date_add(next_day('
2020-04-29','MO'),-1)),
count(*)
from
(
select
mid_id
from
(
select
mid_id
from dws_uv_detail_daycount
where dt>=date_add(next_day('2020-04-29','monday'),-7)
and dt<=date_add(next_day('2020-04-29','monday'),-1)
group by mid_id
union all
select
mid_id
from dws_uv_detail_daycount
where dt>=date_add(next_day('2020-04-29','monday'),-7*2)
and dt<=date_add(next_day('2020-04-29','monday'),-7-1)
group by mid_id
union all
select
mid_id
from dws_uv_detail_daycount
where dt>=date_add(next_day('2020-04-29','monday'),-7*3)
and dt<=date_add(next_day('2020-04-29','monday'),-7*2-1)
group by mid_id
)t1
group by mid_id
having count(*)=3
)t2
最近七天内连续三天活跃用户数
table ads_continuity_uv_count(
`dt` string COMMENT '统计日期',
`wk_dt` string COMMENT '最近7 天日期',
`continuity_count` bigint
) COMMENT '连续活跃设备数'
导入sql
这是一个多层嵌套sql,我们一层一层往外解析
第一层通过dws_uv_detail_daycount求出最近7天所有活跃用户
通过开窗函数根据mid_id分区做rank(这里不会出现rank重复,因为dws已在天级细粒度实现去重),时间升序排序
第二层对dt和rk做一个差值,这样连续的天数相减会得到一个定值
第三层对设备id mid_id 和 差值 diff做一个分组,且保留同组计数大于3的分组(这时的细粒度是由mid_id和diff共同构成)
第四层我们最后需要得到的是mid_id,所以对mid_id再做一次去重,去掉midid相同但是时间不同的分组,得到唯一的mid_id
因为比较复杂这里给出图解
insert into table ads_continuity_uv_count
select
'2020-04-29',
concat(date_add('2020-04-29',-6),'_','2020-04-29'),
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_daycount
where dt>=date_add('2020-04-29',-6) and
dt<='2020-04-29'
)t1
)t2
group by
mid_id,date_dif
having count(*)>=3
)t3
group by
mid_id
)t4;