大数据学习第八天

最新推荐文章于 2024-09-10 22:51:31 发布

Zafike

最新推荐文章于 2024-09-10 22:51:31 发布

阅读量83

点赞数 3

文章标签：学习大数据 mysql 笔记

本文链接：https://blog.csdn.net/Zafike/article/details/134146019

版权

拖了好多天没更了...

今天来一道牛客网的用户增长场景（某度信息流）的SQL题，直接上题目：

2021年11月每天新用户的次日留存率查询表：【tb_user_log】

输入：

DROP TABLE IF EXISTS tb_user_log;
CREATE TABLE tb_user_log (
    id INT PRIMARY KEY AUTO_INCREMENT COMMENT '自增ID',
    uid INT NOT NULL COMMENT '用户ID',
    artical_id INT NOT NULL COMMENT '视频ID',
    in_time datetime COMMENT '进入时间',
    out_time datetime COMMENT '离开时间',
    sign_in TINYINT DEFAULT 0 COMMENT '是否签到'
) CHARACTER SET utf8 COLLATE utf8_bin;

INSERT INTO tb_user_log(uid, artical_id, in_time, out_time, sign_in) VALUES
  (101, 0, '2021-11-01 10:00:00', '2021-11-01 10:00:42', 1),
  (102, 9001, '2021-11-01 10:00:00', '2021-11-01 10:00:09', 0),
  (103, 9001, '2021-11-01 10:00:01', '2021-11-01 10:01:50', 0),
  (101, 9002, '2021-11-02 10:00:09', '2021-11-02 10:00:28', 0),
  (103, 9002, '2021-11-02 10:00:51', '2021-11-02 10:00:59', 0),
  (104, 9001, '2021-11-02 10:00:28', '2021-11-02 10:00:50', 0),
  (101, 9003, '2021-11-03 11:00:55', '2021-11-03 11:01:24', 0),
  (104, 9003, '2021-11-03 11:00:45', '2021-11-03 11:00:55', 0),
  (105, 9003, '2021-11-03 11:00:53', '2021-11-03 11:00:59', 0),
  (101, 9002, '2021-11-04 11:00:55', '2021-11-04 11:00:59', 0);

输出：

	2021-11-01|0.67
	2021-11-02|1.00
	2021-11-03|0.00

次日留存率为当天新增的用户数中第二天又活跃了的用户数占比
如果in_time-进入时间和out_time-离开时间跨天了，在两天里都记为该用户活跃过，结果按日期升序

解题：

先把表建好 t_user_log

做题的第一步，先审题，提取一下题目里的关键字：
2021年11月每天新用户的次日留存率
好！没看懂！
开个玩笑，题目要求时间在 2021年11月的每一天，查出在这段时间内每天的新用户，在第二天再次访问的比例
题目里也解释了什么是次日留存率，通俗点来说就是：当天所有被记录的新用户，在第二天再次被记录的个数(再次活跃的个数)，占当天所有被记录的新用户的比例。
有点绕是不是？
举个例子：比方说 11月1号记录了10个新用户，11月2号记录的所有用户数据里，有5个是11月1号被记录的新用户，那么11月1号新用户的次日留存率就是 0.5 。
所以说：次日留存率 = 第二天再次被记录的第一天用户的个数 ÷ 第一天记录的新用户个数
我们需要查的数据也就明确了

下面来分析一下这张表。
表中的字段有 id（自增id）、uid（用户id）、artical_id（视频id）、in_time（进入时间）、out_time（离开时间）、sign_in（是否签到）
在这些字段里，我们需要用到的有 uid、in_time、out_time；
题目要求我们求每一日的结果，所以 in_time和out_time 中时间的时分秒不影响判断，可以用函数 DATE( ) 来转换成只有年月日的格式来方便判断。

分析完后就开始梳理思路啦。

首先，我们需要得出每日新增用户表和用户活跃表；
用这两张表进行连表，条件过滤要判断两张表的uid相同，同时第一次出现的uid要在第二天再次出现；
然后对连表后的数据进行判断，判断时间是否在2021年11月之内
然后将查到的数据按照日期分组，使用查到的当日新用户个数与次日留存的新用户个数计算次日留存率
最后再严谨些，对日期进行排序即可。

下面就开始实现思路：

查出每日新增用户表和用户活跃表
1. a.每日新增用户表

