有一个需求,需要保留每个电站每一天发电数据的最大值记录,其余删除。
表数据大概长这样:
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