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.rhkb.cn/news/460534.html

如若内容造成侵权/违法违规/事实不符,请联系长河编程网进行投诉反馈email:809451989@qq.com,一经查实,立即删除!

相关文章

★ Linux ★ 基础开发工具的使用(上)

Ciallo&#xff5e;(∠・ω< )⌒☆ ~ 今天&#xff0c;我将和大家一起学习 linux 基础开发工具的使用~ 目录 壹 Linux编辑器 - vim使用 1.1 vim的基本概念 1.2 vim正常模式命令集 1.2.1 插入模式 1.2.2 移动光标命令 1.2.3 编辑命令 1.3 vim末行模式命令集 贰 Lin…

solidworks学习6吊环-20241030

solidworks学习6吊环 图 1 使用到的命名&#xff1a;拉伸曲面&#xff0c;旋转曲面&#xff0c;镜像实体&#xff0c;剪裁曲面&#xff0c; 前视基准面绘制 图 2 绘制旋转轴 图 3 旋转曲面 图 4 上视基准面绘制&#xff0c;标准圆边尺寸的时候需要按住shift键标注&#x…

提示词高级阶段学习day4.1

第一步&#xff1a;你要有一个大模型帐号&#xff0c;至少已经熟悉和它们对话的方式。最强性能当属ChatGPT4&#xff0c;当然也推荐国产平替&#xff1a; Kimi.ai - 帮你看更大的世界 智谱清言 第二步&#xff1a;看 OpenAI 的官方文档&#xff1a; 目录&#xff1a;OpenAI …

开源趣味艺术画板Paint Board

什么是 Paint Board &#xff1f; Paint Board 是简洁易用的 Web 端创意画板。它集成了多种创意画笔和绘画功能&#xff0c;支持形状绘制、橡皮擦、自定义画板等操作&#xff0c;并可以将作品保存为图片。 软件功能&#xff1a; 不过非常可惜&#xff0c;老苏最期待的数据同步还…

建设NFS服务器并实现文件共享

关闭防火墙和s0 systemctl stop firewalld setenforce 0 安装NFS yum install nfs-utils -y 新建共享目录并设置权限 echo "hello" > /nfs/shared/test1 chmod -Rf 777 /nfs/shared/ 配置服务端的NFS配置文件 vim /etc/exports /nfs/shared *(ro) 启动…

软件测试学习笔记丨SeleniumPO模式

本文转自测试人社区&#xff0c;原文链接&#xff1a;https://ceshiren.com/t/topic/22525 本文为霍格沃兹测试开发学社的学习经历分享&#xff0c;写出来分享给大家&#xff0c;希望有志同道合的小伙伴可以一起交流技术&#xff0c;一起进步~ 说明&#xff1a;本篇博客基于sel…

python通过keyboard库实现模拟/监听键盘

keyboard介绍 如果我们想要通过快捷键&#xff0c;来调用某段代码&#xff0c;我们可以使用python的keyboard库&#xff0c;这个库可以用于发送&#xff0c;挂钩&#xff0c;以及模拟键盘事件等&#xff0c;并且同时支持多种操作系统&#xff08;但是需要注意的是&#xff0c;…

Spring Boot 创建项目详细介绍

上篇文章简单介绍了 Spring Boot&#xff08;Spring Boot 详细简介&#xff01;&#xff09;&#xff0c;还没看到的读者&#xff0c;建议看看。 下面&#xff0c;介绍一下如何创建一个 Spring Boot 项目&#xff0c;以及自动生成的目录文件作用。 Maven 构建项目 访问 http…

windows下安装python库wordCloud报错

换电脑安装wordcloud半天安装失败&#xff0c;记录一下遇到的坑&#xff0c;也给大家节省点时间。 方法1&#xff1a; 错误呢就是下面这个&#xff0c;说没c编译器&#xff0c;要不就去他给的地址上安装一下&#xff0c;我安装了一下好像没什么用&#xff0c;也没太敢勾选&am…

未来之维,陈欣的智能CAD

第一章 新世界的曙光 在不远的未来&#xff0c;人类科技取得了前所未有的进步。人工智能不仅渗透到了生活的每一个角落&#xff0c;而且开始在科学研究、艺术创作乃至人类情感交流中扮演重要角色。在这个充满无限可能的时代&#xff0c;有一位年轻的女工程师——陈欣&#xff…

目前最新最好用 NET 混淆工具 .NET Reactor V6.9.8

