|NO.Z.00032|——————————|BigDataEnd|——|Hadoop&PB级数仓.V16|——|PB数仓.v16|会员活跃度分析|Hive on Tez&会员留存率计算|

一、会员留存率的计算：会员留存率计算概述

### --- 修改后的代码（计算留存率）：

select t1.*, t2.* from ads.ads_member_retention_count t1 join
ads.ads_new_member_cnt t2 on
t1.add_date=t2.dt
where t1.dt='2020-07-23';

### --- 修改ads_load_member_retention.sh脚本bug文件

[root@hadoop02 ~]# vim /data/yanqidw/script/member_active/ads_load_member_retention.sh
#!/bin/bash

source /etc/profile

if [ -n "$1" ] ;then
    do_date=$1
else
    do_date=`date -d "-1 day" +%F`
fi

sql="
insert overwrite table ads.ads_member_retention_count partition (dt='$do_date')
select add_date, retention_date, count(*) retention_count
from dws.dws_member_retention_day 
where dt='$do_date' group by add_date, retention_date;

insert overwrite table ads.ads_member_retention_rate
partition (dt='$do_date')
select t1.add_date,
        t1.retention_day,
        t1.retention_count,
        t2.cnt,
        t1.retention_count/t2.cnt*100
from ads.ads_member_retention_count t1 join ads.ads_new_member_cnt t2 on t1.add_date=t2.dt
where t1.dt='$do_date';
"
hive -e "$sql"

### --- 计算用户留存率。
~~~     备注：主要改的是连接条件。将连接条件改为：t1.add_date=t2.dt

[root@hadoop02 ~]# SCRIPT_HOME=/data/yanqidw/script/member_active
[root@hadoop02 ~]# sh $SCRIPT_HOME/ads_load_member_retention.sh 2020-07-23

### --- 查看2020-07-23用户留存率

hive (default)> show partitions ads.ads_member_retention_rate;
partition
dt=2020-07-21
dt=2020-07-22
dt=2020-07-23

hive (default)> select * from ads.ads_member_retention_rate;
2020-07-21  1   4000    5000    80.00   2020-07-22  # 21日到22日留存会员为4000留存率为80%
2020-07-21  2   2000    9999    20.00   2020-07-23  # 21日到23日留存会员为2000留存率为20%
2020-07-22  1   5000    5000    100.00  2020-07-23  # 21日到23日留存会员为5000留存率为100%

---

 

 

 

 

 

 

 

 

 

---

Walter Savage Landor:strove with none,for none was worth my strife.Nature I loved and, next to Nature, Art:I warm'd both hands before the fire of life.It sinks, and I am ready to depart

                                                                                                                                                   ——W.S.Landor

---