MySQL 查询连续登陆7天以上的用户【窗口函数】

　　注意：本文使用的row_number()【窗口函数】是MySql8.0版本才有，MySql5.7及以下是不存在此函数

窗口函数和普通聚合函数的区别

　　 ①聚合函数是将多条记录聚合为一条；窗口函数是每条记录都会执行，有几条记录执行完还是几条。

②聚合函数也可以用于窗口函数。

窗口函数的一个概念是当前行，当前行属于某个窗口，窗口由over关键字用来指定函数执行的窗口范围，如果后面括号中什么都不写，则意味着窗口包含满足where条件的所有行，窗口函数基于所有行进行计算；如果不为空，则有三个参数来设置窗口：

　　partition by子句：按照指定字段进行分区，两个分区由边界分隔，窗口函数在不同的分区内分别执行，在跨越分区边界时重新初始化

　　order by子句：按照指定字段进行排序，窗口函数将按照排序后的记录顺序进行编号。可以和partition by子句配合使用，也可以单独使用。

　　frame子句：当前分区的一个子集，用来定义子集的规则，通常用来作为滑动窗口使用。

　　MySql8.0版本下载：https://downloads.mysql.com/archives/installer/

　　查询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