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

PostgreSQL About 1,827 words

数据准备

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)
Views: 445 · Posted: 2023-11-10

————        END        ————

Give me a Star, Thanks:)

https://github.com/fendoudebb/LiteNote

扫描下方二维码关注公众号和小程序↓↓↓

扫描下方二维码关注公众号和小程序↓↓↓
Today On History
Browsing Refresh