计算新登陆用户次日的留存率、每个日期登陆的新用户数、 每个日期新用户的次日留存率
1. 请你写出一个sql语句查询新登录用户次日成功的留存率，即第1天登陆之后，第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入)2. 请你写出一个sql语句查询每个日期登录新用户个数，并且查询结果按照日期升序排序，3.请你写出一个sql语句查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序4. 计算用户连续登录天数5. 查询连续登陆5天的用户1. 请你写出一个sql语句查询新登录用户次日成功的留存率，即第1天登陆之后，第2天再次登陆的概率,保存小数点后面3位(3位之后的四舍五入)
select round(count(t2.user_id)*1.0/count(t1.user_id),3)from (select user_id,min(date) as date from login group by user_id) t1left join login t2on t1.user_id = t2.user_id and t2.date = date_add(t1.date,interval 1 day)
2. 请你写出一个sql语句查询每个日期登录新用户个数，并且查询结果按照日期升序排序，
-- 方法1select tmp.date,sum(case tmp.rk when 1 then 1 else 0 end) as newfrom (select user_id,date,rank() over(partition by user_id order by date) as rk from login) tmpgroup by tmp.date-- 方法2select tmp.date,count(distinct tmp.user_id) as newfrom (select user_id,min(date) date from login group by user_id) tmpgroup by tmp.dateunion select date,0 as new from loginwhere date not in (select min(date) from login group by user_id)order by date;
3.请你写出一个sql语句查询每个日期新用户的次日留存率，结果保留小数点后面3位数(3位之后的四舍五入)，并且查询结果按照日期升序排序
select t1.date,round(count(distinct t2.user_id)/count(distinct t1.user_id),3) pfrom (select user_id,min(date) date from login group by user_id) t1left join login t2 on t1.user_id = t2.user_id and t2.date=date_add(t1.date,interval 1 day)group by t1.dateunionselect date,0.000 p from loginwhere date not in (select min(date) from login group by user_id)order by date;
4. 计算用户连续登录天数
create table user_login(user_id varchar(100),login_time datetime);insert into user_login values(1,'-11-25 13:30:45'), (1,'-11-24 13:30:45'), (1,'-11-24 10:30:45'), (1,'-11-24 09:30:45'), (1,'-11-23 09:30:45'), (1,'-11-10 09:30:45'), (1,'-11-09 09:30:45'), (1,'-11-01 09:30:45'), (1,'-10-31 09:30:45'), (2,'-11-25 13:30:45'), (2,'-11-24 13:30:45'), (2,'-11-23 10:30:45'), (2,'-11-22 09:30:45'), (2,'-11-21 09:30:45'), (2,'-11-20 09:30:45'), (2,'-11-19 09:30:45'), (2,'-11-02 09:30:45'), (2,'-11-01 09:30:45'), (2,'-10-31 09:30:45'), (2,'-10-30 09:30:45'), (2,'-10-29 09:30:45'); 1. 一天中可能有多次打卡记录create table user_login_date(select distinct user_id, date(login_time) login_date from user_login);2. 按照用户分区 login_date 从小到大 排序select user_id,login_date,row_number() over(partition by user_id order by login_date) rkfrom user_login_date;3. 计算辅助列 login_date-rkselect user_id,login_date,rk,date_sub(login_date,interval rk day) t_datefrom(select user_id,login_date,row_number() over(partition by user_id order by login_date) rkfrom user_login_date)a4. 按照用户和辅助列分组 计算个数select user_id,min(login_date) start_date,max(login_date) end_date,count(t_date) cfrom(select user_id,login_date,rk,date_sub(login_date,interval rk day) t_datefrom(select user_id,login_date,row_number() over(partition by user_id order by login_date) rkfrom user_login_date)a)bgroup by user_id,t_date;+---------+------------+----+------------+| user_id | login_date | rk | t_date|+---------+------------+----+------------+| 1 | -10-31 | 1 | -10-30 || 1 | -11-01 | 2 | -10-30 || 1 | -11-09 | 3 | -11-06 || 1 | -11-10 | 4 | -11-06 || 1 | -11-23 | 5 | -11-18 || 1 | -11-24 | 6 | -11-18 || 1 | -11-25 | 7 | -11-18 || 2 | -10-29 | 1 | -10-28 || 2 | -10-30 | 2 | -10-28 || 2 | -10-31 | 3 | -10-28 || 2 | -11-01 | 4 | -10-28 || 2 | -11-02 | 5 | -10-28 || 2 | -11-19 | 6 | -11-13 || 2 | -11-20 | 7 | -11-13 || 2 | -11-21 | 8 | -11-13 || 2 | -11-22 | 9 | -11-13 || 2 | -11-23 | 10 | -11-13 || 2 | -11-24 | 11 | -11-13 || 2 | -11-25 | 12 | -11-13 |+---------+------------+----+------------+结果+---------+------------+------------+---+| user_id | start_date | end_date | c |+---------+------------+------------+---+| 1 | -10-31 | -11-01 | 2 || 1 | -11-09 | -11-10 | 2 || 1 | -11-23 | -11-25 | 3 || 2 | -10-29 | -11-02 | 5 || 2 | -11-19 | -11-25 | 7 |+---------+------------+------------+---+
5. 查询连续登陆5天的用户
1.采用lead函数 找到用户第五次登陆的日期select *,lead(login_date,5) over(partition by user_id order by login_date) 5th_dayfrom user_login_date;+---------+------------+------------+| user_id | login_date | 5th_day |+---------+------------+------------+| 1 | -10-31 | -11-24 || 1 | -11-01 | -11-25 || 1 | -11-09 | NULL || 1 | -11-10 | NULL || 1 | -11-23 | NULL || 1 | -11-24 | NULL || 1 | -11-25 | NULL || 2 | -10-29 | -11-19 || 2 | -10-30 | -11-20 || 2 | -10-31 | -11-21 || 2 | -11-01 | -11-22 || 2 | -11-02 | -11-23 || 2 | -11-19 | -11-24 || 2 | -11-20 | -11-25 || 2 | -11-21 | NULL || 2 | -11-22 | NULL || 2 | -11-23 | NULL || 2 | -11-24 | NULL || 2 | -11-25 | NULL |+---------+------------+------------+2. 计算第五次登陆时间和本次登陆时间相差5天的用户select distinct user_id from(select *,timestampdiff(day,login_date,5th_date) diff_datefrom (select *,lead(login_date,5) over(partition by user_id order by login_date) 5th_datefrom user_login_date)a)b where diff_date=5;结果+---------+| user_id |+---------+| 2 |+---------+
Mysql 计算新登陆用户次日的留存率 每个日期登陆的新用户数 每个日期新用户的次日留存率 用户连续登陆天数 连续登录5天的用户
如果觉得《Mysql 计算新登陆用户次日的留存率 每个日期登陆的新用户数 每个日期新用户的次》对你有帮助，请点赞、收藏，并留下你的观点哦！
