连续登录问题

发表于 更新于 分类于 阅读次数： Valine：

问题

连续登录问题，是实际分析需求和面试中常见的问题。

其实，连续登录问题常规来说有两种场景，其难度完全不一样。

第一种是指定连续登录天数，求满足的用户
第二种则是求用户连续登录了多少天。

本文只是简单探讨。

语法

本文使用的spark sql的语法，如有差异，自行替换。

表结构&数据

明细就直接忽略了，直接以用户登录日期粒度的表结构为主,实际需求中可以自行聚合到这个粒度。
表结构&数据

user_id login_date
1 ‘2023-01-01’
1 ‘2023-01-02’
1 ‘2023-01-03’
1 ‘2023-01-04’
1 ‘2023-01-11’
1 ‘2023-01-12’
1 ‘2023-01-15’
2 ‘2023-01-15’
3 ‘2023-01-01’
3 ‘2023-01-02’
3 ‘2023-01-03’ 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
with user_log as (
    select col1 as user_id, col2 as login_date
    from values
         (1, '2023-01-01')
         ,(1, '2023-01-02')
         ,(1, '2023-01-03')
         ,(1, '2023-01-04')
         ,(1, '2023-01-11')
         ,(1, '2023-01-12')
         ,(1, '2023-01-15')
         ,(2, '2023-01-15')
         ,(3, '2023-01-01')
         ,(3, '2023-01-02')
         ,(3, '2023-01-03')
)
select * from user_log

场景

场景1 给定具体日期，且指定连续登录天数

例如： 求在2023-01-03号那天连续登录3天的用户。

这种场景是最简单的，直接聚合+having

1
2
3
4
5
6
7
select user_id
,count(*) as cnt
from user_log
where login_date between date_sub('2023-01-03',2) and '2023-01-03'
group by user_id
having cnt>=3
order by user_id

结果：

user_id cnt
1 3
1 3

场景2，不给定具体日期，但指定连续登录天数

例如：求用户在哪几天连续登录2天。

这部分需要用到窗口函数了，同时由于日期间隔为1，可以使用range特性

1
2
3
4
select *
, count(*) over (partition by user_id order by datediff(login_date, '2000-01-01') range between 1 preceding and current row ) as continue_cnt
 from user_log
 order by user_id,login_date

结果如下

user_id login_date continue_cnt
1 2023-01-01 1
1 2023-01-02 2
1 2023-01-03 2
1 2023-01-04 2
1 2023-01-11 1
1 2023-01-12 2
1 2023-01-15 1
2 2023-01-15 1
3 2023-01-01 1
3 2023-01-02 2
3 2023-01-03 2

结果没有过滤，是为了方便理解，大家自行加上continue_cnt=2即可。

场景3，既不给定具体业务日期，也不指定连续登录天数。

这种场景是最难的，最耗性能的，但也是实用性最高的。

row_number 解法

说实话，这个解法本人没有想出来，是网上搜到的，使用row_number进行排序，利用日期差特性，如果是连续的，那么实际的差值日期是一样的特性。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
,login_date_grp as (
    select *
    ,date_sub(login_date,login_date_rn) as continue_date_grp --连续登录日期分组
    from
        (
         select *
        ,row_number() over (partition by user_id order by login_date) as login_date_rn
        from user_log
        order by user_id,login_date
        ) a
)
select user_id
,login_date
,row_number() over(partition by user_id,continue_date_grp order by login_date) as continue_login_day_cnt --连续登录天数
,login_date_rn --用户登录日期排序
,continue_date_grp --用户连续登录日期分组
from login_date_grp
order by user_id
,login_date

结果如下

user_id login_date continue_login_days_cnt login_date_rn continue_date_grp
1 2023-01-01 1 1 2022-12-31
1 2023-01-02 2 2 2022-12-31
1 2023-01-03 3 3 2022-12-31
1 2023-01-04 4 4 2022-12-31
1 2023-01-11 1 5 2023-01-06
1 2023-01-12 2 6 2023-01-06
1 2023-01-15 1 7 2023-01-08
2 2023-01-15 1 1 2023-01-14
3 2023-01-01 1 1 2022-12-31
3 2023-01-02 2 2 2022-12-31
3 2023-01-03 3 3 2022-12-31

