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

mysql 5.7实现组内排序(连续xx天数)

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

我先说一下思路:要实现连续登录的判断,可以找一下他们之间的规律。这里我拿一个用户来说,如果这个用户在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结果也说不过去*

在这里插入图片描述


http://www.kler.cn/a/373328.html

相关文章:

  • 《文心一言插件设计与开发》赛题三等奖方案 | NoteTable
  • 分别用webpack和vite注册全局组件
  • 从零学习大模型(十)-----剪枝基本概念
  • 沈阳乐晟睿浩科技有限公司抖音小店新篇章
  • 微服务分布式事务
  • pyav保存视频
  • LeetCode Hot100 - 子串篇
  • 商场紧急预案管理:SpringBoot实现指南
  • 3. 教你用WebSocket构建一个实时聊天应用
  • Chromium 中chrome.fontSettings扩展接口定义c++
  • django中entity.save(using=)的使用
  • 不再输入单号查快递,批量查快递单号信息的新方法,智能排序快递时效并查找时效相同的单号,一站式物流查询解决方案
  • 微服务篇SpringCloud
  • Mysql报错注入之floor报错详解
  • AI学习指南自然语言处理篇-Transformer模型简介
  • 美团2025校招 广告算法工程师 面经
  • Linux基础 -- 文件同步之 rsync 命令的使用
  • golang 高阶函数
  • 各国家的MCC
  • Tomcat异常日志中文乱码怎么解决
  • ELK之路第四步——整合!打通任督二脉
  • 9种 Vuejs 常用事件修饰符与使用指南
  • 《神经网络助力战场车辆及部件损毁识别与评估》
  • 【Moonlight】Sunshine 安装
  • QT——TCP网络调试助手
  • 嵌入式C/C++语言相关知识——C++八股