实现查询连续3天登陆的用户

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

1、创建表

create table test_user_login_3days(
user_id int,
login_date string
);

2、数据准备

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'); 

3、查询逻辑一：

思路：连续三天的逻辑变更为按照user_id分组排序之后，前后两个日期相减为1，再相加等于3

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;

 4、查询逻辑二：第三天减去第一天的差为2，只要存在差为2的都是满足连续三天登录的情况

SELECT
A.user_id,A.login_date,A.lag_2days
FROM (SELECT
user_id,
login_date,
LEAD(login_date,2,’9999-12-31′) 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;

同理，这种情况可以继续推到满足连续N天登录的情况。

原文地址：http://www.cnblogs.com/miduofanxiang/p/16795825.html