离线电商项目常重点Sql

活跃设备数（日、周、月）

需求定义：

日活：当日活跃的设备数

周活：当周活跃的设备数

月活：当月活跃的设备数

1）建表语句

hive (gmall)>

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/';

1. 导入数据

hive (gmall)>

insert into table ads_uv_count

select  

    '2021-06-14' dt,

    daycount.ct,

    wkcount.ct,

    mncount.ct,

    if(date_add(next_day('2021-06-14','MO'),-1)='2021-06-14','Y','N') ,

    if(last_day('2021-06-14')='2021-06-14','Y','N')

from

(

    select  

        '2021-06-14' dt,

        count(*) ct

    from dwt_uv_topic

    where login_date_last='2021-06-14'  

)daycount join

(

    select  

        '2021-06-14' dt,

        count (*) ct

    from dwt_uv_topic

    where login_date_last>=date_add(next_day('2021-06-14','MO'),-7)

    and login_date_last<= date_add(next_day('2021-06-14','MO'),-1)

) wkcount on daycount.dt=wkcount.dt

join

(

    select  

        '2021-06-14' dt,

        count (*) ct

    from dwt_uv_topic

    where date_format(login_date_last,'yyyy-MM')=date_format('2021-06-14','yyyy-MM')  

)mncount on daycount.dt=mncount.dt;

3）查询导入结果

hive (gmall)> select * from ads_uv_count;

每日新增设备

1）建表语句

hive (gmall)>

drop table if exists ads_new_mid_count;

create external table ads_new_mid_count

(