本周回流用户

定义：上周未活跃，本周活跃用户，并且非本周新增的用户；（根据业务要求进行定义）

建表：

drop table if exists back_count;
create external table back_count(
    dt string comment '统计日期',
    wk_dt string comment '统计日期所在周',
    wastage_count bigint comment '回流设备数'
）comment '本周回流用户数'
row format delimited field terminated by '\t' location '/data/hive/back_count';

数据导入：

insert into table back_count
    select 
        '2021-05-22',
        concat(date_add(next_day('2021-05-22','MO'),-7),'-',date_add(next_day('2021-05-22','MO'),-1)),
        count(*)
    from
    (
          select
        mid_id
    from dwt_uv_topic
    where login_date_last>=date_add(next_day('2021-05-22','MO'),-7) 
    and login_date_last<= date_add(next_day('2021-05-22','MO'),-1)
    and login_date_first<date_add(next_day('2021-05-22','MO'),-7)
)current_wk
left join
(
    select
        mid_id
    from dws_uv_detail_daycount
    where dt>=date_add(next_day('2021-05-22','MO'),-7*2) 
    and dt<= date_add(next_day('2021-05-22','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;