MySQL5.7实现连续登陆天数计算

环境

MySQL5.7

问题场景

目前有个登陆记录表名为test，他的结构是这样的：

name	time
A	2022-10-09
B	2022-10-09
A	2022-10-08
B	2022-10-07
A	2022-10-06
A	2022-10-05

编写一个SQL，求出某个name距离今天连续登陆时间是几天？

测试数据

-- Table structure for test
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `name` varchar(255) DEFAULT NULL,
  `time` date DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- Records of test
BEGIN;
INSERT INTO `test` (`name`, `time`) VALUES ('A', '2023-04-09');
INSERT INTO `test` (`name`, `time`) VALUES ('A', '2023-04-08');
INSERT INTO `test` (`name`, `time`) VALUES ('A', '2023-04-07');
INSERT INTO `test` (`name`, `time`) VALUES ('A', '2023-04-06');
INSERT INTO `test` (`name`, `time`) VALUES ('A', '2023-04-02');
INSERT INTO `test` (`name`, `time`) VALUES ('A', '2023-04-01');
INSERT INTO `test` (`name`, `time`) VALUES ('B', '2023-04-08');
INSERT INTO `test` (`name`, `time`) VALUES ('C', '2023-04-08');
INSERT INTO `test` (`name`, `time`) VALUES ('A', '2023-04-10');
INSERT INTO `test` (`name`, `time`) VALUES ('C', '2023-04-07');
INSERT INTO `test` (`name`, `time`) VALUES ('B', '2023-04-09');
INSERT INTO `test` (`name`, `time`) VALUES ('C', '2023-04-10');
INSERT INTO `test` (`name`, `time`) VALUES ('D', '2023-04-09');
COMMIT; 

前置知识

窗口函数

MySQL8支持，当前版本无，可以使用会话变量实现

会话变量

SET @row_number = 0;
SELECT
  ( @row_number := @row_number + 1 ) AS ROW_NUMBER, `NAME`, `TIME`
FROM test 
ORDER BY NAME, TIME;

合并写法

SELECT
  ( @row_number := @row_number + 1 ) AS ROW_NUMBER, `NAME`, `TIME`
FROM test a,(select @row_number := 0) b
ORDER BY NAME, TIME;

日期对比函数

SELECT DATEDIFF('2023-04-10', '2023-04-08') AS diff;  -- 2

思考过程

增加一个字段，将记录中临近的日期，都改为最近的日期。

例如数据库中的10号、9号、8号三条记录，新字段都改成10号。

要实现这个需要引入两个会话变量，一个是上一条记录的日期@prev_time，另一个是当前记录的组@grp。

SELECT `NAME`, `TIME`, @grp := IF ( DATEDIFF( @prev_time, `TIME` ) = 1, @grp, `TIME` ) AS grp, @prev_time := `TIME`,
		CAST( UNHEX( HEX( @grp )) AS CHAR ) AS group_name
FROM test, ( SELECT @prev_time := NULL, @grp := NULL ) vars 
WHERE `NAME` = 'A' 
ORDER BY `TIME` DESC;

基于这个组我们可以写一个嵌套查询来求天数

SELECT `NAME`, MIN(`TIME`) AS start_time, MAX(`TIME`) AS end_time, COUNT(*) AS consecutive_days
FROM (
	SELECT `NAME`, `TIME`, @grp := IF ( DATEDIFF( @prev_time, `TIME` ) = 1, @grp, `TIME` ) AS grp, @prev_time := `TIME`,
			CAST( UNHEX( HEX( @grp )) AS CHAR ) AS group_name
	FROM test, ( SELECT @prev_time := NULL, @grp := NULL ) vars 
	WHERE `NAME` = 'A' 
	ORDER BY `TIME` DESC
) t
GROUP BY `NAME`, grp
HAVING grp  = CURRENT_DATE();

结果：