sql计算留存率

最新推荐文章于 2023-11-12 23:07:24 发布

南风知我意丿

最新推荐文章于 2023-11-12 23:07:24 发布

阅读量1.5k

点赞数 1

分类专栏： SQL 文章标签： sql 数据库

本文链接：https://blog.csdn.net/Lzx116/article/details/129408232

版权

SQL 专栏收录该内容

14 篇文章 0 订阅

订阅专栏

文章目录

什么是留存率

留存用户是指用户在APP产生行为后，在固定的第N日继续访问或使用APP的用户。留存率是指用户在一段时间后或固定的间隔后产生留存用户的比例。
用户留存率有很多种：新客留存率、老客留存率、活跃用户留存率、购买留存率、或者某个功能使用用户的留存率等。
通常计算的时间间隔为次日、3日、7日、30日、60日，根据业务不同需求可以选择不同的计算方式以及时间间隔，重点是要和产品负责人以及运营人员对好指标口径。

案例1

公式

N日新客留存率=某日的新注册用户在第N日访问过APP的留存用户数 / 某日的新注册用户总数。

数据

DROP TABLE user_info;
create table user_info(
    user_id varchar(10),
    login_date datetime
);
insert into user_info values
('u_01','2020-01-01 09:15:00'),
('u_02','2020-01-01 00:04:00'),
('u_03','2020-01-01 22:16:00'),
('u_04','2020-01-01 20:32:00'),
('u_05','2020-01-01 13:59:00'),
('u_06','2020-01-01 21:28:00'),
('u_07','2020-01-01 14:03:00'),
('u_08','2020-01-01 11:00:00'),
('u_09','2020-01-01 23:57:00'),
('u_10','2020-01-01 04:46:00'),
('u_11','2020-01-02 14:21:00'),
('u_12','2020-01-02 11:15:00'),
('u_13','2020-01-02 07:26:00'),
('u_14','2020-01-02 10:34:00'),
('u_15','2020-01-02 08:22:00'),
('u_16','2020-01-02 14:23:00'),
('u_17','2020-01-03 09:20:00'),
('u_18','2020-01-03 11:21:00'),
('u_19','2020-01-03 12:17:00'),
('u_20','2020-01-03 15:26:00'),
('u_02','2020-01-02 00:14:00'),
('u_10','2020-01-02 08:32:00'),
('u_03','2020-01-02 09:20:00'),
('u_08','2020-01-02 10:07:00'),
('u_04','2020-01-02 10:29:00'),
('u_09','2020-01-02 11:45:00'),
('u_05','2020-01-02 12:19:00'),
('u_01','2020-01-02 14:29:00'),
('u_15','2020-01-03 00:26:00'),
('u_14','2020-01-03 11:18:00'),
('u_11','2020-01-03 13:18:00'),
('u_16','2020-01-03 14:33:00'),
('u_06','2020-01-04 07:51:00'),
('u_18','2020-01-04 08:11:00'),
('u_07','2020-01-04 09:27:00'),
('u_10','2020-01-04 10:59:00'),
('u_20','2020-01-04 11:51:00'),
('u_03','2020-01-04 12:37:00'),
('u_17','2020-01-04 15:07:00'),
('u_08','2020-01-04 16:35:00'),
('u_01','2020-01-04 19:29:00'),
('u_14','2020-01-05 08:03:00'),
('u_12','2020-01-05 10:27:00'),
('u_15','2020-01-05 16:33:00'),
('u_19','2020-01-06 09:03:00'),
('u_20','2020-01-06 15:26:00'),
('u_04','2020-01-08 11:03:00'),
('u_05','2020-01-08 12:54:00'),
('u_06','2020-01-08 19:22:00'),
('u_13','2020-01-09 10:20:00'),
('u_15','2020-01-09 16:40:00'),
('u_18','2020-01-10 21:34:00');

代码

