企业核心业务分析指标(难度易)

全篇以安卓手机通用埋点字段进行分析

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;