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

小牛同学_

于 2024-09-03 17:17:15 发布

阅读量226

点赞数 5

文章标签： sql 数据库

本文链接：https://blog.csdn.net/ChickenBro_/article/details/141864781

版权

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

表数据大概长这样：

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