WITH a AS (
    SELECT DISTINCT user_id, -- 防止一个用户一天内多次登录
                    login_date,
                    register_date,
                    DATEDIFF(login_date, register_date) date_diff -- 登陆日期和注册日期的差
    FROM (SELECT user_id,
                 DATE(login_date) login_date,
                 MIN(DATE(login_date)) OVER (PARTITION BY user_id) AS register_date -- 最小登陆日期即为注册日期
          FROM user_info) t1
)
SELECT register_date,
       COUNT(DISTINCT user_id)                                     AS                                  DAU,
       COUNT(CASE WHEN date_diff = 1 THEN a.user_id ELSE NULL END) AS                                  "次日留存数",
       COUNT(CASE WHEN date_diff = 3 THEN a.user_id ELSE NULL END) AS                                  "3日留存数",
       COUNT(CASE WHEN date_diff = 7 THEN a.user_id ELSE NULL END) AS                                  "7日留存数",
       ROUND(COUNT(CASE WHEN date_diff = 1 THEN a.user_id ELSE NULL END) / COUNT(DISTINCT user_id), 2) "次日留存率",
       ROUND(COUNT(CASE WHEN date_diff = 3 THEN a.user_id ELSE NULL END) / COUNT(DISTINCT user_id), 2) "3日留存率",
       ROUND(COUNT(CASE WHEN date_diff = 7 THEN a.user_id ELSE NULL END) / COUNT(DISTINCT user_id), 2) "7日留存率"
FROM a
GROUP BY register_date
;

效果

案例2

公式

N日新客留存率=某日的新注册用户在第N日内访问过APP的留存用户数 / 某日的新注册用户总数。

数据
同案例一
代码

WITH a AS (
    SELECT DISTINCT user_id, -- 防止一个用户一天内多次登录
                    login_date,
                    register_date,
                    DATEDIFF(login_date, register_date) date_diff -- 登陆日期和注册日期的差
    FROM (SELECT user_id,
                 DATE(login_date) login_date,
                 MIN(DATE(login_date)) OVER (PARTITION BY user_id) AS register_date -- 最小登陆日期即为注册日期
          FROM user_info) t1
)
SELECT register_date,
       COUNT(DISTINCT user_id) AS DAU,
       COUNT(CASE WHEN date_diff = 1 THEN a.user_id ELSE NULL END) AS "次日留存数",
       COUNT(CASE WHEN 1 < date_diff and date_diff <= 3 THEN a.user_id ELSE NULL END) AS "第3日留存数",
       COUNT(CASE WHEN 3 < date_diff and date_diff <= 7 THEN a.user_id ELSE NULL END) AS "第7日留存数",
       ROUND(COUNT(CASE WHEN date_diff = 1 THEN a.user_id ELSE NULL END) / COUNT(DISTINCT user_id), 2) "次日留存率",
       ROUND(COUNT(CASE WHEN 1 < date_diff and date_diff <= 3 THEN a.user_id ELSE NULL END) / COUNT(DISTINCT user_id), 2) "第3日留存率",
       ROUND(COUNT(CASE WHEN 3 < date_diff and date_diff <= 7 THEN a.user_id ELSE NULL END) / COUNT(DISTINCT user_id), 2) "第7日留存率"
FROM a
GROUP BY register_date

效果

案例3

参考牛客sql164

公式

次日留存率=某日的新注册用户在第2日访问过APP的留存用户数 / 某日注册用户总数
和案例一一样

代码
https://blog.csdn.net/Lzx116/article/details/128927778

案例4

参考牛客sql262

公式

次日留存率=某日的新注册用户在第2日访问过APP的留存用户数 / 总新注册用户总数

数据

drop table if exists login;
CREATE TABLE `login` (
`id` int(4) NOT NULL,
`user_id` int(4) NOT NULL,
`client_id` int(4) NOT NULL,
`date` date NOT NULL,
PRIMARY KEY (`id`));

INSERT INTO login VALUES
(1,2,1,'2020-10-12'),
(2,3,2,'2020-10-12'),
(3,1,2,'2020-10-12'),
(4,2,2,'2020-10-13'),
(5,4,1,'2020-10-13'),
(6,1,2,'2020-10-13'),
(7,1,2,'2020-10-14');

代码

WITH t1 as
(
     SELECT user_id,   -- 防止用户一天内多次登陆的情况
            date,first_date,datediff(date,first_date) as date_diff
     from(
         SELECT user_id,date,min(date) over(PARTITION BY user_id) as first_date
         FROM login)t1
)SELECT
        COUNT(DISTINCT user_id) as uv,
        round(COUNT(CASE WHEN date_diff = 1 THEN t1.user_id ELSE NULL END) / COUNT(DISTINCT user_id),3) as "次日留存率"
FROM t1;