SQL:查询连续n天登录的用户

最新推荐文章于 2024-06-12 23:25:49 发布

|长梦|

最新推荐文章于 2024-06-12 23:25:49 发布

阅读量297

点赞数

文章标签： sql mysql 数据库

本文链接：https://blog.csdn.net/youdainnan/article/details/130286288

版权

使用 MySQL 解决用户连续n天登录的问题
博客地址：http://www.changmeng.top/
原文地址：MySQL 解决用户连续n天登录的问题

测试数据集

建表

-- `t`
create table if not exists `t`
(
`uid` varchar(256) not null comment '用户名',
`login_time` varchar(256) not null comment '时间'
) comment '`t`';

导入测试数据

insert into `t` (`uid`, `login_time`) values ('4', '2022-10-30');
insert into `t` (`uid`, `login_time`) values ('6', '2022-10-3');
insert into `t` (`uid`, `login_time`) values ('4', '2022-10-18');
insert into `t` (`uid`, `login_time`) values ('1', '2022-10-2');
insert into `t` (`uid`, `login_time`) values ('8', '2022-10-1');
insert into `t` (`uid`, `login_time`) values ('2', '2022-10-28');
insert into `t` (`uid`, `login_time`) values ('4', '2022-10-2');
insert into `t` (`uid`, `login_time`) values ('7', '2022-10-1');
insert into `t` (`uid`, `login_time`) values ('6', '2022-10-2');
insert into `t` (`uid`, `login_time`) values ('1', '2022-10-30');
insert into `t` (`uid`, `login_time`) values ('2', '2022-10-30');
insert into `t` (`uid`, `login_time`) values ('5', '2022-10-4');
insert into `t` (`uid`, `login_time`) values ('6', '2022-10-30');
insert into `t` (`uid`, `login_time`) values ('7', '2022-10-18');
insert into `t` (`uid`, `login_time`) values ('6', '2022-10-5');
insert into `t` (`uid`, `login_time`) values ('5', '2022-10-30');
insert into `t` (`uid`, `login_time`) values ('4', '2022-10-3');
insert into `t` (`uid`, `login_time`) values ('2', '2022-10-30');
insert into `t` (`uid`, `login_time`) values ('1', '2022-10-30');
insert into `t` (`uid`, `login_time`) values ('8', '2022-10-3');
insert into `t` (`uid`, `login_time`) values ('5', '2022-10-8');
insert into `t` (`uid`, `login_time`) values ('6', '2022-10-18');
insert into `t` (`uid`, `login_time`) values ('4', '2022-10-1');
insert into `t` (`uid`, `login_time`) values ('9', '2022-10-2');
insert into `t` (`uid`, `login_time`) values ('4', '2022-10-30');
insert into `t` (`uid`, `login_time`) values ('4', '2022-10-9');
insert into `t` (`uid`, `login_time`) values ('1', '2022-10-4');
insert into `t` (`uid`, `login_time`) values ('9', '2022-10-30');
insert into `t` (`uid`, `login_time`) values ('7', '2022-10-8');
insert into `t` (`uid`, `login_time`) values ('2', '2022-10-8');
insert into `t` (`uid`, `login_time`) values ('4', '2022-10-7');
insert into `t` (`uid`, `login_time`) values ('1', '2022-10-21');
insert into `t` (`uid`, `login_time`) values ('8', '2022-10-30');
insert into `t` (`uid`, `login_time`) values ('6', '2022-10-9');
insert into `t` (`uid`, `login_time`) values ('9', '2022-10-30');
insert into `t` (`uid`, `login_time`) values ('6', '2022-10-5');
insert into `t` (`uid`, `login_time`) values ('9', '2022-10-1');
insert into `t` (`uid`, `login_time`) values ('5', '2022-10-30');
insert into `t` (`uid`, `login_time`) values ('2', '2022-10-19');
insert into `t` (`uid`, `login_time`) values ('2', '2022-10-6');
insert into `t` (`uid`, `login_time`) values ('8', '2022-10-7');
insert into `t` (`uid`, `login_time`) values ('4', '2022-10-27');
insert into `t` (`uid`, `login_time`) values ('7', '2022-10-5');
insert into `t` (`uid`, `login_time`) values ('8', '2022-10-9');
insert into `t` (`uid`, `login_time`) values ('3', '2022-10-30');
insert into `t` (`uid`, `login_time`) values ('3', '2022-10-8');
insert into `t` (`uid`, `login_time`) values ('1', '2022-10-3');
insert into `t` (`uid`, `login_time`) values ('7', '2022-10-12');
insert into `t` (`uid`, `login_time`) values ('8', '2022-10-3');
insert into `t` (`uid`, `login_time`) values ('6', '2022-10-21');
insert into `t` (`uid`, `login_time`) values ('5', '2022-10-2');
insert into `t` (`uid`, `login_time`) values ('1', '2022-10-3');
insert into `t` (`uid`, `login_time`) values ('5', '2022-10-7');
insert into `t` (`uid`, `login_time`) values ('6', '2022-10-30');
insert into `t` (`uid`, `login_time`) values ('1', '2022-10-5');
insert into `t` (`uid`, `login_time`) values ('3', '2022-10-3');
insert into `t` (`uid`, `login_time`) values ('6', '2022-10-30');
insert into `t` (`uid`, `login_time`) values ('2', '2022-10-12');
insert into `t` (`uid`, `login_time`) values ('9', '2022-10-1');
insert into `t` (`uid`, `login_time`) values ('2', '2022-10-23');
insert into `t` (`uid`, `login_time`) values ('4', '2022-10-3');
insert into `t` (`uid`, `login_time`) values ('2', '2022-10-6');
insert into `t` (`uid`, `login_time`) values ('2', '2022-10-15');
insert into `t` (`uid`, `login_time`) values ('7', '2022-10-30');
insert into `t` (`uid`, `login_time`) values ('7', '2022-10-30');
insert into `t` (`uid`, `login_time`) values ('5', '2022-10-9');
insert into `t` (`uid`, `login_time`) values ('3', '2022-10-2');
insert into `t` (`uid`, `login_time`) values ('2', '2022-10-30');
insert into `t` (`uid`, `login_time`) values ('3', '2022-10-30');
insert into `t` (`uid`, `login_time`) values ('6', '2022-10-4');
insert into `t` (`uid`, `login_time`) values ('9', '2022-10-5');
insert into `t` (`uid`, `login_time`) values ('5', '2022-10-9');
insert into `t` (`uid`, `login_time`) values ('6', '2022-10-6');
insert into `t` (`uid`, `login_time`) values ('9', '2022-10-30');
insert into `t` (`uid`, `login_time`) values ('4', '2022-10-7');
insert into `t` (`uid`, `login_time`) values ('5', '2022-10-5');
insert into `t` (`uid`, `login_time`) values ('1', '2022-10-5');
insert into `t` (`uid`, `login_time`) values ('1', '2022-10-1');
insert into `t` (`uid`, `login_time`) values ('7', '2022-10-5');
insert into `t` (`uid`, `login_time`) values ('5', '2022-10-29');
insert into `t` (`uid`, `login_time`) values ('6', '2022-10-6');
insert into `t` (`uid`, `login_time`) values ('9', '2022-10-5');
insert into `t` (`uid`, `login_time`) values ('6', '2022-10-18');
insert into `t` (`uid`, `login_time`) values ('3', '2022-10-2');
insert into `t` (`uid`, `login_time`) values ('6', '2022-10-11');
insert into `t` (`uid`, `login_time`) values ('1', '2022-10-30');
insert into `t` (`uid`, `login_time`) values ('8', '2022-10-30');
insert into `t` (`uid`, `login_time`) values ('8', '2022-10-30');
insert into `t` (`uid`, `login_time`) values ('4', '2022-10-28');
insert into `t` (`uid`, `login_time`) values ('5', '2022-10-5');
insert into `t` (`uid`, `login_time`) values ('3', '2022-10-23');
insert into `t` (`uid`, `login_time`) values ('5', '2022-10-1');
insert into `t` (`uid`, `login_time`) values ('3', '2022-10-1');
insert into `t` (`uid`, `login_time`) values ('9', '2022-10-2');
insert into `t` (`uid`, `login_time`) values ('1', '2022-10-15');
insert into `t` (`uid`, `login_time`) values ('8', '2022-10-9');
insert into `t` (`uid`, `login_time`) values ('9', '2022-10-24');
insert into `t` (`uid`, `login_time`) values ('4', '2022-10-1');
insert into `t` (`uid`, `login_time`) values ('9', '2022-10-4');
insert into `t` (`uid`, `login_time`) values ('7', '2022-10-2');

