SQL之连续签到题目
------------恢复内容开始------------
建表:
drop table if exists t_user_attendence; CREATE TABLE `t_user_attendence` ( `id` INT(11) NOT NULL AUTO_INCREMENT, `fuser_id` varchar(11) default null , `fdate` VARCHAR(40) DEFAULT NULL, `department` VARCHAR(40) DEFAULT NULL, `fis_sign_in` varchar(40) default null, PRIMARY KEY (`id`) ) ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8; INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Peter','20191025','IT','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Peter','20191024','IT','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Peter','20191023','IT','0'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Peter','20191022','IT','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Peter','20191021','IT','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Peter','20191020','IT','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Peter','20191019','IT','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Peter','20191018','IT','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Peter','20191017','IT','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Peter','20191016','IT','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Peter','20191015','IT','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Peter','20191014','IT','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Peter','20191013','IT','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Peter','20191012','IT','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Tony','20191025','IT','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Tony','20191024','IT','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Tony','20191023','IT','0'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Tony','20191022','IT','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Tony','20191021','IT','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Tony','20191020','IT','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Tony','20191019','IT','0'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Tony','20191018','IT','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Tony','20191017','IT','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Tony','20191016','IT','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Tony','20191015','IT','0'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Tony','20191014','IT','0'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Tony','20191013','IT','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Tony','20191012','IT','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Tom','20191025','Sales','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Tom','20191024','Sales','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Tom','20191023','Sales','0'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Tom','20191022','Sales','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Tom','20191021','Sales','0'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Rose','20191025','Sales','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Rose','20191024','Sales','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Rose','20191023','Sales','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Rose','20191022','Sales','1'); INSERT INTO t_user_attendence(fuser_id,fdate,department,fis_sign_in) VALUES('Rose','20191021','Sales','1');
表格如下:
1.请计算20191001~20191031日,31天内,每个部门签到最多的10名用户
思路:
-
直接用where fis_sign_in = 1 筛选出每个部门每个用户签到的记录,然后用sum(1) over() 窗口函数求出每个部门的每个用户的签到记录有多少次,记为nums;
-
然后再根据部门和用户进行分组,根据nums降序排序,记为rk;
-
然后再where筛选出rk<=10的用户
代码如下:
#每个部门签到最多的10名用户 select * from (select department,fuser_id,nums,dense_rank() over(partition by department order by nums desc) as rk from (select distinct department,fuser_id,sum(1) over(partition by department,fuser_id) as nums from t_user_attendence where fis_sign_in = 1
and fdate between '2019-10-01' and '2019-10-31'
) as l) k where rk<=10;
结果如下:
2.请计算20191001~20191031日,31天内,连续签到超过10天的用户数
思路:
(1)对每个用户的登录天数进行排序,记为rk;
select fuser_id, fdate, rank() over(PARTITION by fuser_id order BY fdate) rk from t_user_attendence where fis_sign_in = 1
and fdate between '2019-10-01' and '2019-10-31'
(2)如果是连续登录的话,那么每天的登录日期减去rk天数一直会是同一天,如果不连续了就会减出不一样的天数,从而可以按减出的天数是否是同一天来分组,从而将连续的天数都放在一组
select fuser_id,fdate,date_sub(fdate, interval rk day ) as dd from( select fuser_id, fdate, rank() over(PARTITION by fuser_id order BY fdate) rk from t_user_attendence where fis_sign_in = 1 ) t
根据dd 把是否是连续登录的天数分在一组
(3)然后将分在一组的连续登录的天数里,求出最大的日期和最小的日期,相减就是连续登录的天数了
select DISTINCT fuser_id, min(fdate) over(PARTITION by date_sub(fdate, interval rk day), fuser_id) as startdate, max(fdate) over(PARTITION by date_sub(fdate, interval rk day), fuser_id) as enddate from ( select fuser_id, fdate, rank() over(PARTITION by fuser_id order BY fdate) rk from t_user_attendence where fis_sign_in = 1 ) t
select *,datediff(enddate,startdate) as n_day from( select DISTINCT fuser_id, min(fdate) over(PARTITION by date_sub(fdate, interval rk day), fuser_id) as startdate, max(fdate) over(PARTITION by date_sub(fdate, interval rk day), fuser_id) as enddate from ( select fuser_id, fdate, rank() over(PARTITION by fuser_id order BY fdate) rk from t_user_attendence where fis_sign_in = 1 ) t ) l
(4)然后求出每个用户连续登录天数超过10天的用户
select fuser_id from( select *,datediff(enddate,startdate) as n_day from( select DISTINCT fuser_id, min(fdate) over(PARTITION by date_sub(fdate, interval rk day), fuser_id) as startdate, max(fdate) over(PARTITION by date_sub(fdate, interval rk day), fuser_id) as enddate from ( select fuser_id, fdate, rank() over(PARTITION by fuser_id order BY fdate) rk from t_user_attendence where fis_sign_in = 1 ) t ) l ) s where n_day>=10;
3.计算历史以来每个用户连续签到的最大天数
思路:同理第2题求出每个用户连续登录的天数n_day之后,对每个用户的n_day求一个max(n_day),求出最大的天数
select fuser_id,max(n_day) from( select *,datediff(enddate,startdate) as n_day from( select DISTINCT fuser_id, min(fdate) over(PARTITION by date_sub(fdate, interval rk day), fuser_id) as startdate, max(fdate) over(PARTITION by date_sub(fdate, interval rk day), fuser_id) as enddate from ( select fuser_id, fdate, rank() over(PARTITION by fuser_id order BY fdate) rk from t_user_attendence where fis_sign_in = 1 ) t ) l ) s group by 1