数据库中有个表的表名叫userlogin_info,有三个字段,id(索引)、userid(用户id)、login_time(登录时间),用户每登录一次APP会往这个表写入一条记录。
问题1:2022年1月中连续7天登录的用户数有哪些?(1至7号连续、或者2至8号连续等)
with t_login as ( select userid,date_sub(login_time,row_cnt) as flag_time from ( select userid,login_time,row_number over(partition by userid order by login_time) as row_cnt from ( select userid,login_time from ( select id,userid,from_unixtime(unix_timestamp(login_time,'yyyy-MM-dd HH:mm:ss'),'yyyyMMdd') as login_time from userlogin_info ) where login_time between 20220101 and 20220131 group by userid,login_time ) ) ) select userid from ( select userid,flag_time,count(1) as login_cnt from t_login group by userid,flag_time having login_cnt>=7 ) group by userid
问题2:2022年1月中连续7天登录的用户数有多少?(1至7号连续、或者2至8号连续等)
with t_login as ( select userid,date_sub(login_time,row_cnt) as flag_time from ( select userid,login_time,row_number over(partition by userid order by login_time) as row_cnt from ( select userid,login_time from ( select id,userid,from_unixtime(unix_timestamp(login_time,'yyyy-MM-dd HH:mm:ss'),'yyyyMMdd') as login_time from userlogin_info ) where login_time between 20220101 and 20220131 group by userid,login_time ) ) ) select count(distinct userid) as login_uv from ( select userid,flag_time,count(1) as login_cnt from t_login group by userid,flag_time having login_cnt>=7 )
解析:
1、对用户(userid)以及登录日期(login_time/天)进行去重,明确用户改天是否有登录
2、用排序函数对用户(userid) 按登录日期(login_time)进行排序得到(row_cnt)
3、用日期函数登录日期(login_time)减去排序的序号(row_cnt),得到flag_time,如果用户连续登录,则flag_time的计算结果是相同的,反则没有连续登录
4、对用户(userid)以及flag_time进行去重,进行count()聚合计算,筛选出登录次数>=7的用户
标签:cnt,登录,userid,问题,连续,time,login,select From: https://www.cnblogs.com/ZeroITStudy/p/17900846.html