SQL助你面大厂（N日留存率）

浅浅的花香味﹌ 2023-10-14 23:48 86阅读 0赞

在一些游戏中比较关注的就是留存率，随着王者的更新，画质在不断的优化，可是匹配机制确实一成不变，导致很多人逐渐退游，那么我们的王者的官方是怎么知道有这么多人退游么？退游了是相当于删除玩家对应的那条数据库信息么？很显然不是，那么怎么才能统计出玩家的留存率问题？这也成了游戏公司比较看重的一点，所以今天我们来讲解一下这类问题：

现在你是腾讯游戏旗下的工作人员，有一个任务，就是最近曙光英雄的出现，导致很多王者玩家开始转战曙光英雄，然后老板让你统计一下从2023-08-20号开始，后面2天的玩家留存率，你准备怎么实现这个任务？如果完成不了就会被K,于是你打开了我的博客，学习了起来！！！

数据如下：

CREATE TABLE lea(
    cuid VARCHAR(20),
    game VARCHAR(20),
    cur_date VARCHAR(20)
    )
    INSERT INTO lea (cuid,game,cur_date) VALUES('1','王者','2023-08-20'),('1','王者','2023-08-21'),('1','王者','2023-08-22'),('2','王者','2023-08-20')
    ,('2','王者','2023-08-21'),('3','王者','2023-08-21'),('1','王者','2023-08-28')

![2086d1c9641347b299699684a0c1d858.png][]

因为老板让你统计2天的统计量，你要讨老板欢心，肯定想尽一切办法去优化查询数据，当你看到了这其中还有其他日期的数据，你果断的选择先对数据进行过滤，再进行查询

SELECT cuid,
    COUNT(IF(cur_date='2023-08-20',1,NULL)) AS '2023-08-20',
    COUNT(IF(cur_date='2023-08-21',1,NULL)) AS '2023-08-21',
    COUNT(IF(cur_date='2023-08-22',1,NULL)) AS '2023-08-22'
    FROM lea WHERE cur_date IN('2023-08-20','2023-08-21','2023-08-22')
    GROUP BY cuid

![78bd1dd973344fbdad5d8931a9d293b7.png][]

一看查询结果居然还有2023-08-20没有登录的用户，这条数据显然是脏数据，我们也对其进行过滤

SELECT cuid,
    COUNT(IF(cur_date='2023-08-20',1,NULL)) AS Login20,
    COUNT(IF(cur_date='2023-08-21',1,NULL)) AS Login21,
    COUNT(IF(cur_date='2023-08-22',1,NULL)) AS Login22
    FROM lea WHERE cur_date IN('2023-08-20','2023-08-21','2023-08-22')
    GROUP BY cuid
    HAVING  Login20>0

![99d97de6b93f4e88b977af50b6739ebd.png][]

我们再对这张表进行查询，嵌套一个子查询：

SELECT '2023-08-20的留存情况' AS TYPE,
        COUNT(cuid) AS total,
        COUNT(IF(Login21>0,1,NULL)) AS login_21,
        COUNT(IF(Login22>0,1,NULL)) AS login_22
    FROM
    (
    SELECT cuid,
    COUNT(IF(cur_date='2023-08-20',1,NULL)) AS Login20,
    COUNT(IF(cur_date='2023-08-21',1,NULL)) AS Login21,
    COUNT(IF(cur_date='2023-08-22',1,NULL)) AS Login22
    FROM lea WHERE cur_date IN('2023-08-20','2023-08-21','2023-08-22')
    GROUP BY cuid
    HAVING  Login20>0) AS temp

![5bd915d87a6c430c809ae7b7f689813e.png][]

后面几天的上线人数已经求出来了，但是存留率应该是剩下的人数处于刚开始的人数

SELECT  TYPE,
     CONCAT(ROUND(login_21/total * 100, 2), '%') AS '21日留存率',
     CONCAT(ROUND(login_22/total * 100, 2), '%') AS '22日留存率'
    FROM
    (SELECT '2023-08-20的留存情况' AS TYPE,
        COUNT(cuid) AS total,
        COUNT(IF(Login21>0,1,NULL)) AS login_21,
        COUNT(IF(Login22>0,1,NULL)) AS login_22
    FROM
    (
    SELECT cuid,
    COUNT(IF(cur_date='2023-08-20',1,NULL)) AS Login20,
    COUNT(IF(cur_date='2023-08-21',1,NULL)) AS Login21,
    COUNT(IF(cur_date='2023-08-22',1,NULL)) AS Login22
    FROM lea WHERE cur_date IN('2023-08-20','2023-08-21','2023-08-22')
    GROUP BY cuid
    HAVING  Login20>0) AS temp) AS arr

![73f1e92d31014ba8bdc444422825e397.png][]

最后这种类型题也就解决了，你也因此保证了工作！！！

上模板：

->where 日期 in (首日,1天后,7天后)
    ->group by 用户
    ->count(if(日期=首日,1,null)) as cnt
      count(if(日期=1天后,1,null)) as cnt2
      count(if(日期=7天后,1,null)) as cnt8
    ->having cnt->0
    ->count(user_id) as 首日总数
      count(if(cnt2>0,1,null)) as 次日留存率
      count(if(cnt8,1,null)) as 7日留存率
    ->次日留存率/首日总数 as 次日留存率
      7日留存率/首日总数 as 7日留存率

[2086d1c9641347b299699684a0c1d858.png]: https://img-blog.csdnimg.cn/2086d1c9641347b299699684a0c1d858.png
[78bd1dd973344fbdad5d8931a9d293b7.png]: https://img-blog.csdnimg.cn/78bd1dd973344fbdad5d8931a9d293b7.png
[99d97de6b93f4e88b977af50b6739ebd.png]: https://img-blog.csdnimg.cn/99d97de6b93f4e88b977af50b6739ebd.png
[5bd915d87a6c430c809ae7b7f689813e.png]: https://img-blog.csdnimg.cn/5bd915d87a6c430c809ae7b7f689813e.png
[73f1e92d31014ba8bdc444422825e397.png]: https://img-blog.csdnimg.cn/73f1e92d31014ba8bdc444422825e397.png