MYSQL:删除指定时间范围内每个电站每天发电数据除最大值以外的记录

本文主要是介绍MYSQL:删除指定时间范围内每个电站每天发电数据除最大值以外的记录,希望对大家解决编程问题提供一定的参考价值,需要的开发者们随着小编来一起学习吧!

有一个需求,需要保留每个电站每一天发电数据的最大值记录,其余删除。

表数据大概长这样:

MYSQL 5.7写法:(因为不支持ROW_NUMBER()函数,采用自定义的变量来代替

首次清理一年内数据:INTERVAL 365 DAY
清理前一日数据:INTERVAL 1 DAY-----------------   DELETE A   
FROM power_app_data_log A  
WHERE NOT EXISTS (  SELECT 1  FROM (  SELECT  t.id,  IF(  @prev_brand_id = t.brand_id AND @prev_time = DATE(t.TIME),  @num := @num + 1,  @num := 1  ) AS row_no,  @prev_brand_id := t.brand_id,  @prev_time := DATE(t.TIME)  FROM  power_app_data_log t,  (SELECT @num := 0, @prev_brand_id := NULL, @prev_time := NULL) AS vars  WHERE  DATE(t.TIME) BETWEEN DATE_SUB(CURDATE(), INTERVAL 365 DAY) AND CURDATE()  ORDER BY  t.brand_id,  DATE(t.TIME),  t.app_data DESC  ) AS subquery  WHERE subquery.row_no = 1 AND A.id = subquery.id  
)
AND DATE(A.TIME) BETWEEN DATE_SUB(CURDATE(), INTERVAL 365 DAY) AND CURDATE();
这段SQL代码的目的是删除power_app_data_log表中一些特定的记录。下面是对这段SQL的详细解释:外层查询:
DELETE A FROM power_app_data_log A:这表示将从power_app_data_log表中删除记录,别名为A。WHERE子句:
WHERE NOT EXISTS (...):这表示将删除那些在内层查询中不存在的记录。内层查询:
这是一个子查询,用于找出每个brand_id和日期组合中的最新记录(基于app_data的降序排序)。
使用了变量@num、@prev_brand_id和@prev_time来跟踪每个brand_id和日期组合中的记录序号。
IF语句用于判断当前记录的brand_id和日期是否与前一条记录相同,如果相同则序号加1,否则序号重置为1。
ORDER BY t.brand_id, DATE(t.TIME), t.app_data DESC:这表示按照brand_id、日期和app_data的降序进行排序。子查询的WHERE子句:
WHERE subquery.row_no = 1 AND A.id = subquery.id:这表示只选择每个brand_id和日期组合中的第一条记录(即最新记录),并且这条记录的id必须与外层查询中的id相匹配。外层查询的额外条件:
AND DATE(A.TIME) BETWEEN DATE_SUB(CURDATE(), INTERVAL 365 DAY) AND CURDATE();:这表示只考虑在过去365天内的记录。综上所述,这段SQL代码的作用是删除power_app_data_log表中在过去365天内,但不是每个brand_id和日期组合中的最新记录的所有记录。换句话说,它保留了每个brand_id和日期组合中的最新记录,删除了其余的记录。

MYSQL 8.0写法:

DELETE A  
FROM power_app_data_log A  
WHERE NOT EXISTS (  SELECT 1  FROM (  SELECT  t.id,  ROW_NUMBER() OVER (  PARTITION BY t.brand_id, DATE(t.TIME)  ORDER BY t.app_data DESC  ) AS row_no  FROM  power_app_data_log t  WHERE  DATE(t.TIME) BETWEEN DATE_SUB(CURDATE(), INTERVAL 365 DAY) AND CURDATE()  ) AS subquery  WHERE subquery.row_no = 1 AND A.id = subquery.id  
)  
AND DATE(A.TIME) BETWEEN DATE_SUB(CURDATE(), INTERVAL 365 DAY) AND CURDATE();
这段SQL代码的目的是删除power_app_data_log表中一些特定的记录,具体解释如下:外层查询:
DELETE A FROM power_app_data_log A:表示将从power_app_data_log表中删除记录,别名为A。WHERE子句:
WHERE NOT EXISTS (...):表示将删除那些在内层查询中不存在的记录。内层查询:
这是一个子查询,用于找出每个brand_id和日期组合中的最新记录(基于app_data的降序排序)。
使用了ROW_NUMBER()窗口函数来为每个brand_id和日期组合中的记录分配一个序号,序号是基于app_data的降序排序的。
PARTITION BY t.brand_id, DATE(t.TIME):表示窗口函数将按照brand_id和日期进行分区。
ORDER BY t.app_data DESC:表示在每个分区内,记录将按照app_data的降序进行排序。子查询的WHERE子句:
WHERE subquery.row_no = 1 AND A.id = subquery.id:表示只选择每个brand_id和日期组合中的第一条记录(即最新记录),并且这条记录的id必须与外层查询中的id相匹配。外层查询的额外条件:
AND DATE(A.TIME) BETWEEN DATE_SUB(CURDATE(), INTERVAL 365 DAY) AND CURDATE();:表示只考虑在过去365天内的记录。综上所述,这段SQL代码的作用是删除power_app_data_log表中在过去365天内,但不是每个brand_id和日期组合中的最新记录的所有记录。换句话说,它保留了每个brand_id和日期组合中的最新记录,删除了其余的记录。这是通过比较每条记录的id是否存在于一个只包含每个组合中最新记录的子查询中来实现的。如果不存在,则删除该记录。

   

扩展:

MySQL之group by与max()一起使用的坑

MYSQL之not in优化方法:left join

mysql 优化 not in优化成not exist

 

这篇关于MYSQL:删除指定时间范围内每个电站每天发电数据除最大值以外的记录的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1137071

相关文章

浅谈mysql的not exists走不走索引

《浅谈mysql的notexists走不走索引》在MySQL中,​NOTEXISTS子句是否使用索引取决于子查询中关联字段是否建立了合适的索引,下面就来介绍一下mysql的notexists走不走索... 在mysql中,​NOT EXISTS子句是否使用索引取决于子查询中关联字段是否建立了合适的索引。以下

Java通过驱动包(jar包)连接MySQL数据库的步骤总结及验证方式

《Java通过驱动包(jar包)连接MySQL数据库的步骤总结及验证方式》本文详细介绍如何使用Java通过JDBC连接MySQL数据库,包括下载驱动、配置Eclipse环境、检测数据库连接等关键步骤,... 目录一、下载驱动包二、放jar包三、检测数据库连接JavaJava 如何使用 JDBC 连接 mys

SQL中如何添加数据(常见方法及示例)

《SQL中如何添加数据(常见方法及示例)》SQL全称为StructuredQueryLanguage,是一种用于管理关系数据库的标准编程语言,下面给大家介绍SQL中如何添加数据,感兴趣的朋友一起看看吧... 目录在mysql中,有多种方法可以添加数据。以下是一些常见的方法及其示例。1. 使用INSERT I

Qt使用QSqlDatabase连接MySQL实现增删改查功能

《Qt使用QSqlDatabase连接MySQL实现增删改查功能》这篇文章主要为大家详细介绍了Qt如何使用QSqlDatabase连接MySQL实现增删改查功能,文中的示例代码讲解详细,感兴趣的小伙伴... 目录一、创建数据表二、连接mysql数据库三、封装成一个完整的轻量级 ORM 风格类3.1 表结构

Python使用vllm处理多模态数据的预处理技巧

《Python使用vllm处理多模态数据的预处理技巧》本文深入探讨了在Python环境下使用vLLM处理多模态数据的预处理技巧,我们将从基础概念出发,详细讲解文本、图像、音频等多模态数据的预处理方法,... 目录1. 背景介绍1.1 目的和范围1.2 预期读者1.3 文档结构概述1.4 术语表1.4.1 核

go中的时间处理过程

《go中的时间处理过程》:本文主要介绍go中的时间处理过程,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,望不吝赐教... 目录1 获取当前时间2 获取当前时间戳3 获取当前时间的字符串格式4 相互转化4.1 时间戳转时间字符串 (int64 > string)4.2 时间字符串转时间

MySQL 中的 CAST 函数详解及常见用法

《MySQL中的CAST函数详解及常见用法》CAST函数是MySQL中用于数据类型转换的重要函数,它允许你将一个值从一种数据类型转换为另一种数据类型,本文给大家介绍MySQL中的CAST... 目录mysql 中的 CAST 函数详解一、基本语法二、支持的数据类型三、常见用法示例1. 字符串转数字2. 数字

Mysql实现范围分区表(新增、删除、重组、查看)

《Mysql实现范围分区表(新增、删除、重组、查看)》MySQL分区表的四种类型(范围、哈希、列表、键值),主要介绍了范围分区的创建、查询、添加、删除及重组织操作,具有一定的参考价值,感兴趣的可以了解... 目录一、mysql分区表分类二、范围分区(Range Partitioning1、新建分区表:2、分

MySQL 定时新增分区的实现示例

《MySQL定时新增分区的实现示例》本文主要介绍了通过存储过程和定时任务实现MySQL分区的自动创建,解决大数据量下手动维护的繁琐问题,具有一定的参考价值,感兴趣的可以了解一下... mysql创建好分区之后,有时候会需要自动创建分区。比如,一些表数据量非常大,有些数据是热点数据,按照日期分区MululbU

SQL Server配置管理器无法打开的四种解决方法

《SQLServer配置管理器无法打开的四种解决方法》本文总结了SQLServer配置管理器无法打开的四种解决方法,文中通过图文示例介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的... 目录方法一:桌面图标进入方法二:运行窗口进入检查版本号对照表php方法三:查找文件路径方法四:检查 S