创建测试数据库，包含用户ID和登录日期

  1. CREATE TABLE `logs` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `user_id` int(11) DEFAULT '0' COMMENT '用户id',
  4.   `date` date DEFAULT NULL COMMENT '日期',
  5.   PRIMARY KEY (`id`)
  6. ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

插入几条测试数据

  1. INSERT INTO `logs` (`id`, `user_id`, `date`) VALUES (1, 1, '2021-01-01');
  2. INSERT INTO `logs` (`id`, `user_id`, `date`) VALUES (2, 2, '2021-01-01');
  3. INSERT INTO `logs` (`id`, `user_id`, `date`) VALUES (3, 3, '2021-01-01');
  4. INSERT INTO `logs` (`id`, `user_id`, `date`) VALUES (4, 4, '2021-01-01');
  5. INSERT INTO `logs` (`id`, `user_id`, `date`) VALUES (5, 1, '2021-01-02');
  6. INSERT INTO `logs` (`id`, `user_id`, `date`) VALUES (6, 2, '2021-01-02');
  7. INSERT INTO `logs` (`id`, `user_id`, `date`) VALUES (7, 3, '2021-01-02');
  8. INSERT INTO `logs` (`id`, `user_id`, `date`) VALUES (8, 1, '2021-01-03');
  9. INSERT INTO `logs` (`id`, `user_id`, `date`) VALUES (9, 2, '2021-01-03');
  10. INSERT INTO `logs` (`id`, `user_id`, `date`) VALUES (10, 1, '2021-01-04');
  11. INSERT INTO `logs` (`id`, `user_id`, `date`) VALUES (11, 2, '2021-01-04');
  12. INSERT INTO `logs` (`id`, `user_id`, `date`) VALUES (12, 1, '2021-01-05');
  13. INSERT INTO `logs` (`id`, `user_id`, `date`) VALUES (13, 1, '2021-01-06');
  14. INSERT INTO `logs` (`id`, `user_id`, `date`) VALUES (14, 1, '2021-01-07');
  15. INSERT INTO `logs` (`id`, `user_id`, `date`) VALUES (15, 1, '2021-01-08');

查询一个日期范围内的用户留存率

  1. SELECT
  2.     date1,
  3.     COUNT(DISTINCT user_id) 当日用户数,
  4.     CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(date2, date1)= 1 THEN user_id ELSE NULL END)/ COUNT( DISTINCT user_id)* 100,2),"%") 次日留存率,
  5.     CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(date2, date1)= 2 THEN user_id ELSE NULL END)/ COUNT( DISTINCT user_id )* 100, 2),"%") 第三日留存率,
  6.     CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(date2, date1)= 6 THEN user_id ELSE NULL END)/ COUNT( DISTINCT user_id )* 100, 2),"%") 第七日留存率,
  7.     CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(date2, date1)= 9 THEN user_id ELSE NULL END)/ COUNT( DISTINCT user_id )* 100, 2),"%") 第十日留存率,
  8.     CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(date2, date1)= 29 THEN user_id ELSE NULL END)/ COUNT( DISTINCT user_id )* 100, 2),"%") 第三十日留存率,
  9.     CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(date2, date1)>= 1 AND DATEDIFF( date2, date1 )<= 2 THEN user_id ELSE NULL END )/ COUNT( DISTINCT user_id)*100,2),"%") 三日内留存率,
  10.     CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(date2, date1)>= 1 AND DATEDIFF( date2, date1 )<= 6 THEN user_id ELSE NULL END )/ COUNT( DISTINCT user_id )* 100,2 ),"%") 七日内留存率,
  11.     CONCAT(ROUND(COUNT(DISTINCT CASE WHEN DATEDIFF(date2, date1)>= 1 AND DATEDIFF( date2, date1 )<= 9 THEN user_id ELSE NULL END )/ COUNT( DISTINCT user_id )* 100,2 ),"%") 十日内留存率 
  12. FROM
  13.     (
  14.     SELECT lc1.user_id,lc1.date date1,lc2.date date2,DATEDIFF(lc1.date,lc2.date) diff FROM logs AS lc1
  15.     LEFT JOIN logs AS lc2 ON lc1.user_id = lc2.user_id where lc1.date <= '2021-01-31' and lc1.date >= '2021-01-01'
  16.     ) temp 
  17. GROUP BY date1

以下就是用户的留存率了