目前最新最好用 NET 混淆工具 .NET Reactor V6.9.8 1、.NET Reactor V6.9.8 功能简介2、官方下载 1、.NET Reactor V6.9.8 功能简介 业界领先的源代码保护 .NET Reactor通过多种方法来防止反编译&#xff0c;这些方法会将 .NET 程序集转换为任何现有工具都无法反编译的进程。…

2024 Rust现代实用教程:1.2编译器与包管理工具以及开发环境搭建

文章目录 一、Rust的编译器rustc二、开发环境搭建三、Rust的包管理工具Cargo四、项目结构1.Cargo.toml文件2.创建一个可执行文件项目3.创建一个库项目 参考 一、Rust的编译器rustc 查看版本 rustc-version编译生成二进制文件 rustc -o output filename filename.rs编译生成库…

macOS Sonoma 14.7.1 (23H222) Boot ISO 原版可引导镜像下载

macOS Sonoma 14.7.1 (23H222) Boot ISO 原版可引导镜像下载 2024 年 10 月 28 日&#xff0c;Apple 智能今日登陆 iPhone、iPad 和 Mac。用户现可借助 Apple 智能优化写作&#xff0c;为通知、邮件和消息生成摘要&#xff0c;体验交互更自然、功能更丰富的 Siri&#xff0c;使…

Kafka相关API开发

(一)引入依赖 用API直接去操作kafka(读写数据)在实际开发中用的并不多&#xff0c;学习它主要还是为了加深对Kafka功能的理解。kafka的读写操作&#xff0c;实际开发中&#xff0c;是通过各类更上层的组件去实现。而这些组件在读写kafka数据时&#xff0c;用的当然是kafka的jav…

Backtrader 数据篇 02

Backtrader 数据篇 本系列是使用Backtrader在量化领域的学习与实践&#xff0c;着重介绍Backtrader的使用。Backtrader 中几个核心组件&#xff1a; Cerebro&#xff1a;BackTrader的基石&#xff0c;所有的操作都是基于Cerebro的。Feed&#xff1a;将运行策略所需的基础数据…

Leetcode224 -- 基本计算器及其拓展

题目分析&#xff1a; 其实这个计算器的实现并不难&#xff0c;因为除了括号就剩下加减法嘛&#xff0c;括号肯定比加减法先执行&#xff0c;但是加减法是同级的&#xff0c;只是会改变数字的正负号而已&#xff0c;所以实现的逻辑并不是很难&#xff0c;我们只需要一个栈&…

【jvm】为什么Xms和Xmx的值通常设置为相同的?

目录 1. 说明2. 避免性能开销3. 提升稳定性4. 简化配置5. 优化垃圾收集6. 获取参数6.1 代码示例6.2 结果示例 1. 说明 1.-Xms 和 -Xmx 参数分别用于设置堆内存的初始大小&#xff08;最小值&#xff09;和最大大小。2.在开发环境中&#xff0c;开发人员可能希望快速启动应用程…

瑞芯微RK3566/RK3568 Android11下该如何默认屏蔽导航栏/状态栏?看这篇文章就懂了

本文介绍瑞芯微RK3566/RK3568在Android11系统下&#xff0c;默认屏蔽导航栏/状态栏方法&#xff0c;使用触觉智能Purple Pi OH鸿蒙开发板演示&#xff0c;搭载了瑞芯微RK3566芯片&#xff0c;类树莓派设计&#xff0c;Laval官方社区主荐&#xff0c;已适配全新OpenHarmony5.0 R…

使用AIM对SAP PO核心指标的自动化巡检监控

一、背景 由于SAP PO系统维护成本较高&#xff0c;各类型异常报错等都需要人员进行时刻监控和响应&#xff0c;遂由AIM平台进行自动化巡检SAP PO的各指标&#xff0c;然后告警通知用户&#xff0c;节省维护成本和提高工作效率 二、核心指标监控 SAP PO失败消息 适用于S…

openpnp - 手工修改配置文件(元件高度,size,吸嘴)

文章目录 openpnp - 手工修改配置文件(元件高度,size,吸嘴)概述笔记parts.xmlpackages.xml 手工将已经存在的NT1,NT2拷贝出来改名备注END openpnp - 手工修改配置文件(元件高度,size,吸嘴) 概述 载入新板子贴片准备时&#xff0c;除了引入Named CSV文件&#xff0c;还要在ope…