求连续操作(登录)数量(次数)最大的记录(

页面导航:首页 > 数据库 > MsSql > 求连续操作(登录)数量(次数)最大的记录(

求连续操作(登录)数量(次数)最大的记录(

来源: 作者: 时间:2016-01-20 09:48 【

登录时间里面有详细的时分秒数据,而我们的题目只要求连续的天数,所以使用DATEDIFF函数可以解决,DATEDIFF(d,LoginTime,getdate()) as diffDate ,有多个用户都在登录,因此应该以用户名为分区,
登录时间里面有详细的时分秒数据,而我们的题目只要求连续的天数,所以使用DATEDIFF函数可以解决,
 
DATEDIFF(d,LoginTime,getdate()) as diffDate ,
有多个用户都在登录,因此应该以用户名为分区,登录时间为顺序来计算rownumber,因此,就是下面的表达式:
 
ROW_NUMBER() over(partition by Name order by LoginTime desc) as rn
关键问题来了,如何求得连续的登录情况?
 
如果是连续的记录,那么 diffDate- rn 肯定是相同的!
 
OK,果然这种方式很巧妙,那么我们最终的SQL写出来也不难了。
 
开始动手,先构造一个表,插入初始数据:
 
/*
  求连续登录次数最多的用户
*/
create table UserLoginInfo(
  ID int IDENTITY primary key,
  Name varchar(50) not null,
  LoginTime datetime not null
  )
  go
  
  insert UserLoginInfo (Name,LoginTime)
  values('zhang','2015-11-10 12:01:50')
  ,('li','2015-11-11 11:01:50')
  ,('wang','2015-11-9 11:01:50');
  go
  
  insert UserLoginInfo (Name,LoginTime) values 
  ('zhang','2015-11-11 12:01:50'),
  ('li','2015-11-11 12:01:50'),
  ('wang','2015-11-11 11:01:50'),
  
  ('zhang','2015-11-12 12:01:50'),
  ('li','2015-11-13 13:01:50'),
  ('wang','2015-11-12 11:01:50'),
  
  ('zhang','2015-11-13 12:01:50'),
  ('li','2015-11-14 11:01:50'),
  ('wang','2015-11-14 11:01:50');
  go
然后用下面的SQL得到结果:
select top 1 
       Name,diffRn,COUNT(diffRn)as LoginCount 
from(
    select Name,diffDate,rn, (diffDate-rn) as diffRn 
    from(      
          select  ID,Name, 
                  DATEDIFF(d,LoginTime,getdate()) as diffDate , 
                  ROW_NUMBER() over(partition by Name order by LoginTime desc) as rn
          from UserLoginInfo
    ) t1
) t2
group by diffRn,Name 
order by LoginCount desc
答案是:
 
Name    diffRn    LoginCount
zhang    14    4
 
如果注释掉 top 1,我们就知道这个结果的由来了:
Name    diffRn    LoginCount
zhang    14    4
li    13    3
wang    14    2
wang    15    1
li    14    1
wang    13    1
这个问题也可以衍生出 求连续登录的用户,或者求连续登录15天的用户(比如QQ的签到功能),是不是很熟悉呢?
 
实际上,上面这个查询,遇到一天登录多次的情况下,统计是不准确的,例如,构造下面的测试数据:
 
insert UserLoginInfo (Name,LoginTime) values 
     ('zhang'    ,'2015-11-10 12:01:50')
    ,('li'            ,'2013-10-05 11:01:50')
    ,('li'            ,'2013-10-06 11:01:50')
    ,('li'            ,'2014-10-05 11:01:50')
    ,('li'            ,'2014-10-06 11:01:50')
    ,('li'            ,'2015-10-05 11:01:50')
    ,('li'            ,'2015-10-06 11:01:50')
    ,('li'            ,'2015-11-10 11:01:50')
    ,('li'            ,'2015-11-11 11:01:50')
    ,('wang'        ,'2015-11-09 11:01:50')
    ,('zhang'        ,'2015-11-11 12:01:50')
    ,('li'            ,'2015-11-11 12:01:50')
    ,('wang'        ,'2015-11-11 11:01:50')
    ,('zhang'        ,'2015-11-12 12:01:50')
    ,('li'            ,'2015-11-13 13:01:50')
    ,('wang'        ,'2015-11-12 11:01:50')
    ,('zhang'        ,'2015-11-13 12:01:50')
    ,('li'            ,'2015-11-14 11:01:50')
    ,('wang'        ,'2015-11-14 11:01:50')
    ;

 

这时应该先去除某天的重复数据,才是正确的,所以查询应该做如下改进:
select --top 1 
       Name,diffRn,COUNT(diffRn)as LoginCount 
from(
    select Name,diffDate,rn, (diffDate-rn) as diffRn 
    from(      
          select  Name, 
                  diffDate,
                  ROW_NUMBER() over(partition by Name order by diffDate asc) rn
          from ( 
                select distinct Name,DATEDIFF(d,LoginTime,getdate()) as diffDate 
                from  UserLoginInfo 
          ) t0
    ) t1
) t2
group by diffRn,Name 
order by LoginCount desc;
结果是:
Name    diffRn    LoginCount
zhang    14    4
wang    14    2
li    13    2
li    14    2
li    48    2
li    411    2
li    774    2
wang    13    1
wang    15    1
结果符合我们的预期,算是完整的答案了。
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<