使用 lag() 函数解

以连续登录5天为例

在MySQL-8.0中使用WITH建立临时表可以使代码清晰。

表t1的作用是过滤相同时间段同一个用户多次登录。

表t2的作用是查询连续登录的日期；使用 OVER 以uid进行分区并以ymd进行排序开窗显示 lag 函数( ymd在t1表中的小且第4大的日期，如果没有则为null)；再使用 datediff 函数(ymd与 lag函数的差值)显示差值，如果值为4则表示该用户连续登录了5天

WITH 
t1 AS (
    SELECT DISTINCT uid, date(login_time) ymd
    FROM t
),
t2 AS (
    SELECT uid, ymd, datediff(ymd, lag(ymd, 4) OVER (PARTITION BY uid ORDER BY ymd)) diff
    FROM t1
)
SELECT uid, date_sub(ymd, INTERVAL 4 DAY) min_date, ymd
FROM t2
WHERE diff = 4;

SELECT uid, ymd
FROM (
	SELECT uid, ymd, datediff(ymd, lag(ymd, 4) OVER (PARTITION BY uid ORDER BY ymd)) diff
	FROM (
		SELECT DISTINCT uid, date(login_time) ymd
		FROM t) t1
) t2
WHERE diff = 4;

使用 row_number() 函数解

以连续登录5天为例

在MySQL-8.0中使用WITH建立临时表可以使代码清晰。

表t1的作用是过滤相同时间段同一个用户多次登录。

表t2的作用是查询连续登录的日期；使用 OVER 以uid进行分区并以ymd进行排序开窗显示 row_number 函数(从1开始排序)；再使用 INTERVAL 关键字取 DAY 的间隔日期数组，最后使用 date_sub 函数显示间隔时间后的日期，如果日期是连续的则日期应该相同；最后进行分组即可得到连续天数。

WITH
t1 AS (
    SELECT DISTINCT uid, date(login_time) ymd
    FROM t
),
t2 AS (
    SELECT uid, ymd, date_sub(ymd, INTERVAL row_number() OVER (PARTITION BY uid ORDER BY ymd) DAY) sub_date
    FROM t1
)
SELECT uid, count(*)
FROM t2
GROUP BY uid, sub_date
HAVING count(*) >= 5

SELECT uid, count(*)
FROM(
	SELECT uid, ymd, date_sub(ymd, INTERVAL row_number() OVER (PARTITION BY uid ORDER BY ymd) DAY) sub_date
	    FROM(
	    SELECT DISTINCT uid, date(login_time) ymd
	    FROM t) t1) t2
GROUP BY uid, sub_date
HAVING count(*) >= 5;