大数据项目之电商数仓（总结）（十一）:用户行为数据仓库

问题导读：
1、如何分析沉默用户、本周回流用户数、流失用户？
2、如何分析最近连续3周活跃用户？
3、如何分析最近七天内连续三天活跃用户数？
4、如何计算本周回流数据？


上一篇：大数据项目之电商数仓（总结）（十）:用户行为数据仓库

第9章 新数据准备
为了分析沉默用户、本周回流用户数、流失用户、最近连续3周活跃用户、最近七天内连续三天活跃用户数，需要准备2019-02-12、2019-02-20日的数据。
1）2019-02-12数据准备
（1）修改日志时间

[kgg@hadoop102 ~]$ dt.sh 2019-02-12
复制代码

（2）启动集群

[kgg@hadoop102 ~]$ cluster.sh start
复制代码

（3）生成日志数据

[kgg@hadoop102 ~]$ lg.sh
复制代码

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

[kgg@hadoop102 ~]$ ods_log.sh 2019-02-12
复制代码

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

[kgg@hadoop102 ~]$ dwd_start_log.sh 2019-02-12
[kgg@hadoop102 ~]$ dwd_base_log.sh 2019-02-12
[kgg@hadoop102 ~]$ dwd_event_log.sh 2019-02-12
复制代码

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

[kgg@hadoop102 ~]$ dws_uv_log.sh 2019-02-12
复制代码

（7）验证

hive (gmall)> select * from dws_uv_detail_day where dt='2019-02-12' limit 2;
复制代码

2）2019-02-20数据准备
（1）修改日志时间

[kgg@hadoop102 ~]$ dt.sh 2019-02-20
复制代码

（2）启动集群

[kgg@hadoop102 ~]$ cluster.sh start
复制代码

（3）生成日志数据

[kgg@hadoop102 ~]$ lg.sh
复制代码

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

[kgg@hadoop102 ~]$ ods_log.sh 2019-02-20
复制代码

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

[kgg@hadoop102 ~]$ dwd_start_log.sh 2019-02-20
[kgg@hadoop102 ~]$ dwd_base_log.sh 2019-02-20
[kgg@hadoop102 ~]$ dwd_event_log.sh 2019-02-20
复制代码

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

[kgg@hadoop102 ~]$ dws_uv_log.sh 2019-02-20
复制代码

（7）验证

hive (gmall)> select * from dws_uv_detail_day where dt='2019-02-20' limit 2;
复制代码

第10章 需求四：沉默用户数
沉默用户：指的是只在安装当天启动过，且启动时间是在一周前

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

10.2 ADS层


1）建表语句

hive (gmall)>
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 '\t'
location '/warehouse/gmall/ads/ads_silent_count';
复制代码

2）导入2019-02-20数据

hive (gmall)>
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）查询导入数据

hive (gmall)> select * from ads_silent_count;
复制代码

10.3 编写脚本
1）在hadoop102的/home/kgg/bin目录下创建脚本

[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）增加脚本执行权限

[kgg@hadoop102 bin]$ chmod 777 ads_silent_log.sh
复制代码

3）脚本使用

[kgg@hadoop102 module]$ ads_silent_log.sh 2019-02-20
复制代码

4）查询结果

hive (gmall)> select * from ads_silent_count;
复制代码

5）脚本执行时间
企业开发中一般在每日凌晨30分~1点

第11章 需求五：本周回流用户数
本周回流=本周活跃-本周新增-上周活跃
11.1 DWS层
使用日活明细表dws_uv_detail_day作为DWS层数据

11.2 ADS层


1）建表语句

hive (gmall)>
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 '\t'
location '/warehouse/gmall/ads/ads_back_count';
复制代码

2）导入数据：

hive (gmall)> 
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）查询结果

hive (gmall)> select * from ads_back_count;
复制代码

11.3 编写脚本
1）在hadoop102的/home/kgg/bin目录下创建脚本

[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）增加脚本执行权限

[kgg@hadoop102 bin]$ chmod 777 ads_back_log.sh
复制代码

3）脚本使用

[kgg@hadoop102 module]$ ads_back_log.sh 2019-02-20
复制代码

4）查询结果

hive (gmall)> select * from ads_back_count;
复制代码

5）脚本执行时间
企业开发中一般在每周一凌晨30分~1点

最新经典文章，欢迎关注公众号