Hive SQL写出连续三天都登录的用户题目：建表及数据准备

92 阅读 0 评论 61 点赞

我是靠谱客的博主魁梧咖啡，这篇文章主要介绍Hive SQL写出连续三天都登录的用户题目：建表及数据准备，现在分享给大家，希望可以做个参考。

题目：

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

建表及数据准备

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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

复制代码

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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，则说明是连续登录三天的用户

复制代码

1
2
3
4
5
6
7
8
9
10
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;