想要判断是否是新增用户，就要对用户进行分组，然后判断该用户第一次(最小)进入时间

SQL如下：

select uid,min(date(in_time)) dt from tb_user_log group by uid

表如下：

b.用户活跃表

前面题目中说过，in_time与out_time可能会有跨天的情况，这种情况下，就把跨越的两天都算作活跃(就是都记录到活跃表内)
这样的话，就要把进入时间与离开时间分成两个表并在一起

SQL如下：

select uid , date(in_time) dt from tb_user_log
union
select uid , date(out_time) from tb_user_log

表如下：

2.连表判断

思路里分析的连表时需要两个判断，uid相同且第一天出现的uid要在第二天再次出现
uid相同好说 t1.uid=t2.uid 即可；但怎么判断连续两天都出现同一个uid 就有点难了。
这里就要用到一个函数了： date_sub(date,INTERVAL expr type)
这个函数的作用是对逗号前的指定日期，减去逗号后的指定时间间隔
date指合法的日期表达式；expr 参数是需要操作的时间间隔，type是时间格式，可以是day、month、year等等。

date_sub(dt,INTERVAL 1 day)

这里就用这个函数把t2表中的日期减去一天，然后判断t1表中是否存在相同日期与uid的数据，即：

t1.uid=t2.uid and t1.dt=date_sub(t2.dt,INTERVAL 1 day)

那么这里的SQL就一个这样写：

select *
from (
    select uid,min(date(in_time)) dt from tb_user_log group by uid
	) as t1  -- 每天新用户表
left join (
	select uid , date(in_time) dt from tb_user_log
    union
    select uid , date(out_time) from tb_user_log
	) as t2 -- 用户活跃表
on t1.uid=t2.uid and t1.dt=date_sub(t2.dt,INTERVAL 1 day)

查出的表如下：

这张表如果没看懂的话，说明你前面没仔细看。
来分析一下，首先101用户、102用户与103用户被记录为新用户的时间是11月01日，而第二天也就是11月02日只有101和103用户活跃再次被记录，102用户11月02日没有活跃，所以没有被记录；
那么2021年11与01日的新增用户就是 count(t1.uid) ：3个；
次日活跃用户 count(t2.uid) ：2个。
那答案就很明显了

3.判断时间是否在2021年11月

这里的判断因为表中数据太少，就算判断了，对结果也没有影响
这里的判断有一个函数可以解决： date_format(date,'%Y-%m') 格式化日期
就是将逗号前的日期转换成逗号后规定的格式，%Y：年，4 位； %m：月，数值(00-12)
有了这个函数那就简单了：

date_format(t1.dt,'%Y-%m') = '2021-11'

把t1的时间转成年-月格式字符串，然后 = 2021-11 就行了

select *
from (
    select uid,min(date(in_time)) dt from tb_user_log group by uid
	) as t1  -- 每天新用户表
left join (
	select uid , date(in_time) dt from tb_user_log
    union
    select uid , date(out_time) from tb_user_log
	) as t2 -- 用户活跃表
on t1.uid=t2.uid and t1.dt=date_sub(t2.dt,INTERVAL 1 day)
where date_format(t1.dt,'%Y-%m') = '2021-11'

查出来的表没有变化：

4.对日期分组，并计算次日留存率，对日期升序排序

需要的数据现在都已经查出来了，下面就要对日期分组，然后在select上计算每组日期的次日留存率了；
次日留存率 = 第二天再次被记录的第一天用户的个数 ÷ 第一天记录的新用户个数
次日留存率 = count(t2.uid) / count(t1.uid)
这里有个细节，最终结果表里的次日留存率是小数点后两位数
我们这里可以用函数 round(x,d) 四舍五入来实现
x ：表示要处理的数； d ：表示要保留几位小数，即：

round(count(t2.uid)/count(t1.uid),2)

那么最终的SQL如下：

select t1.dt '日期',round(count(t2.uid)/count(t1.uid),2) '新用户次日留存率'
from (
    select uid,min(date(in_time)) dt from tb_user_log group by uid
	) as t1  
left join (
	select uid , date(in_time) dt from tb_user_log
    union
    select uid , date(out_time) from tb_user_log
	) as t2 
on t1.uid=t2.uid and t1.dt=date_sub(t2.dt,INTERVAL 1 day)
where date_format(t1.dt,'%Y-%m') = '2021-11'
group by t1.dt
order by t1.dt