全篇以安卓手机通用埋点字段进行分析
1.每日活跃设备分析
insert overwrite table dws_uv_detail_day
partition(dt="2019-12-20")
select
mid_id,
concat_ws("|",collect_set(user_id)) user_id,
concat_ws("|",collect_set(version_code)) version_code,
...
from dwd_start_log
where dt="2019-12-20"
group by mid_id;
2.每周活跃设备分析
insert overwrite table dws_uv_detail_wk
partition (wk_dt) //因为在这里没有给分区赋值,所以最后一个字段给这里赋值
select mid_id,
concat_ws("|",collect_set(mid_id)) mid_id,
concat_ws("|",collect_set(user_id)) user_id,
...,
date_add(next_day("2019-12-20","MO"),-7),
date_add(next_day("2019-12-20","MO"),-1),
concat(date_add(next_day("2019-12-20","MO"),-7),"_",date_add(next_day("2019-12-20","MO"),-1))
from dws_uv_detail_day
where dt <= date_add(next_day("2019-12-20","MO"),-1) and dt >= date_add(next_day("2019-12-20","MO"),-7)
group by mid_id;
3.每月活跃设备分析
insert overwrite table dws_uv_detail_mn
partition (mn_dt)
select mid_id,
concat_ws("|",collect_set(user_id)) user_id,
concat_ws("|",collect_set(version_code)) version_code,
...,
date_format("2019-12-20","yyyy-MM")
from dws_uv_detail_day
where date_format(dt,"yyyy-MM") = date_format("2019-12-20","yyyy-MM")
group by mid_id;
4.活跃设备分析
insert into table ads_uv_count
select
"2019-12-20" dt, //创建日期
dc.num day_count,
wc.num wk_count,
mc.num mc_count
if(dt = date_add(next_day("2019-12-20","MO"),-1),"Y","N"), //判断创建日期是否是星期日
if(dt = last_day("2019-12-20"),"Y","N") //判断创建日期是否是月末
from
(select "2019-12-20" dt,
count(*) num
from dws_uv_detail_day
where dt="2019-12-20") dc
join
(select "2019-12-20" dt,
count(*) num
from dws_uv_detail_wk
where wk_dt=concat(date_add(next_day("2019-12-20","MO"),-7),"_",date_add(next_day("2019-12-20","MO"),-1))) wc
dc.dt = wc.dt //使用创建日期进行join
join
(select "2019-12-20" dt,
count(*) num
from dws_uv_detail_mn
where mn_dt=date_format("2019-12-20","yyyy-MM")) mc
dc.dt = wc.dt)
5.日新增明细表
insert into table dws_new_mid_day //注意这是insert into 否则理解理解日新增表可能会有误差
select
ud.mid_id,
ud.user_id,
...,
"2019-12-20"
from
dws_uv_detail_day ud left join dws_new_mid_day nm //"2019-12-20"的日活和之前的新增用户表所有的数据进行left join
on ud.mid_id = nm.mid_id //如果新增用户表中没有这个数据,就把这些用户放进去
where ud.dt="2019-12-20" and nm.mid_id is null; //其实新增用户表说成(用户总表)也没错
6.每日新增设备数表
insert into table ads_new_mid_count
select
create_date,
count(*)
from
dws_new_mid_day nm
where nm.create_date="2019-12-20"
group by nm.create_date;