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：删除指定时间范围内每个电站每天发电数据除最大值以外的记录的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---