项目实战从0到1之hive（43）大数据项目之电商数仓（用户行为数据）（十一）

第14章 新数据准备

为了分析沉默用户、本周回流用户数、流失用户、最近连续3周活跃用户、最近七天内连续三天活跃用户数，需要准备2019-02-12、2019-02-20日的数据。

1）2019-02-12数据准备

（1）修改日志时间

dt.sh 2019-02-12

（2）启动集群

 cluster.sh start

（3）生成日志数据

lg.sh

（4）将HDFS数据导入到ODS层

ods_log.sh 2019-02-12

（5）将ODS数据导入到DWD层

dwd_start_log.sh 2019-02-12
dwd_base_log.sh 2019-02-12
 dwd_event_log.sh 2019-02-12

（6）将DWD数据导入到DWS层

dws_uv_log.sh 2019-02-12

（7）验证

select * from dws_uv_detail_day where dt='2019-02-12' limit 2;

2）2019-02-20数据准备

（1）修改日志时间

dt.sh 2019-02-20

（2）启动集群

cluster.sh start

（3）生成日志数据

lg.sh

（4）将HDFS数据导入到ODS层

ods_log.sh 2019-02-20

（5）将ODS数据导入到DWD层

dwd_start_log.sh 2019-02-20
dwd_base_log.sh 2019-02-20
dwd_event_log.sh 2019-02-20

（6）将DWD数据导入到DWS层

 dws_uv_log.sh 2019-02-20

（7）验证

select * from dws_uv_detail_day where dt='2019-02-20' limit 2;

第15章 需求四：沉默用户数

沉默用户：指的是只在安装当天启动过，且启动时间是在一周前

15.1 DWS层

使用日活明细表dws_uv_detail_day作为DWS层数据

15.2 ADS层

1）建表语句

drop table if exists ads_silent_count;
create external table ads_silent_count( 
    `dt` string COMMENT '统计日期',
    `silent_count` bigint COMMENT '沉默设备数'
) 
row format delimited fields terminated by '	'
location '/warehouse/gmall/ads/ads_silent_count';

2）导入2019-02-20数据

insert into table ads_silent_count
select 
    '2019-02-20' dt,
    count(*) silent_count
from 
(
    select mid_id
    from dws_uv_detail_day
    where dt<='2019-02-20'
    group by mid_id    
    having count(*)=1 and max(dt)<date_add('2019-02-20',-7)
) t1;

3）查询导入数据

select * from ads_silent_count;

15.3 编写脚本

1）创建脚本

[kgg@hadoop102 bin]$ vim ads_silent_log.sh
    在脚本中编写如下内容
#!/bin/bash
​
hive=/opt/module/hive/bin/hive
APP=gmall
​
if [ -n "$1" ];then
    do_date=$1
else
    do_date=`date -d "-1 day" +%F`
fi
​
echo "-----------导入日期$do_date-----------"
​
sql="
insert into table "$APP".ads_silent_count
select 
    '$do_date' dt,
    count(*) silent_count
from 
(
    select 
        mid_id
    from "$APP".dws_uv_detail_day
    where dt<='$do_date'
    group by mid_id
    having count(*)=1 and min(dt)<=date_add('$do_date',-7)
)t1;"
​
$hive -e "$sql"

2）增加脚本执行权限

chmod 777 ads_silent_log.sh

3）脚本使用

ads_silent_log.sh 2019-02-20

4）查询结果

select * from ads_silent_count;

5）脚本执行时间

企业开发中一般在每日凌晨30分~1点

第16章 需求五：本周回流用户数

本周回流=本周活跃-本周新增-上周活跃

16.1 DWS层

使用日活明细表dws_uv_detail_day作为DWS层数据

16.2 ADS层

1）建表语句

drop table if exists ads_back_count;
create external table ads_back_count( 
    `dt` string COMMENT '统计日期',
    `wk_dt` string COMMENT '统计日期所在周',
    `wastage_count` bigint COMMENT '回流设备数'
) 
row format delimited fields terminated by '	'
location '/warehouse/gmall/ads/ads_back_count';

2）导入数据：

insert into table ads_back_count
select 
   '2019-02-20' dt,
   concat(date_add(next_day('2019-02-20','MO'),-7),'_',date_add(next_day('2019-02-20','MO'),-1)) wk_dt,
   count(*)
from 
(
    select t1.mid_id
    from 
    (
        select    mid_id
        from dws_uv_detail_wk
        where wk_dt=concat(date_add(next_day('2019-02-20','MO'),-7),'_',date_add(next_day('2019-02-20','MO'),-1))
    )t1
    left join
    (
        select mid_id
        from dws_new_mid_day
        where create_date<=date_add(next_day('2019-02-20','MO'),-1) and create_date>=date_add(next_day('2019-02-20','MO'),-7)
    )t2
    on t1.mid_id=t2.mid_id
    left join
    (
        select mid_id
        from dws_uv_detail_wk
        where wk_dt=concat(date_add(next_day('2019-02-20','MO'),-7*2),'_',date_add(next_day('2019-02-20','MO'),-7-1))
    )t3
    on t1.mid_id=t3.mid_id
    where t2.mid_id is null and t3.mid_id is null
)t4;

3）查询结果

select * from ads_back_count;

16.3 编写脚本

1）创建脚本

[kgg@hadoop102 bin]$ vim ads_back_log.sh
    在脚本中编写如下内容
#!/bin/bash
​
if [ -n "$1" ];then
    do_date=$1
else
    do_date=`date -d "-1 day" +%F`
fi
​
hive=/opt/module/hive/bin/hive
APP=gmall
​
echo "-----------导入日期$do_date-----------"
​
sql="
insert into table "$APP".ads_back_count
select 
       '$do_date' dt,
       concat(date_add(next_day('$do_date','MO'),-7),'_',date_add(next_day('$do_date','MO'),-1)) wk_dt,
       count(*)
from 
(
    select t1.mid_id
    from 
    (
        select mid_id
        from "$APP".dws_uv_detail_wk
        where wk_dt=concat(date_add(next_day('$do_date','MO'),-7),'_',date_add(next_day('$do_date','MO'),-1))
    )t1
    left join
    (
        select mid_id
        from "$APP".dws_new_mid_day
        where create_date<=date_add(next_day('$do_date','MO'),-1) and create_date>=date_add(next_day('$do_date','MO'),-7)
    )t2
    on t1.mid_id=t2.mid_id
    left join
    (
        select mid_id
        from "$APP".dws_uv_detail_wk
        where wk_dt=concat(date_add(next_day('$do_date','MO'),-7*2),'_',date_add(next_day('$do_date','MO'),-7-1))
    )t3
    on t1.mid_id=t3.mid_id
    where t2.mid_id is null and t3.mid_id is null
)t4;"
​
$hive -e "$sql"

2）增加脚本执行权限

chmod 777 ads_back_log.sh

3）脚本使用

 ads_back_log.sh 2019-02-20

4）查询结果

select * from ads_back_count;

5）脚本执行时间

企业开发中一般在每周一凌晨30分~1点