mysql游戏留存率计算

前言

游戏，一般有2张表，用户表(g_user)，操作日志表(g_login_record)，留存率就是根据这个计算。

通常，我们会直接算出来，用个视图表来显示，我们不会去弄个表维护，因为更新量太大了，没有意义。直接sql就能算出来，省时省力。

概念说明

游戏留存率：指的是，以注册时间为基准，昨天5.16号有2个人注册了，那么就算这2个注册的人，17号有一个人登录了，那么留存率就是百分之50。简单说，用户注册后，第几天又登录了，代表这个游戏吸不吸引人。

表结构

g_user表

我们只用了2个字段,user_id和create_time

g_login_record表

我们只用了2个字段,user_id和login_time

sql

逻辑

SELECT

-- 注册时间
a.create_time,

-- 总数
count( DISTINCT a.user_id ) AS user_count,
count( DISTINCT ( IF ( DATEDIFF( b.login_time, a.create_time ) = 1, a.user_id, NULL ) ) ) AS remain1,
-- 1日留存数
count( DISTINCT ( IF ( DATEDIFF( b.login_time, a.create_time ) = 2, a.user_id, NULL ) ) ) AS remain2,
-- 2日留存数
count( DISTINCT ( IF ( DATEDIFF( b.login_time, a.create_time ) = 3, a.user_id, NULL ) ) ) AS remain3,

FROM
    ( 注册表
    LEFT JOIN ( 日志表) b ON a.user_id = b.user_id 
WHERE
    b.login_time >= a.create_time
GROUP BY
    a.create_time;

完整代码

SELECT
a.create_time,
count( DISTINCT a.user_id ) AS user_count,
count( DISTINCT ( IF ( DATEDIFF( b.login_time, a.create_time ) = 1, a.user_id, NULL ) ) ) AS remain1,
-- 1日留存数
count( DISTINCT ( IF ( DATEDIFF( b.login_time, a.create_time ) = 2, a.user_id, NULL ) ) ) AS remain2,
-- 2日留存数
count( DISTINCT ( IF ( DATEDIFF( b.login_time, a.create_time ) = 3, a.user_id, NULL ) ) ) AS remain3,
-- 3日留存数
count( DISTINCT ( IF ( DATEDIFF( b.login_time, a.create_time ) = 4, a.user_id, NULL ) ) ) AS remain4,
-- 4日留存数
count( DISTINCT ( IF ( DATEDIFF( b.login_time, a.create_time ) = 5, a.user_id, NULL ) ) ) AS remain5,
-- 5日留存数
count( DISTINCT ( IF ( DATEDIFF( b.login_time, a.create_time ) = 6, a.user_id, NULL ) ) ) AS remain6,
-- 6日留存数
count( DISTINCT ( IF ( DATEDIFF( b.login_time, a.create_time ) = 7, a.user_id, NULL ) ) ) AS remain7,
-- 7日留存数
count( DISTINCT ( IF ( DATEDIFF( b.login_time, a.create_time ) = 15, a.user_id, NULL ) ) ) AS remain15,
-- 15日留存数
count( DISTINCT ( IF ( DATEDIFF( b.login_time, a.create_time ) = 30, a.user_id, NULL ) ) ) AS remain30 
-- 30日留存数

FROM
    ( SELECT DATE(create_time) AS create_time,user_id
FROM g_user
WHERE create_time BETWEEN '2024-04-17 06:43:48' AND '2024-05-18 06:43:48' ) a
    LEFT JOIN ( SELECT user_id, login_time FROM g_login_record GROUP BY user_id, login_time  ) b ON a.user_id = b.user_id 
WHERE
    b.login_time >= a.create_time
GROUP BY
    a.create_time;

以这个代码创建视图

DROP VIEW IF EXISTS user_remain_view;
CREATE VIEW user_remain_view AS 上面的sql代码

查询视图

-- 3.计算留存

SELECT
    create_time,
    user_count,
    remain1 as 第一天人数,
    concat( round( remain1 / user_count * 100, 2 ), '%' ) AS 第一天比例,
    remain2 as 第二天人数,
    concat( round( remain2 / user_count * 100, 2 ), '%' ) AS 第二天比例,
    remain3 as 第三天人数,
    concat( round( remain3 / user_count * 100, 2 ), '%' ) AS 第三天比例,
    remain4 as 第四天人数,
    concat( round( remain4 / user_count * 100, 2 ), '%' ) AS 第四天比例,
    remain5 as 第五天人数,
    concat( round( remain5 / user_count * 100, 2 ), '%' ) AS 第五天比例,
    remain6 as 第六天人数,
    concat( round( remain6 / user_count * 100, 2 ), '%' ) AS 第六天比例,
    remain7 as 第七天人数,
    concat( round( remain7 / user_count * 100, 2 ), '%' ) AS 第七天比例,
    remain15 as 第十五天人数,
    concat( round( remain15 / user_count * 100, 2 ), '%' ) AS 第十五天比例,
    remain30 as 第三十天人数,
    concat( round( remain30 / user_count * 100, 2 ), '%' ) AS 第三十天比例 

FROM
    user_remain_view;

结果:

参考：

面试系列-SQL如何计算用户留存率？ - 知乎