题目：

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;

最后

以上就是魁梧咖啡为你收集整理的Hive SQL写出连续三天都登录的用户题目：建表及数据准备的全部内容，希望文章能够帮你解决Hive SQL写出连续三天都登录的用户题目：建表及数据准备所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。