需求七 连续三周登录用户
--ads层的三周连续活跃用户
create external table ads_continuity_wk_count(
`dt` string COMMENT '统计日期,一般用结束周周日日期,如果每天计算一次,可用当天日期',
`wk_dt` string COMMENT '持续时间',
`continuity_count` bigint
) row format delimited fields terminated by '\t'
select * from ads_continuity_wk_count
insert into table ads_continuity_wk_count
select '2022-04-12' dt,concat(date_add(next_day('2022-04-12','mo'),-7*3),'_',
date_add(next_day('2022-04-12','mo'),-1)),
count(*) from (
select mid_id from dws_uv_detail_wk
where wk_dt>=concat(date_add(next_day('2022-04-12','mo'),-7*3),'_',
date_add(next_day('2022-04-12','mo'),-7*2-1))
and wk_dt<=concat(date_add(next_day('2022-04-12','mo'),-7),'_',
date_add(next_day('2022-04-12','mo'),-1))
group by mid_id having count(*)=3 ) tt
需求八 最近七天内连续三天活跃用户数
涉及知识点:开窗函数、rank、dense_rank、row_number、date_sub
--ads层的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'
select * from ads_continuity_uv_count
insert into table ads_continuity_uv_count
select '2022-04-12' dt,
concat(date_add('2022-04-12',-6),'_','2022-04-12'),
count(*) from (
select mid_id,count(*) from (
select mid_id,date_sub(dt,r) sub from (
select mid_id,dt,
rank() over(partition by mid_id order by dt) r
from dws_uv_detail_day
where dt>=date_add('2022-04-12',-7)
and dt<='2022-04-12') t ) tt
group by mid_id,sub having count(*)>=3 ) ttt