MySQL 8.0及5.7查询连续登陆7天以上的用户

查询7天连续登陆用户这个问题很经典，解决方法也有很多，这里我我参考另一位博友写的，自己实践了下，希望对大家有帮助。

具体思路:

1、因为每天用户登录次数可能不止一次，所以需要先将用户每天的登录日期去重。

2、再用row_number() over(partition by _ order by _)函数将用户id分组，按照登陆时间进行排序。

3、计算登录日期减去第二步骤得到的结果值，用户连续登陆情况下，每次相减的结果都相同。

4、按照id和日期分组并统计人数，筛选大于等于7的即为连续7天登陆的用户。

• 实践前准备：

• SET NAMES utf8mb4;
  SET FOREIGN_KEY_CHECKS = 0;
  
  -- ----------------------------
  -- Table structure for login_log
  -- ----------------------------
  DROP TABLE IF EXISTS `login_log`;
  CREATE TABLE `login_log`  (
    `id` int(0) NOT NULL AUTO_INCREMENT,
    `stu_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
    `createtime` datetime(6) NULL DEFAULT CURRENT_TIMESTAMP(6),
    PRIMARY KEY (`id`) USING BTREE
  ) ENGINE = InnoDB AUTO_INCREMENT = 20 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
  
  -- ----------------------------
  -- Records of login_log
  -- ----------------------------
  INSERT INTO `login_log` VALUES (1, 'zhangsan', '2021-03-07 09:58:29.438123');
  INSERT INTO `login_log` VALUES (2, 'zhangsan', '2021-03-03 09:58:29.438123');
  INSERT INTO `login_log` VALUES (3, 'zhangsan', '2021-03-05 09:58:29.438123');
  INSERT INTO `login_log` VALUES (4, 'zhangsan', '2021-03-01 09:58:29.438123');
  INSERT INTO `login_log` VALUES (5, 'lisi', '2021-02-04 09:58:29.438123');
  INSERT INTO `login_log` VALUES (6, 'lisi', '2021-02-03 09:58:29.438123');
  INSERT INTO `login_log` VALUES (7, 'lisi', '2021-02-02 09:58:29.438123');
  INSERT INTO `login_log` VALUES (8, 'lisi', '2021-02-01 09:58:29.438123');
  INSERT INTO `login_log` VALUES (9, 'lisi', '2021-02-05 09:58:29.438123');
  INSERT INTO `login_log` VALUES (10, 'lisi', '2021-02-06 09:58:29.438123');
  INSERT INTO `login_log` VALUES (11, 'lisi', '2021-02-07 09:58:29.438123');
  INSERT INTO `login_log` VALUES (12, 'lisi', '2021-02-08 09:58:29.438123');
  INSERT INTO `login_log` VALUES (13, 'xiaowang', '2021-02-05 09:58:29.438123');
  INSERT INTO `login_log` VALUES (14, 'xiaoli', '2021-02-06 09:58:29.438123');
  INSERT INTO `login_log` VALUES (15, 'xiaoli', '2021-02-07 09:58:29.438123');
  INSERT INTO `login_log` VALUES (16, 'xiaozhao', '2021-02-08 09:58:29.438123');
  INSERT INTO `login_log` VALUES (17, 'lisi', '2021-02-05 09:58:29.438123');
  INSERT INTO `login_log` VALUES (18, 'xiaozhao', '2021-02-06 09:58:29.438123');
  INSERT INTO `login_log` VALUES (19, 'lisi', '2021-02-07 09:58:29.438123');
  
  SET FOREIGN_KEY_CHECKS = 1;

• 查询表里面数据

• 

• 查询近7天连续登录sql语句
• 8.0版本实现方式

• -- 3 按照stu_name和日期分组并统计人数，筛选大于等于7的即为连续7天登陆的用户
  select  stu_name,count(num) num from 
  (
      -- 2 计算登录日期,登录时间-用row_number() over(partition by _ order by _)函数将用户id分组的结果值
      select stu_name,date(createtime)-row_number() over(partition by stu_name ORDER BY createtime) num from 
      (
      -- 1、去重，每天多次登录，只保留一条
      select distinct stu_name,DATE_FORMAT(createtime,'%Y-%m-%d')createtime  from login_log
      ) t1
  )t2 GROUP BY  stu_name  HAVING(count(1))>7

5.7版本实现方式 

-- 声明用户变量，记录行号和登录用户名
set @row_number:=0,@customer_no:='';
-- 3 如果连续登录，date(createtime)-num 结果会相等
select  stu_name,count( date(createtime)-num )as num from 
(
   -- 2 记录行号;
   select    @row_number:=
            case 
                when @customer_no=l1.stu_name then @row_number+1
                else 1
            end as num,
      @customer_no:= l1.stu_name  stuName
   ,stu_name,DATE_FORMAT(createtime,'%Y-%m-%d') createtime from 
    (
      -- 1 去除同一天登录多次
      select DISTINCT stu_name,DATE_FORMAT(createtime,'%Y-%m-%d') createtime from login_log  ORDER BY stu_name,createtime
    ) l1
  
) l2 GROUP BY l2.stu_name HAVING num>7