PostgreSQL 查询连续登录超过 7 天的用户
PostgreSQL 大约 1827 字数据准备
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)
阅读 91 · 发布于 2023-11-10
————        END        ————
Give me a Star, Thanks:)
https://github.com/fendoudebb扫描下方二维码关注公众号和小程序↓↓↓

昵称:
随便看看
换一批
-
Prometheus+Grafana+nginx-prometheus-exporter 监控 Nginx阅读 3969
-
Java 获取垃圾回收队列长度阅读 264
-
PostgreSQL duplicate key violates unique constraint阅读 2044
-
算法:插入排序阅读 930
-
Java 并发编程之 AtomicStampedReference阅读 1396
-
HTTP Bad chunk header 和 Illegal or missing hexadecimal sequence in chunked-encoding 解决方法阅读 906
-
Linux 查看 cron 定时任务执行日志阅读 6264
-
为什么 Redis 的 hash slot 设置为 16384阅读 2590
-
MySQL 开启日志阅读 1093
-
Android 监听 SIM 卡状态阅读 5687