新增用户
思路:
1:抽取出来当天活跃用户 etl_user_active_day,
2: 拿当天活跃用户跟历史用户做对比(得出当日的新增用户)
3:增加新增用户到历史记录表里面
4:对当日新增用户进行各维度统计
/** 历史用户表 **/
create table etl_history_user(uid string);
/** 创建一个每日新增用户表 **/
create table etl_user_new_day(
uid string,
commit_time string,
city string,
release_channel string,
app_ver_name string
)partitioned by (day string);
/** 创建维度聚合的 **/
create table dim_user_new(
city string,
release_channel string,
app_ver_name string,
newnew_cnt int
)partitioned by (day string,flag string);
/** etl_user_new_day ***/
/** 新增用户 20170101 **/
insert into table etl_user_new_day partition(day="20170101")
select
a.uid,a.commit_time,a.city,a.release_channel,a.app_ver_name
from etl_user_active_day a
left join
etl_history_user b
on a.uid=b.uid
where a.day="20170101" and b.uid is null;
/** 把新增用户插入到历史记录表里面 **/
insert