hive sql 实现连续登录查询

一.问题描述

获取登录日志,怎样查询一个人是否连续几天登录?

二..数据

张三,2021-11-28 10:30:00
李四,2021-11-28 10:30:00
王五,2021-11-28 10:30:00
张三,2021-11-28 10:40:00
赵六,2021-11-28 10:30:00
田七,2021-11-28 10:30:00
张三,2021-11-29 10:30:00
张三,2021-11-29 10:40:00
张三,2021-11-30 10:30:00
张三,2021-11-30 10:40:00
张三,2021-12-01 10:30:00
张三,2021-12-01 10:40:00
李四,2021-11-30 10:30:00
李四,2021-11-30 10:40:00
李四,2021-12-02 10:20:00
李四,2021-12-02 10:30:00
王五,2021-11-30 10:30:00
王五,2021-12-01 10:30:00
王五,2021-12-02 10:30:00
赵六,2021-11-29 10:30:00
赵六,2021-12-01 10:30:00
赵六,2021-12-02 10:30:00
赵六,2021-12-04 10:30:00
赵六,2021-12-05 10:30:00
田七,2021-11-29 10:30:00
田七,2021-11-30 10:30:00

三.解析

此处以连续三天为例,使用Hive的hsql实现。

 1.将数据存入linux中的/root/data/login.txt文件中

2.创表,导入数据

 查询所有数据

 3.因为要判断连续几天登录，所以先把时分秒去掉,这里使用substr()函数进行截取

select distinct l.name,substr(l.login_date,1,10) ldate from t_login l;

查询结果:

4.方式一:

当前日期+2得到第三天日期,将以上数据进行分组排序后，再向下找两条数据，最后看二者是否相等。使用开窗函数lead()over()进行分组,排序,与查找下面的第三条数据,使用date_add()函数实现日期加两天得到第三天日期。

select t.*,date_add(t.ldate,2) day1 ,lead(t.ldate,2)over(partition by t.name order by t.ldate) day2 from
    (select distinct l.name,substr(l.login_date,1,10) ldate from t_login l) t

 查询结果:

例如:第一条数据 张三 2021-11-28 登录 当前日期加两天为 day1:2021-11-30 ，以当前数据为基准向下查找两条数据 也就是第三条数据 为 张三  2021-11-30  day2:2021-11-30 如果day1与day2相等，则说明该用户连续登录了三天。

方式二:

上述方法虽然可以查到是否连续登录，但却无法得到连续登录的天数，无法根据连续登录的天数进行排名，下面开始介绍方法二:

首先根据姓名求排名,此处使用开窗函数rank() over () 将其根据姓名分组，并根据日期排序

select t.*,rank() over (partition by t.name order by t.ldate)num from 
(select distinct l.name,substr(l.login_date,1,10) ldate from t_login l) t;

查询结果:

 num列即为每组的排名,观察此表可以发现一些规律，如果连续登录了，那么每组的当前登录日期减去排名都会得到一个相同的日期。接下来将当前登录日期减去排名看看能够得到怎样的结果

select tt.*,date_sub(tt.ldate,num) cha from 
(select t.*,rank() over (partition by t.name order by t.ldate)num from
        (select distinct l.name,substr(l.login_date,1,10) ldate from t_login l) t) tt;

查询结果:

在以cha值与姓名进行分组，并统计个数

select ttt.name,count(1) cnt from 
( select tt.*,date_sub(tt.ldate,num) cha from (select t.*,rank() over (partition by t.name order by t.ldate)num from
    (select distinct l.name,substr(l.login_date,1,10) ldate from t_login l) t) tt) ttt group by ttt.name,ttt.cha

查询结果:

 分析此表:

张三连续登录了4天，李四有可能登录一天之后隔了一天又登录了一天，之后隔了一天也登录一天，并非连续登录，依次逐个分析。如果要得到连续登录超过三天的用户可以在group by  后加上 having cnt>=3

select ttt.name,count(1) cnt from 
( select tt.*,date_sub(tt.ldate,num) cha from (select t.*,rank() over (partition by t.name order by t.ldate)num from
    (select distinct l.name,substr(l.login_date,1,10) ldate from t_login l) t) tt) ttt group by ttt.name,ttt.cha having cnt>=3;

查询结果:

亦可以根据cnt进行排序,获取连续登录时间最长的用户等等，此处就不在展开。

如有错误，欢迎指正。

The end!