数据源示例：

建表语句：

CREATE TABLE `user_login` (

`id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'id',

`name` varchar(255) COLLATE utf8_bin DEFAULT NULL COMMENT '人名',

`login_date` datetime DEFAULT NULL COMMENT '登录日期',

PRIMARY KEY (`id`)

)

;

插入数据：

insert into user_login (name,login_date)
values
('A','2023-07-19 10:00:00'),
('A','2023-07-19 09:00:00'),
('A','2023-07-18 08:00:00'),
('A','2023-07-17 06:00:00'),
('A','2023-07-16 08:00:00'),
('A','2023-07-15 06:00:00'),
('A','2023-07-14 08:00:00'),
('A','2023-07-13 06:00:00'),
('A','2023-07-10 08:00:00'),
('A','2023-07-09 06:00:00'),
('B','2023-07-18 08:00:00'),
('B','2023-07-17 06:00:00'),
('B','2023-07-16 08:00:00'),
('C','2023-07-15 06:00:00'),
('C','2023-07-14 08:00:00'),
('C','2023-07-13 06:00:00'),
('C','2023-07-12 08:00:00'),
('C','2023-07-11 06:00:00'),
('A','2023-05-18 08:00:00'),
('A','2023-05-17 06:00:00'),
('A','2023-05-16 08:00:00'),
('A','2023-05-15 06:00:00'),
('A','2023-05-14 08:00:00'),
('A','2023-05-13 06:00:00'),
('A','2023-05-10 08:00:00'),
('A','2023-05-09 06:00:00'),
('B','2023-05-18 08:00:00'),
('B','2023-05-17 06:00:00'),
('B','2023-05-16 08:00:00'),
('C','2023-05-15 06:00:00'),
('C','2023-05-14 08:00:00'),
('C','2023-05-13 06:00:00'),
('C','2023-05-12 08:00:00'),
('C','2023-05-11 06:00:00'),
('A','2023-07-20 08:00:00'),
('A','2023-08-12 08:00:00'),
('A','2023-08-11 08:00:00'),
('A','2023-08-18 08:00:00'),
('A','2023-08-17 06:00:00'),
('A','2023-08-16 08:00:00'),
('A','2023-08-15 06:00:00'),
('A','2023-08-14 08:00:00'),
('A','2023-08-13 06:00:00'),
('A','2023-08-10 08:00:00'),
('A','2023-08-09 06:00:00'),
('B','2023-08-18 08:00:00'),
('B','2023-08-17 06:00:00'),
('B','2023-08-16 08:00:00'),
('C','2023-08-15 06:00:00'),
('C','2023-08-14 08:00:00'),
('C','2023-08-13 06:00:00'),
('C','2023-08-12 08:00:00'),
('C','2023-08-11 06:00:00')
;

1 计算本周内，连续4天登录的用户

备注：如果是连续登录N天，则将 having count(diff)>3 ，修改成having count(diff)>N即可

select name,count(diff) as "本周用户连续登录天数"

from

(select *,date_sub(new_login_date , interval date_rank DAY ) as diff

from

(select *,date(login_date) as new_login_date,

dense_rank ()over (partition by name order by login_date) as date_rank

from

(select *,DATE(subdate(curdate()-1,date_format(curdate()-1,'%w')-1)) as a1,

DATE(subdate(curdate()-1,date_format(curdate()-1,'%w')-7)) as a2

from user_login)t1

where login_date >a1 and login_date <a2)t2)t3

group by name

having count(diff)>3

输出结果表：

 

2 计算上周内，连续4天登录的用户

select name,count(diff) as "上周用户连续登录天数"

from

(select *,date_sub(new_login_date , interval date_rank DAY ) as diff

from

(select *,date(login_date) as new_login_date,

dense_rank ()over (partition by name order by login_date) as date_rank

from

(select *,DATE(subdate(curdate()-1,date_format(curdate()-1,'%w')+6)) as a1,

DATE(subdate(curdate()-1,date_format(curdate()-1,'%w')+0)) as a2

from user_login)t1

where login_date >a1 and login_date <a2)t2)t3

group by name

having count(diff)>3

;

输出结果表：

3 计算过去7天用户连续登录天数

select name,count(diff) as '用户近7天连续登录天数'

from

(select * ,date_sub(new_login_date , interval date_rank DAY) as diff

from

(select *,

dense_rank () over (partition by name order by login_date) as date_rank

from

(select *,date(login_date) as new_login_date

from user_login

where login_date >= curdate() - interval 7 DAY)t1)t2)t3

group by name

 

输出结果表

 

注意：

sql中函数计算的当下日期为2023-08-16，在其他日期情况下执行，本周和上周用户连续登录天数，输出结果不同。

情况一：提取上周日到本周六的数据

select * from 表名称 where DATE_SUB(CURDATE(), INTERVAL 6 DAY) <= date(表内时间字段);

情况二：提取本周一到本周日的数据

select* from 表名 where 时间字段名 between DATE(subdate(curdate()-1,date_format(curdate()-1,'%w')-1)) and  DATE(subdate(curdate()-1,date_format(curdate()-1,'%w')-7))