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

posted @ 2021-09-15 17:24 阿井井w 阅读(317) 评论(0) 编辑收藏举报

刷新页面返回顶部

阿井井w

SQL之连续签到题目

1.请计算20191001~20191031日，31天内，每个部门签到最多的10名用户

思路：

直接用where fis_sign_in = 1 筛选出每个部门每个用户签到的记录，然后用sum（1） over() 窗口函数求出每个部门的每个用户的签到记录有多少次，记为nums；

然后再根据部门和用户进行分组，根据nums降序排序，记为rk；

然后再where筛选出rk<=10的用户

2.请计算20191001~20191031日，31天内，连续签到超过10天的用户数

3.计算历史以来每个用户连续签到的最大天数

公告