Hive|Hive-sql连续登陆问题 sql|hadoop|hive

在写sql语句的人经常会遇到这么一种情况:求连续登陆的这一批用户。遇到这样的问题,可能会比较棘手,现在跟着我动手写一遍,看下如何求连续登陆的问题。
需求是:求出连续登陆3天的这一批用户
思路: 首先通过窗口函数row number,根据user_id字段进行分区,按照login_date进行排序,窗口函数这一列起个别名为rk。接着通过sub函数将login_date减去rk,得到一个新的日期。接着对user_id和新日期进行groupby。得到的结果通过having 去筛选个数为3的id即可。
数据准备在本地创建文件: vim test_user_login.txt

123 2021-08-02 456 2021-11-02 123 2021-08-03 123 2021-08-04 456 2021-12-09 789 2021-01-01 789 2021-04-03 789 2021-09-10 789 2021-09-11 789 2021-09-12

建表创建表:

create table test_user_login_3days( user_id int, login_date date ) row format delimited fields terminated by '\t';

导入数据

load data locol inpath '/opt/module/datas/test_user_login.txt' into table test_user_login_3days

编写sql语句利用开窗函数,row number( ) over()函数,对user_id进行分区,以及login_date进行排序:

select user_id, login_date, row_number() over (partition by user_id order by login_date) as rn from test_user_login_3days;

结果:

OK user_id login_date rn 123 2021-08-02 1 123 2021-08-03 2 123 2021-08-04 3 456 2021-11-02 1 456 2021-12-09 2 789 2021-01-01 1 789 2021-04-03 2 789 2021-09-10 3 789 2021-09-11 4 789 2021-09-12 5

接着再利用date_sub函数(该函数的作用是根据输入的日期减去输入的参数)。

select A.user_id, A.login_date, date_sub (A.login_date,A.rn)AS inteval_days from ( select user_id, login_date, row_number() over (partition by user_id order by login_date) as rn from test_user_login_3days)A;

结果:

OK a.user_id a.login_date inteval_days 123 2021-08-02 2021-08-01 123 2021-08-03 2021-08-01 123 2021-08-04 2021-08-01 456 2021-11-02 2021-11-01 456 2021-12-09 2021-12-07 789 2021-01-01 2020-12-31 789 2021-04-03 2021-04-01 789 2021-09-10 2021-09-07 789 2021-09-11 2021-09-07 789 2021-09-12 2021-09-07 Time taken: 96.335 seconds, Fetched: 10 row(s)

最后再通过group by 根据user_id和interval_days进行聚合。
having 条件筛选个数=3即可。
所以最终的sql语句是:

select B.user_id from ( select A.user_id, A.login_date, date_sub (A.login_date,A.rn)AS inteval_days from ( select user_id, login_date, row_number() over (partition by user_id order by login_date) as rn from test_user_login_3days)A)B group by B.user_id,B.inteval_days having count(1) = 3;

【Hive|Hive-sql连续登陆问题】结果: