PostgreSQL 查询连续登录超过 7 天的用户

数据准备

create table login_log(id varchar(255), log_time date);
INSERT INTO login_log(id, log_time) VALUES ('101', '2022-02-14');
INSERT INTO login_log(id, log_time) VALUES ('101', '2022-02-15');
INSERT INTO login_log(id, log_time) VALUES ('102', '2022-02-15');
INSERT INTO login_log(id, log_time) VALUES ('101', '2022-02-16');
INSERT INTO login_log(id, log_time) VALUES ('101', '2022-02-17');
INSERT INTO login_log(id, log_time) VALUES ('101', '2022-02-18');
INSERT INTO login_log(id, log_time) VALUES ('101', '2022-02-19');
INSERT INTO login_log(id, log_time) VALUES ('101', '2022-02-20');
INSERT INTO login_log(id, log_time) VALUES ('101', '2022-02-21');
INSERT INTO login_log(id, log_time) VALUES ('101', '2022-02-22');
INSERT INTO login_log(id, log_time) VALUES ('102', '2022-02-23');
INSERT INTO login_log(id, log_time) VALUES ('102', '2022-02-24');
INSERT INTO login_log(id, log_time) VALUES ('103', '2022-02-14');
INSERT INTO login_log(id, log_time) VALUES ('103', '2022-02-15');
INSERT INTO login_log(id, log_time) VALUES ('103', '2022-02-16');
INSERT INTO login_log(id, log_time) VALUES ('103', '2022-02-18');
INSERT INTO login_log(id, log_time) VALUES ('103', '2022-02-19');
INSERT INTO login_log(id, log_time) VALUES ('103', '2022-02-20');
INSERT INTO login_log(id, log_time) VALUES ('103', '2022-02-21');

lag 窗口函数

lag()和lead()表示计算当前行的前n行或后n行，n默认是1。

select distinct on(id) id, log_time, lag
from (select *, lag(log_time, 6) over (partition by id order by log_time) lag from login_log) temp
where log_time - lag <= 6;

输出

z-blog=# select distinct on(id) id, log_time, lag
z-blog-# from (select *, lag(log_time, 6) over (partition by id order by log_time) lag from login_log) temp
z-blog-# where log_time - lag <= 6;
 id  |  log_time  |    lag
-----+------------+------------
 101 | 2022-02-20 | 2022-02-14
(1 row)