MYSQL查询至少连续n天登录的用户

数据

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
-- Table structure for orde
-- ----------------------------
DROP TABLE IF EXISTS `orde`;
CREATE TABLE `orde` (
  `id` varchar(10) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `date` datetime NOT NULL,
  `orders` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL,
  PRIMARY KEY (`id`,`date`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;

-- ----------------------------
-- Records of orde
-- ----------------------------
BEGIN;
INSERT INTO `orde` VALUES ('1', '2022-01-01 00:00:00', '101');
INSERT INTO `orde` VALUES ('1', '2022-01-02 00:00:00', '109');
INSERT INTO `orde` VALUES ('1', '2022-01-03 00:00:00', '150');
INSERT INTO `orde` VALUES ('1', '2022-01-04 00:00:00', '991');
INSERT INTO `orde` VALUES ('1', '2022-01-05 00:00:00', '145');
INSERT INTO `orde` VALUES ('1', '2022-01-06 00:00:00', '155');
INSERT INTO `orde` VALUES ('1', '2022-01-07 00:00:00', '199');
INSERT INTO `orde` VALUES ('1', '2022-01-08 00:00:00', '188');
INSERT INTO `orde` VALUES ('2', '2022-01-02 00:00:00', '109');
INSERT INTO `orde` VALUES ('3', '2022-01-03 00:00:00', '150');
INSERT INTO `orde` VALUES ('4', '2022-01-01 00:00:00', '100');
INSERT INTO `orde` VALUES ('4', '2022-01-04 00:00:00', '199');
INSERT INTO `orde` VALUES ('5', '2022-01-05 00:00:00', '145');
INSERT INTO `orde` VALUES ('6', '2022-01-06 00:00:00', '1455');
INSERT INTO `orde` VALUES ('7', '2022-01-07 00:00:00', '199');
INSERT INTO `orde` VALUES ('8', '2022-01-08 00:00:00', '188');
INSERT INTO `orde` VALUES ('9', '2022-01-01 00:00:00', '110');
INSERT INTO `orde` VALUES ('9', '2022-01-02 00:00:00', '109');
INSERT INTO `orde` VALUES ('9', '2022-01-03 00:00:00', '150');
INSERT INTO `orde` VALUES ('9', '2022-01-04 00:00:00', '919');
INSERT INTO `orde` VALUES ('9', '2022-01-06 00:00:00', '145');
INSERT INTO `orde` VALUES ('9', '2022-01-09 00:00:00', '455');
INSERT INTO `orde` VALUES ('9', '2022-01-10 00:00:00', '199');
INSERT INTO `orde` VALUES ('9', '2022-01-13 00:00:00', '188');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;

排名

SELECT 
	*,
	ROW_NUMBER() over (PARTITION by id ORDER BY date) rn 
FROM orde

思路：连续日期 - 连续排序值 = 固定一个日期
这样就能统计到底有多少个连续日期的数据

SELECT
	*,
	DATE_SUB(tmp.date, INTERVAL tmp.rn day) as dateSub
FROM
  (SELECT 
		*,
		ROW_NUMBER() over (PARTITION by id ORDER BY date) rn 
	FROM orde) tmp

然后根据 dateSub, id 分组统计个数
计算连续时间段的最早和最晚时间

SELECT 
	tmp2.id,
	min(tmp2.date) startDate,
	max(tmp2.date) endDate,
	count(*) count
FROM
 (SELECT
	*,
	DATE_SUB(tmp.date, INTERVAL tmp.rn day) as dateSub
FROM
  (SELECT 
		*,
		ROW_NUMBER() over (PARTITION by id ORDER BY date) rn 
	FROM orde) tmp) tmp2
GROUP BY tmp2.dateSub, tmp2.id

增加判断，指定获取连续3天以上的数据

SELECT 
	tmp2.id,
	min(tmp2.date) startDate,
	max(tmp2.date) endDate,
	count(*) count
FROM
 (SELECT
	*,
	DATE_SUB(tmp.date, INTERVAL tmp.rn day) as dateSub
FROM
  (SELECT 
		*,
		ROW_NUMBER() over (PARTITION by id ORDER BY date) rn 
	FROM orde) tmp) tmp2
GROUP BY tmp2.dateSub, tmp2.id
HAVING count(*) > 3