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: 705 · Posted: 2023-11-10
————        END        ————
Give me a Star, Thanks:)
https://github.com/fendoudebb/LiteNote扫描下方二维码关注公众号和小程序↓↓↓
Loading...