Как можно удалить старые записи, оставив, как минимум, одну последнюю для каждого датчика?
В таблицу channel_snapshots пишутся отчёты состояния с разных датчиков (snapshot_time, FK channel_id). Чтобы держать размер таблицы контролируемым, старые записи свыше 12 часов нужно удалять, но так, чтобы не был удалён последний старый отчёт датчика, который по каким-то причинам не отправил отчёт. То есть нужно оставить 1 последний отчёт каждого датчика, от которого не был отправлен отчёт более 12 часов назад.
На данный момент же удаляются банально все старые записи свыше 12 часов и даже те, которые нужно оставить поштучно.
Вопрос: можно ли выполнить эту задачу одним запросом, учитывая данную логику? Если да, то как?
Для правильного вопроса надо знать половину ответа
DELETE `t1`
FROM `channel_snapshots` AS `t1`
LEFT JOIN (
SELECT MAX(`snapshot_time`) AS `time`, `channel_id`
FROM `channel_snapshots`
GROUP BY `channel_id`
) AS `t2` ON `t2`.`time` = `t1`.`snapshot_time` AND `t2`.`channel_id` = `t1`.`channel_id`
WHERE `t1`.`snapshot_time` < NOW() - INTERVAL 12 HOUR
AND `t2`.`channel_id` IS NULL
