mysql 查询用户_mysql查询用户留存语法（用户留存和用户留存率问题）大厂面试必问...

用户留存和用户留存率问题,小编在面对几次大厂面试的时候都问了这个题,同时在实际生产环境中也常用到这个语法.

1、创建表

CREATE TABLE `user_log`  (  `device_id` varchar(11) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,  `app_id` varchar(20) CHARACTER SET latin1 COLLATE latin1_swedish_ci DEFAULT NULL,  `times` int(100) DEFAULT NULL,  `duration` int(100) DEFAULT NULL,  `log_day` date DEFAULT NULL) ENGINE = InnoDB CHARACTER SET = latin1 COLLATE = latin1_swedish_ci ROW_FORMAT = Dynamic; '''字段及表说明：表名：user_log字段名：log_day:登录日期device_id:用户设备idtimes:日登陆次数duration：平均每次登陆时长app_id:用户app的id，其中device_id和app_id确定唯一的用户'''

2、插入数据

INSERT into user_log VALUES('a001','R11',5,10,'2020/3/17'),......('a001','R11',5,13,'2020/3/1'),('a005','R11',6,14,'2020/3/1');

3、查询语句:用户留存/留存率(次日、3日、7日、30日,...)

3.1计算某日用户留存率(次日、3日、7日、30日,...)

-- 3.1计算某日用户留存率(次日、3日、7日、30日,...)select *,  concat(round(100*次日留存用户/日活跃用户数,2),'%')  次日留存率,  concat(round(100*三日留存用户/日活跃用户数,2),'%')  三日留存率,  concat(round(100*七日留存用户/日活跃用户数,2),'%')  七日留存率from (    select         a.log_day 日期,      count(distinct concat(a.device_id,a.app_id))  日活跃用户数,      count(distinct concat(b.device_id,b.app_id))  次日留存用户,      count(distinct concat(c.device_id,c.app_id))  三日留存用户,      count(distinct concat(d.device_id,d.app_id))  七日留存用户    from user_log a       left join user_log b on concat(a.device_id,a.app_id) = concat(b.device_id,b.app_id) and b.log_day = a.log_day + 1      left join user_log c on concat(a.device_id,a.app_id) = concat(c.device_id,c.app_id) and c.log_day = a.log_day + 3      left join user_log d on concat(a.device_id,a.app_id) = concat(d.device_id,d.app_id) and d.log_day = a.log_day + 7    group by a.log_day) p;

1.2计算某日新增用户留存率(次日、3日、7日、30日、...)

-- 1.2计算某日新增用户留存率(次日、3日、7日、30日、...)select *,concat(round(100*次日留存用户/日新增用户数,2),'%')  次日留存率,concat(round(100*三日留存用户/日新增用户数,2),'%')  三日留存率,concat(round(100*七日留存用户/日新增用户数,2),'%')  七日留存率 from (    select   c.log_day 日期,  count(distinct concat(c.device_id,c.app_id))  日新增用户数,  count(distinct concat(d.device_id,d.app_id))  次日留存用户,  count(distinct concat(e.device_id,e.app_id))  三日留存用户,  count(distinct concat(f.device_id,f.app_id))  七日留存用户  from   (    select a.*    from user_log a     left join user_log b on concat(a.device_id,a.app_id) = concat(b.device_id,b.app_id) and b.log_day < a.log_day    where b.log_day is null  ) c  left join user_log d on concat(c.device_id,c.app_id) = concat(d.device_id,d.app_id) and  d.log_day = c.log_day + 1   left join user_log e on concat(c.device_id,c.app_id) = concat(e.device_id,e.app_id) and  e.log_day = c.log_day + 3  left join user_log f on concat(c.device_id,c.app_id) = concat(f.device_id,f.app_id) and  f.log_day = c.log_day + 7   group by c.log_day) p;