目录
1.前言
计算原则:一般看到什么什么率啥的,指标率=a/b,都要明确分子,分母代表的含义,然后在相除 。
新增留存率具体是指留存用户数与新增用户数的比值。
2、用到的函数
1.datediff(time1,time2):日期比较函数,time1-time2,返回一直int(整数)
select datediff('2020-06-14 11','2020-06-10');
返回结果: 4
2.if(表达式,a,b):当表达式条件为真,返回a,否则返回b的值
select if(1=2,100,200); -->200
select if(1=1,100,200); -->100
if(time='2020-06-14',1,0): 表达式为真,返回1,否则返回0;
3. cast():类型转换函数
select cast(1.0001 as int) -->1
cast(id as string) -->将id字段的类型改成 string类型
3、指标理解
留存率理解:2020-06-14新增100个用户,
1日之后(2020-06-15)这100人中有80个人活跃了,
那2020-06-14的1日留存数则为80,2020-06-14的1日留存率则为80%(80/100)。
4、创建表和导入数据
4.1目标表
1.建表(目标表)
DROP TABLE IF EXISTS ads_user_retention;
CREATE EXTERNAL TABLE ads_user_retention
(
`dt` STRING COMMENT '统计日期',
`create_date` STRING COMMENT '用户新增日期',
`retention_day` INT COMMENT '截至当前日期留存天数',
`retention_count` BIGINT COMMENT '留存用户数量',
`new_user_count` BIGINT COMMENT '新增用户数量',
`retention_rate` DECIMAL(16, 2) COMMENT '留存率'
) COMMENT '用户留存率'
ROW FORMAT DELIMITED FIELDS TERMINATED BY '\t'
LOCATION '/warehouse/gmall/ads/ads_user_retention/';
4.2 数据来源表
数据来源表:见另一篇博客
4.2.1 用户登录历史表
dws_user_user_login_td:是用户域用户粒度登录历史至今汇总表,
如图所示:以为是历史累计表,当我们拿14号分区数据时,最后登录时间为10号 的用户数据也包括在里面。
4.2.2 用户注册表
dwd_user_login_inc:用户域用户注册事务事实表,
5、sql解析
5.1一般思路做法
下面SQL中 求06-14留存的用户数的图解
2020-06-13日 的 1 日留存率:在13号新增的用户,而且在14号活跃(有登录)的用户
留存率= 当天的留存用户数/昨天新增用户数
步骤:
(1)求06-13新增用户的数据 a
(2)求06-14留存的用户数 b
(3)做差 b/a
1.求06-13新增用户的数据
select user_id
from dwd_user_register_inc
where dt='2020-06-13'
2.求06-14留存的用户数
select count(*)
from
(
select user_id
from dwd_user_register_inc
where dt='2020-06-13'
)t1
join
(
select user_id
from dws_user_user_login_td
where dt='2020-06-14' and login_date_last='2020-06-14'
--拿到当天的分区,而且用户最后登录时间是 06-14号的数据
)t1
on t1.user_id=t2.user_id
--两个表连接,拿到既是昨天新增,又是有今天登录的用户数据
3.
select
'2020-06-13' ,
1 ,-- 06-13号的1日留存率,
b.cnt , --留存用户数,
a.cnt , -- 新增用户数
cast(b.cnt/a.cnt as decimal(16,2)) --留存率
from
(
select count(user_id) as cnt1
from dwd_user_register_inc
where dt='2020-06-13'
)a,
(
select count(*) as cnt
from
(
select user_id
from dwd_user_register_inc
where dt='2020-06-13'
)t1
join
(
select user_id
from dws_user_user_login_td
where dt='2020-06-14' and login_date_last='2020-06-14'
--拿到当天的分区,而且用户最后登录时间是 06-14号的数据
)t1
on t1.user_id=t2.user_id
--两个表连接,拿到既是昨天新增,又是有今天登录的用户数据
)b
5.2 减少部分重复计算 方法
方法1中求新增用户数计算了两次
所以我们可以减少一个子查询
5.3 实际开发的做法(思路和上面差不多,不过更灵活)
思路:
(1)where筛选出用户注册表(dwd_user..)中有注册的用户。
(2)在用户历史登录表中求14号分区的数据。
(3)根据 dt 时间分组,就可以知道用户注册表中每个用户是在哪一天注册(新增)的了,
所以count(*)时,也是统计当天分区 有用户注册的数据(新增的用户数)。
(4)if()筛选14号留存的用户数, 然后留存率=某天用户留存数/用户新增数。
!这里有个小细节:
group by dt (分组时只用来dt), 但是在select中使用了不是分组字段的
datediff('2020-06-14',dt),也没有报错的原因是
-->
group by 分组后 ,一般select可以查询分组字段和max()和sum()等聚合函数,
还可以查询 使用了udf函数的分组字段,满足一进一出即可,如datediff('2020-06-14',dt)。
select
'2020-06-14' ,
dt ,
datediff('2020-06-14',dt) ,
sum(if(login_date_last='2020-06-14',1,0)) retention_count,
count(*) new_user_count,
cast(sum(if(login_date_last='2020-06-14',1,0))/count(*)*100 as decimal(16,2)) retention_rate
from
(
select
user_id,
dt
from dwd_user_register_inc
where dt>=date_add('2020-06-14',-7)
and dt<'2020-06-14'
)t1
join
(
select
user_id,
login_date_last
from dws_user_user_login_td
where dt='2020-06-14'
)t2
on t1.user_id=t2.user_id
group by dt;