【HiveSql面试题】SQL求连续登陆天数(允许间隔)

一、需求

        求每个用户最大的连续登陆天数，断一天还算连续登录（两个日期的差小于或等于 2 ）。
        比如 11-06 号登录，最近的下一次登录是 01-08 号，两个日期的差等于 2 天，因此这两个日期之间的天数都算作连续天数，一共 3 天。

测试数据：

user_id		login_date

1001		2021-11-05
1001		2021-11-06
1001		2021-11-08
1001		2021-11-10
1001		2021-11-15
1001		2021-11-16
1001		2021-11-18
1001		2021-11-19
1002		2021-11-05
1002		2021-11-07
1002		2021-11-09
1002		2021-11-11
1002		2021-11-13
1002		2021-11-16

预期数据结果：

user_id		max_continuous_login_days

1001		6
1002		9

准备数据：

//创建测试表test_login
create table if not exists test_login(
    user_id string,
    login_date string) 
row format delimited fields terminated by '\t'; 
//装载数据
load data local inpath '/opt/module/data/test1_login.txt' into table test_login;

二、解决思路

        1.相邻两次登录日期差值小于等于2即为同一连续登录组
        2.相邻两次登录日期差值大于2更换连续登录组（相邻两个连续登录组的分隔条件为分隔位置登录日期差值大于2）
        3.连续登录日期组最大日期-最小日期差值加1即为连续登录天数

三、分步代码及测试

1.获取上一次登陆日期

        找到上一次登陆日期，与此次的登陆日期对比，才可以判断是否符合小于等于2天的条件。
        此处我们使用lag窗口函数，开窗查询前一行数据，注意每个用户的第一条登陆信息上一天为null，这里我们给1970-01-01，防止空指针异常。

select
    user_id,
    login_date,
    lag(login_date,1,'1970-01-01') over(partition by user_id order by login_date) last_login_date
from test_login; t1

查询结果：

user_id	login_date	last_login_date

1001	2021-11-05	1970-01-01
1001	2021-11-06	2021-11-05
1001	2021-11-08	2021-11-06
1001	2021-11-10	2021-11-08
1001	2021-11-15	2021-11-10
1001	2021-11-16	2021-11-15
1001	2021-11-18	2021-11-16
1001	2021-11-19	2021-11-18
1002	2021-11-05	1970-01-01
1002	2021-11-07	2021-11-05
1002	2021-11-09	2021-11-07
1002	2021-11-11	2021-11-09
1002	2021-11-13	2021-11-11
1002	2021-11-16	2021-11-13

2.获取相邻登录日期差值

        将当前行数据的登录日期减去上一行数据的登录日期。

select
	user_id,
	login_date,
	datediff(login_date,last_login_date) date_diff
from t1; t2

查询结果：

user_id	login_date	date_diff

1001	2021-11-05	18936
1001	2021-11-06	1
1001	2021-11-08	2
1001	2021-11-10	2
1001	2021-11-15	5
1001	2021-11-16	1
1001	2021-11-18	2
1001	2021-11-19	1
1002	2021-11-05	18936
1002	2021-11-07	2
1002	2021-11-09	2
1002	2021-11-11	2
1002	2021-11-13	2
1002	2021-11-16	3

3.划分连续登录组

        相邻两次登录日期差值小于等于2的数据为一个连续登录组，每遇到相邻两次登录日期差值大于2的数据，分组编号+1，更换连续登录组。

select
	user_id,
	login_date,
	sum(if(date_diff>2,1,0)) over(partition by user_id order by login_date rows between unboundedpreceding and current row) group_id
from t2; t3

查询结果：

user_id	login_date	group_id

1001	2021-11-05	1
1001	2021-11-06	1
1001	2021-11-08	1
1001	2021-11-10	1
1001	2021-11-15	2
1001	2021-11-16	2
1001	2021-11-18	2
1001	2021-11-19	2
1002	2021-11-05	1
1002	2021-11-07	1
1002	2021-11-09	1
1002	2021-11-11	1
1002	2021-11-13	1
1002	2021-11-16	2

4.计算连续登录天数

        每个连续登录日期组中，最大日期-最小日期差值+1 即为连续登录天数。

select
	user_id,
	group_id,
	(datediff(max(login_date),min(login_date))+1) continuous_login_days
from t3
group by user_id,group_id; t4

查询结果：

user_id	group_id	continuous_login_days

1001	1	        6
1001	2	        5
1002	1	        9
1002	2	        1

5.计算每个用户最大的连续登陆天数

        按用户取最大连续登录天数。

select
	user_id,
	max(continuous_login_days) max_continuous_login_days
from t4
group by user_id;

查询结果：

user_id	max_continuous_login_days
1001	6
1002	9

四、最终代码及测试

select
    user_id,
    max(continuous_login_days) max_continuous_login_days
from
    (
        select
            user_id,
            group_id,
            (datediff(max(login_date), min(login_date)) + 1) continuous_login_days
        from
            (
                select
                    user_id,
                    login_date,
                    sum(if(date_diff > 2, 1, 0))
                        over (partition by user_id order by login_date rows between unbounded preceding and current row) group_id
                from
                    (
                        select
                            user_id,
                            login_date,
                            datediff(login_date, last_login_date) date_diff
                        from
                            (select
                                 user_id,
                                 login_date,
                                 lag(login_date, 1, '1970-01-01')
                                     over (partition by user_id order by login_date) last_login_date
                             from
                                 test_login) t1) t2) t3
        group by user_id, group_id) t4
group by user_id;

测试结果：

user_id	max_continuous_login_days
1001	6
1002	9

五、拓展

        对于求每个用户最大的连续登陆天数，断一天还算连续登录（两个日期的差小于或等于 2 ），这个需求可以扩展为断n天还算连续登录，只需要修改sum(if(date_diff > 2, 1, 0)) over(...)，2换成n+1即可。

        提醒：由于本人使用的是spark引擎，datediff函数在子查询中且开窗使用，时间字段使用为String类型时，会报空指针异常。

        解决方法：

                方法1：将hive改为使用MR引擎

                方法2：改时间字段为Date类型

                方法3：在窗口函数中补全窗口子句rows between unbounded preceding and current row