如何使用可扩展的SQL查找连续登录n天的用户

Question

如果我有一个表(甲骨文或MySQL)，它存储日期用户登录。

那么，我如何编写一个SQL(或其他东西)来查找连续登录了n天的用户。

例如：

userID  |  logindate
1000       2014-01-10
1000       2014-01-11
1000       2014-02-01
1000       2014-02-02
1001       2014-02-01
1001       2014-02-02
1001       2014-02-03
1001       2014-02-04
1001       2014-02-05
1002       2014-02-01
1002       2014-02-03
1002       2014-02-05
.....

我们可以看到，用户1000在2014年连续丢失了两天，而用户1001则连续丢失了5天。用户1002永远不会连续登录。

SQL应该是可扩展的，这意味着我可以选择所有的n，修改一些或传递一个新的参数，结果与预期的一样。

谢谢!

Answer 1

由于我们不知道您使用的是什么数据库管理系统(您同时命名了MySQL和Oracle)，这里有两种解决方案，它们都在执行相同的操作:从登录日期开始对行进行排序并减去行号天数(因此，如果第6条记录是2014-02-12，第7条记录是2014-02-13，它们都会在2014-02-06年间产生)。因此，我们按用户和那个群组进行分组，并计算天数。然后根据用户进行分组，找出最长的序列。

下面是一个具有解析窗口函数(例如Oracle)的dbms的解决方案：

select userid, max(days)
from
(
  select userid, groupday, count(*) as days
  from
  (
    select
      userid, logindate - row_number() over (partition by userid order by logindate) as groupday
    from mytable
  )
  group by userid, groupday
)
group by userid
--having max(days) >= 3

下面是一个MySQL查询(未经测试，因为我没有可用的MySQL )：

select
  userid, max(days)
from
(
  select
    userid, date_add(logindate, interval -row_number day) as groupday, count(*) as days
  from
  (
    select 
      userid, logindate,
      @row_num := @row_num + 1 as row_number
    from mytable
    cross join (select @row_num := 0) r
    order by userid, logindate
  )
  group by userid, groupday
)
group by userid
-- having max(days) >= 3

Answer 2

我认为以下查询将为您提供一个非常可扩展的参数化：

select z.userid, count(*) continuous_login_days 
from
( 
  with max_dates as
  ( -- Get max date for every user ID
    select t.userid, max(t.logindate) max_date
    from test t
    group by t.userid
  ),
  ranks as
  ( -- Get ranks for login dates per user
    select t.*,
           row_number() over
               (partition by t.userid order by t.logindate desc) rnk
    from test t
  )
  -- So here, we select continuous days by checking if rank inside group
  -- (per user ID) matches login date compared to max date
  select r.userid, r.logindate, r.rnk, m.max_date
  from ranks r, max_dates m
  where m.userid = r.userid
    and r.logindate + r.rnk - 1 = m.max_date -- here is the key
) z
-- Then we only group by user ID to get the number of continuous days
group by z.userid
;

结果如下：

    USERID  CONTINUOUS_LOGIN_DAYS
1   1000    2
2   1001    5
3   1002    1

因此，您可以通过查询字段CONTINUOUS_LOGIN_DAYS进行选择。

编辑：如果您想从所有范围(不仅仅是最后一个)中进行选择，我的查询结构将不再工作，因为它依赖于最后一个范围。但这里有一个解决办法：

with w as
( -- Parameter
  select 2 nb_cont_days from dual
)
select *
from
(
  select t.*,
         -- Get number of days around
         (select count(*) from test t2
          where t2.userid = t.userid
            and t2.logindate between t.logindate - nb_cont_days + 1
                                 and t.logindate) m1,
         -- Get also number of days more in the past, and in the future
         (select count(*) from test t2
          where t2.userid = t.userid
            and t2.logindate between t.logindate - nb_cont_days
                                 and t.logindate + 1) m2,
         w.nb_cont_days
  from w, test t
) x
-- If these 2 fields match, then we have what we want
where x.m1 = x.nb_cont_days
  and x.m2 = x.nb_cont_days
order by 1, 2

您只需更改WITH子句中的参数，就可以从这个查询中创建一个函数来使用该参数调用它。

Answer 3

SELECT userID,count(userID) as numOfDays FROM LOGINTABLE WHERE logindate between '2014-01-01' AND '2014-02-28'
GROUP BY userID

在这种情况下，您可以检查每个用户在特定时间段内的登录天数。