当前位置：首页 > article >正文

mysql 5.7实现组内排序（连续xx天数）

article 2024/10/31 18:29:21

需求：查询出连续登录的用户及其连续登录的天数

我先说一下思路：要实现连续登录的判断，可以找一下他们之间的规律。这里我拿一个用户来说，如果这个用户在1、2、3号都有登录记录，可以对这个用户的数据按照时间排序，然后按照顺序加一个索引，如下图
在这里插入图片描述
到这可能有一些人已经看规律来了。规律如下图

所以当日期-序号得到的值相同说明是连续的，反之则是不连续的。

背景

最近项目上有连续预警次数的统计这种需求，用的是mysql5.7版本，在mysql8.0版本中实现分组排序可以用row_number() over(partition by 分组字段 order by 排序字段)就可以了，但是在mysql5.7是没有该方法的，只能自己实现了。

方案

首先我们先建一个表，这个表里只有几个所用的字段，主要就是日期和得分。

CREATE TABLE `a_test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '标识',
  `stat_cycle` varchar(255) DEFAULT NULL COMMENT '日期',
  `user_no` varchar(255) DEFAULT NULL COMMENT '用户编号',
  `score` varchar(255) DEFAULT NULL COMMENT '得分',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='测试表';

然后随便插入一些数据

INSERT INTO `a_test` VALUES ('1', '20240901', '1', '80');
INSERT INTO `a_test` VALUES ('11', '20240901', '2', '75');
INSERT INTO `a_test` VALUES ('21', '20240901', '3', '90');
INSERT INTO `a_test` VALUES ('31', '20240902', '1', '98');
INSERT INTO `a_test` VALUES ('41', '20240902', '2', '92');
INSERT INTO `a_test` VALUES ('51', '20240903', '1', '88');
INSERT INTO `a_test` VALUES ('61', '20240910', '1', '96');
INSERT INTO `a_test` VALUES ('71', '20240910', '2', '92');
INSERT INTO `a_test` VALUES ('81', '20240911', '1', '88');
INSERT INTO `a_test` VALUES ('91', '20240911', '2', '100');

查询看一下数据

select * from a_test

在这里插入图片描述
在mysql5.7中加索引可以通过变量来控制

select 
@row_number :=IF(@user_no = a.user_no, @row_number + 1, 1 ) AS rn,
@user_no := a.user_no AS user_no,
a.score,
a.stat_cycle
from a_test a,
(select @row_number := 0) b
where date_format(a.stat_cycle,'%Y%m') = '202409'
ORDER BY a.user_no,a.stat_cycle asc

在这个sql里，(select @row_number := 0) b就相当于创建了一个变量row_number，默认值为0，而在上边查询条件里通过IF判断对变量row_number重新赋值：如果变量user_no与记录的user_no相等则row_number +1，反之重新赋值为1
@user_no同理

结果为：
在这里插入图片描述

注意

第一点

下边这两个变量顺序不能反，不然的话，rn就不是组内进行排序了

@row_number :=IF(@user_no = a.user_no, @row_number + 1, 1 ) AS rn,
@user_no := a.user_no AS user_no

结果是这样的（注意看rn列）
在这里插入图片描述

第二点

如果是同统计连续登录这个问题可忽略。

第二个问题也我在项目发布测试才发现的：如果数据不是顺序插入（我项目是有个导入的功能，客户填写的时候可能第一行日期是25号，第二行是24号）。

首先，我们先新建一个b_test表

CREATE TABLE `b_test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '唯一标识',
  `user_no` varchar(32) DEFAULT NULL COMMENT '编号',
  `user_name` varchar(255) DEFAULT NULL COMMENT '名称',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

插入b_test表数据

INSERT INTO `b_test` VALUES ('1', '1', '张三');
INSERT INTO `b_test` VALUES ('2', '2', '李四');
INSERT INTO `b_test` VALUES ('11', '3', '王五');

查看下b_test表数据

SELECT * from b_test

结果如下
在这里插入图片描述

修改a_test表数据：模拟用户导入，先插入了2号的数据，再插入1号的数据，这里注意下id（自增类型主键），20240902是主键为1，20240901主键为31。然后我还关联了一个基本信息表b_test
在这里插入图片描述

这是原始sql

select 
@row_number :=IF(@user_no = a.user_no, @row_number + 1, 1 ) AS rn,
@user_no := a.user_no AS user_no,
a.score,
a.stat_cycle
-- ,b.user_name
from a_test a 
-- left join b_test b on a.user_no=b.user_no
,
(select @row_number := 0) b
where date_format(a.stat_cycle,'%Y%m') = '202409'
ORDER BY a.user_no,a.stat_cycle asc

结果为
在这里插入图片描述
看user_no为1的排序，20240901序号为1，20240902序号为2，没有问题
接着关联查出user_name。

select 
@row_number :=IF(@user_no = a.user_no, @row_number + 1, 1 ) AS rn,
@user_no := a.user_no AS user_no,
a.score,
a.stat_cycle
,b.user_name
from a_test a 
 left join b_test b on a.user_no=b.user_no
,
(select @row_number := 0) b
where date_format(a.stat_cycle,'%Y%m') = '202409'
ORDER BY a.user_no,a.stat_cycle asc

结果为：
在这里插入图片描述
数据就不一样了：20240901排序序号变成了2，20240902序号为1

针对上述sql，我有两个问题，希望有大佬帮忙解答
*
（1）按照sql执行顺序来说，先select，在order by，所以是关联，然后赋值序号，然后再根据日期排序。在执行到select这一步时，表里的数据是20240902在前，20240901在后，所以排上序后，20240902对应着1，20240901对应着2，排完序后会出现这个结果。但是咱们不关联b_test的表的时候，也应该是这个sql顺序，但是查询结果却是20240901对应着1，20240902对应着2，和刚才说的逻辑就冲突了
（2）先看不关联的sql的话，像是先执行的排序，然后赋值序号，先不说违背了sql执行顺序，关联的b_test表的sql结果也说不过去*

在这里插入图片描述