电商数仓3.0 ADS层设备主题相关需求分析处理

设备活跃数(日、周、月)

增加 is_weekend 和 is_monthend 字段的意义在于将日周月三个量的计算频率保持一致，都是每天执行一次，后续如果想求完整的周活只要是 字段为 Y 就行

-- 创建表
drop table if exists ads_uv_count;
create external table ads_uv_count(
    `dt` string COMMENT '统计日期',
    `day_count` bigint COMMENT '当日用户数量',
    `wk_count`  bigint COMMENT '当周用户数量',
    `mn_count`  bigint COMMENT '当月用户数量',
    `is_weekend` string COMMENT 'Y,N是否是周末,用于得到本周最终结果',
    `is_monthend` string COMMENT 'Y,N是否是月末,用于得到本月最终结果' 
) COMMENT '活跃设备数'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_uv_count/';

-- 导入数据
insert into table ads_uv_count
select
    '2020-06-15',
    -- 日活
    sum(if(login_date_last='2020-06-15',1,0)),
    -- 周活 末次登录时间小于等于本周周末，大于等于本周周一
    sum(if(login_date_last>=date_add(next_day('2020-06-15','MO'),-7) and login_date_last>=date_add(next_day('2020-06-15','MO'),-1),1,0)),
    -- 月活 将取到的末次时间进行格式化去掉日，保留年月，就可以查出所有本月出现的设备
    sum(if(date_format(login_date_last,'yyyy-MM')=date_format('2020-06-15','yyyy-MM'),1,0)),
    -- 是否是周末
    if('2020-06-15'=date_add(next_day('2020-06-15','MO'),-1),'Y','N'),
    -- 是否是月末
    if('2020-06-15'=last_day('2020-06-15'),'Y','N')
from dwt_uv_topic;

每日新增设备

每天新增设备数量

drop table if exists ads_new_mid_count;
create external table ads_new_mid_count
(
    `create_date`     string comment '创建时间' ,
    `new_mid_count`   BIGINT comment '新增设备数量' 
)  COMMENT '每日新增设备数量'
row format delimited fields terminated by '\t'
location '/warehouse/gmall/ads/ads_new_mid_count/';

-- 插入数据
insert into table ads_new_mid_count
select
    '2020-06-15',
    count(*)
from dwt_uv_topic
where login_date_first='2020-06-15';

沉默用户数

只在安装当天启动过，且启动时间是在7天前

-- adsc层-设备-沉默用户数
drop table if exists ads_silent_count;
create external table ads_silent_count(
    `dt`<