可以看到，结果是符合我们预期的，这时候只要外面再套上一层过滤条件即可。

看的稍微有点蒙？这里稍微解释一下，这里巧妙的利用了等差数组的特性。
比如有如下一个用户连续5天登录的数据，同时我们对同一用户按照日期进行row num排序，再求每一行和’2023-01-01’这一天的差值日期，然后再对这两列求差值，会看到如下结果

用户id 登录日期 日期排序 和’2022-12-31’的间隔天数 日期排序和’2023-01-01’的间隔天数的差值
1 2023-01-01 1 1 0
1 2023-01-02 2 2 0
1 2023-01-03 3 3 0
1 2023-01-04 4 4 0
1 2023-01-05 5 5 0

我们可以观察到，如果日期是连续的，那么本身和’2022-12-31’的间隔天数这一列就是+1的规则，而row num也是自然+1的，差值永远是1

此时，如果删除2023-01-03这一天的登录记录，那么结果就会变为

用户id 登录日期 日期排序 和’2022-12-31’的间隔天数 日期排序和’2022-12-31’的间隔天数的差值
1 2023-01-01 1 1 0
1 2023-01-02 2 2 0
1 2023-01-04 3 4 1
1 2023-01-05 4 5 1
可以看到，随着2023-01-03这条记录被删除，在2023-01-04这一天，间隔从0变为了1，而2023-01-05这天还是连续登录的，间隔仍然是1.

此时就能发现规律了，如果是连续登录的，那么间隔差值就不会变，这里其实可以用数学公式证明，这里就补展开了。

所以，为什么代码里用的是date_sub作为分组？其实我们要求的是差值，而日期本身就是连续的数字，date_sub(日期，rn) 是不是就是可以看成差值。 还节约了计算资源。

这题的解法，本人认为还是有很多精巧设计在里面的，一般确实很难想到。

使用日期维表获取上一次未登陆日期

这个是本人想到的，可以利用日期维表，补全没有登录的天数，然后利用窗口函数求出上一次未登陆日期，再利用datediff求出差值即连续登录天数。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
dim_date as (
  select t1.*
  ,date_add('2022-12-31',row_num) as dt_ymd
  from
      (
  select row_number() over(order by m) as row_num
  from
      (select split(repeat(",",15),",") as multi) a
  LATERAL VIEW explode(multi) tmpTable as m
  ) t1
)
,user_full_date as (
    select t1.user_id
     ,t2.dt_ymd
    from
        (select distinct user_id from user_log) t1
    cross join dim_date t2
)
,last_unlogin as (
    select a.user_id
    ,a.dt_ymd
    ,b.user_id as user_id2
    ,b.login_date
    ,last_value(if(b.login_date is not null,null,a.dt_ymd),true) over(partition by a.user_id order by a.dt_ymd  rows between unbounded preceding and current row ) as last_unlogin_date
    from user_full_date a
    left join user_log b
    on a.dt_ymd=b.login_date
    and a.user_id=b.user_id
)
select user_id
,login_date
,date_diff(login_date,nvl(last_unlogin_date,'2022-12-31')) as continue_days
,last_unlogin_date
from last_unlogin
where user_id2 is not null
order by user_id,dt_ymd


结果如下

user_id login_date continue_days last_unlogin_date
1 2023-01-01 1 null
1 2023-01-02 2 null
1 2023-01-03 3 null
1 2023-01-04 4 null
1 2023-01-11 1 2023-01-10
1 2023-01-12 2 2023-01-10
1 2023-01-15 1 2023-01-14
2 2023-01-15 1 2023-01-14
3 2023-01-01 1 null
3 2023-01-02 2 null
3 2023-01-03 3 null

可以看到结果符合预期。

总结

一直以为自己sql水平还是不错的，原来以前一直没怎么处理过连续登录问题，面试被问了还是懵逼了一下。该总结的还得总结一下。

总的来说，连续登录还是比较难的，尤其是考虑性能的情况下，使用row_number利用等差数据确实是一个很精妙的解法。