连续登录天数最多的用户

roych

先上测试数据源：

1. <div>DROP TABLE IF EXISTS user_login;</div><div>CREATE TABLE user_login( user_id VARCHAR(8), date DATE ) ;</div><div>INSERT INTO user_login  VALUES ('a001','2021-01-01') ,('b001','2021-01-01') ,('a001','2021-01-02') ,('a002','2021-01-02') ,</div><div>('a001','2021-01-03') ,('b001','2021-01-03') ,('a001','2021-01-04') ,('a001','2021-01-06') ,('a002','2021-01-06') ,('b001','2021-01-07')</div>

复制代码

返回结果如下图所示：

以下给出两种解决方法。
1.递归查询（CTE通用表）：

1. WITH cte (user_id, date, days, prev_date)
   
2.     AS
   
3.     (
   
4.         SELECT user_id, date, 1 as days, date as prev_date
   
5.         FROM user_login
   
6.         UNION ALL
   
7.         SELECT  a.user_id, a.date, case when datediff(day,a.date, b.prev_date)=1 then
   
8.                 days +1 else 0 end , a.date
   
9.         FROM user_login AS a
   
10.         INNER JOIN cte AS b
    
11.             ON a.user_id=b.user_id and b.date=DATEADD(day,1,a.date)
    
12.     )
    
13. select top 1 user_id,max(days) continue_days from cte
    
14. GROUP BY user_id
    
15. ORDER BY continue_days DESC

复制代码

递归查询先定义一个底层，再通过两个表互相关联，根据日期差对days字段自增，最后统计出最大次数的用户。
需要注意的是，加上”b.date=DATEADD(day,1,a.date)“这句以确定终止条件，否则可能会陷入死循环。


2.窗口函数（lead和row_numnber）：

1. select top 1 user_id, max(case when dates=1 then rnk+1 else 1 end) continue_days
   
2. from(
   
3. select user_id, date, datediff(day,date, lead(date,1,null) over(partition by user_id order by date)) dates,
   
4. row_number() over(partition by user_id order by date) rnk  from user_login
   
5. )
   
6. a
   
7. GROUP BY user_id
   
8. ORDER BY continue_days DESC

复制代码

相对复杂一些，需要使用lead窗口函数计算下一天和当天是否相差为1（1表示连续，其它数值则表示不连续），再通过row_number()排序，把等于1的最大值求出来，加上1就是连续天数了。
考虑到不少版友面试时可能用的是MySQL，所以，最后再给出MySQL的三种写法，语法上略有差异，这里就不再赘述了。

1. --方法1
   
2. WITH continuous_login AS (
   
3.   SELECT user_id,
   
4.          `date`,
   
5.          @rn:=IF(@previous_user = user_id AND @previous_date = DATE_ADD(`date`, INTERVAL -1 DAY),
   
6.                  @rn + 1,
   
7.                  IF(@previous_user := user_id, 1, 1)) AS rn,
   
8.          @previous_date:=`date`
   
9.   FROM user_login,
   
10.        (SELECT @rn:=0, @previous_user:='', @previous_date:='') AS init_vars
    
11.   ORDER BY user_id, `date`
    
12. )
    
13. SELECT user_id, MAX(rn) AS continue_days
    
14. FROM continuous_login
    
15. GROUP BY user_id
    
16. ORDER BY continue_days DESC
    
17. LIMIT 1;
    
18. --方法2
    
19. WITH RECURSIVE cet(user_id, date, continue_days, prev_date) AS (
    
20.     SELECT user_id, date, 1, date
    
21.     FROM user_login
    
22.     UNION ALL
    
23.     SELECT ul.user_id, ul.date,
    
24.         IF(DATEDIFF(ul.date, prev_date) = 1, continue_days + 1, 1),
    
25.         ul.date
    
26.     FROM user_login ul
    
27.     JOIN cet ON ul.user_id = cet.user_id AND ul.date = DATE_ADD(cet.date, INTERVAL 1 DAY)
    
28. )
    
29. SELECT user_id, MAX(continue_days) AS continue_days
    
30. FROM cet
    
31. GROUP BY user_id
    
32. ORDER BY continue_days DESC
    
33. LIMIT 1;
    
34. --方法3
    
35. SELECT user_id
    
36.         , max(if(dates = 1, rnk + 1, 1)) AS continue_days
    
37. FROM (
    
38.         SELECT user_id, date
    
39.                 , datediff(lead(date, 1, NULL) OVER (PARTITION BY user_id ORDER BY date), date) AS dates
    
40.                 , row_number() OVER (PARTITION BY user_id ORDER BY date) AS rnk
    
41.         FROM user_login
    
42. ) a
    
43. GROUP BY user_id
    
44. ORDER BY continue_days DESC
    
45. LIMIT 1;
    

复制代码

喜欢的话就回个贴吧。SQL Server板块毕竟那么冷清

accben

厉害了！谢谢分享。
不过如果是我碰到这种统计，我只会用ADO去解决。纯SQL语法搞不掂。

roych

accben 发表于 2023-3-15 08:08
厉害了！谢谢分享。
不过如果是我碰到这种统计，我只会用ADO去解决。纯SQL语法搞不掂。

Access里只能用ADO，SQL Server或MySQL等数据库才能使用这个语法。分享出来也是便于大家用来解决面试题或工作中的需求。