SQL常见问题之如何分析周活跃率？（每周的活跃率）——步骤具体且明确

【题目】

有两张表：

1）“用户登录信息表”，包含2个字段：用户id、登录日期。

 2）“已注册用户信息表”，包含2个字段：用户id、注册日期。

【问题】计算周活跃用户率。

【解题思路】

1. 活跃用户率

“活跃用户率”是观察用户活跃度的常见指标，一般指所有注册用户中活跃用户的比例。当活跃周期设定为“周”时，计算的就是“周活跃用户率”。

周活跃用户率 = 周活跃用户数 / 当前注册用户数

值得注意的是，“当前注册用户数”指截止本周最后一天之前注册的用户数量。因为，在这之后注册的用户必然不会在本周活跃。

2. 日期处理

为了获取“用户登录信息表”中“登录日期”对应周的信息，我们需要对该字段进行处理。

函数year的作用是日期的年份，使用方法是：

 
year(日期字段名)

 函数week的作用是返回一个int型的数值，代表指定日期在所在年的周数信息，使用方法是：

week(日期字段名,参数)

其中，“参数”不填写的情况下，默认值为0，代表周日为本周第一天；“参数”为1时，代表周一为本周第一天；“参数”为2时，代表周二为本周第一天；以此类推。

select 用户id,
       登录日期,
       year(登录日期) as 年份,
       week(登录日期,1) as 周数
from 用户登录信息表;

该结果记为表t1，在此基础上计算每周活跃用户数。

select t1.年份,
       t1.周数,
       count(distinct 用户id) as 活跃用户数
from
(
select 用户id,
       登录日期,
       year(登录日期) as 年份,
       week(登录日期,1) as 周数
from 用户登录信息表
) as t1
group by t1.年份,t1.周数;

 

该结果记为表t2，记录的是每周的活跃用户数。

对“已注册用户信息表”的“注册日期”字段进行处理：

select 用户id,
       注册日期,
       year(注册日期) as 年份,
       week(注册日期,1) as 周数
from 已注册用户信息表;

 

该结果记为表t3。

为了后面计算“当前注册用户数”，需要获取“用户登录信息表”中的“活跃最大年份”，以及对应的“活跃最大周数”。

select year(max(登录日期)) as 活跃最大年份,
       week(max(登录日期),1) as 活跃最大周数
from 用户登录信息表;

 

为了计算每个活跃周的“当前注册用户数”，将表t3和表t4进行联结。

 
select count(distinct t3.用户id) as 当前注册用户数
from
(
select 用户id,
       注册日期,
       year(注册日期) as 年份,
       week(注册日期,1) as 周数
from 已注册用户信息表
) as t3
left join
(
select year(max(登录日期)) as 活跃最大年份,
       week(max(登录日期),1) as 活跃最大周数
from 用户登录信息表
) as t4 on 1 = 1
where t3.年份 < t4.活跃最大年份 or (t3.年份 = t4.活跃最大年份 and t3.周数 <= t4.活跃最大周数);

 

该结果记为表t5。

最后，将表t2和表t5联结，计算最终结果：

 
select t2.年份,
       t2.周数,
       t2.活跃用户数,
       t5.当前注册用户数,
       t2.活跃用户数/t5.当前注册用户数 as 周活跃用户率
from
(
select t1.年份,
       t1.周数,
       count(distinct 用户id) as 活跃用户数
from
(
select 用户id,
       登录日期,
       year(登录日期) as 年份,
       week(登录日期,1) as 周数
from 用户登录信息表
) as t1
group by t1.年份,t1.周数
) as t2
left join
(
select count(distinct t3.用户id) as 当前注册用户数
from
(
select 用户id,
       注册日期,
       year(注册日期) as 年份,
       week(注册日期,1) as 周数
from 已注册用户信息表
) as t3
left join
(
select year(max(登录日期)) as 活跃最大年份,
       week(max(登录日期),1) as 活跃最大周数
from 用户登录信息表
) as t4 on 1 = 1
where t3.年份 < t4.活跃最大年份 or (t3.年份 = t4.活跃最大年份 and t3.周数 <= t4.活跃最大周数)
) as t5 on 1 = 1;

 

整体思路分析理解： 

        和之前所求的周活跃率不同的有两个，①之前的数据在一个表中，所求周活跃人数是在当前一周时间范围内用户人数（去重之后），总人数就是去重之后的总人数；②本次所求的是一年中每周的活跃率

步骤解析：

①求出用户登录信息表中的年份和周数

这样可以求出年份的周数的活跃用户数，如图

②求已注册用户信息表的年份和周数，此时的数据要远大于用户登录信息表的数据

③返回用户登录信息表中的最大年份和周数，为了在已注册用户信息表的年份和周数的大集合中取其子集 ，求出当前用户数

④临时表之间相互关联最后得到结果，连接条件为1也就是相当于直接添加了一列