Hive SQL写出连续三天都登录的用户

题目：

sql写出连续三天都登录的用户

建表及数据准备

create table test_user_login_3days(
    user_id int,
    login_date date
);
insert into test_user_login_3days values (123,'2018-08-02');
insert into test_user_login_3days values (123,'2018-08-03');
insert into test_user_login_3days values (123,'2018-08-04');
insert into test_user_login_3days values (456,'2018-11-02');
insert into test_user_login_3days values (456,'2018-12-09');
insert into test_user_login_3days values (789,'2018-01-01');
insert into test_user_login_3days values (789,'2018-04-23');
insert into test_user_login_3days values (789,'2018-09-10');
insert into test_user_login_3days values (789,'2018-09-11');
insert into test_user_login_3days values (789,'2018-09-12');
insert into test_user_login_3days values (10001,'2018-04-23');
insert into test_user_login_3days values (10001,'2018-04-24');
insert into test_user_login_3days values (10001,'2018-09-11');
insert into test_user_login_3days values (10001,'2018-09-12');

查询方法1

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;

查询方法2

查询思路：将用户登录日期按照增序排列，通过lead函数查找第三次登录的日期，如果第三次登录日期和登录日期相差2，则说明是连续登录三天的用户

SELECT 
    A.user_id 
FROM (SELECT 
    user_id,
    login_date,
    LEAD(login_date,2) OVER(PARTITION BY user_id ORDER BY login_date) AS lag_2days 
    FROM test_user_login_3days 
    ORDER BY user_id,login_date)A 
WHERE DATEDIFF(A.lag_2days,A.login_date) = 2;

查询方法3

查询思路：将用户登录日期按照增序排列，通过sum函数对用户进行分组标识，按照用户和分组标识分组大于2，则说明是连续登录三天的用户

select 
  ttt.user_id
from
(select 
  tt.user_id,
  tt.login_date,
  tt.lag_date,
  tt.user_flag,
  --按照用户标识user_flag，进行sum求和，作为用户分组的标识，如果3次连续登陆，则对应的用户分组标识相同
  sum(tt.user_flag) over(partition by tt.user_id order by tt.login_date) as user_group
from 
(select 
  t.user_id,
  t.login_date,
  t.lag_date,
  --按照相隔天数判断作为分组标识，如果相隔为1天满足要求，则为0，否则为1
  if(datediff(t.login_date,t.lag_date)=1,0,1) as user_flag  
from 
(select 
  user_id,
  login_date,
  lag(login_date,1) over(partition by user_id order by login_date) as  lag_date
from test.test_user_login_3days
order by user_id,login_date)t )tt)ttt
group by ttt.user_id,ttt.user_group
having count(1) >= 3