Как можно удалить старые записи, оставив, как минимум, одну последнюю для каждого датчика?

Question

[d'Ivan]

В таблицу channel_snapshots пишутся отчёты состояния с разных датчиков (snapshot_time, FK channel_id). Чтобы держать размер таблицы контролируемым, старые записи свыше 12 часов нужно удалять, но так, чтобы не был удалён последний старый отчёт датчика, который по каким-то причинам не отправил отчёт. То есть нужно оставить 1 последний отчёт каждого датчика, от которого не был отправлен отчёт более 12 часов назад.

На данный момент же удаляются банально все старые записи свыше 12 часов и даже те, которые нужно оставить поштучно.
Вопрос: можно ли выполнить эту задачу одним запросом, учитывая данную логику? Если да, то как?

Answer 1

[Rsa97]

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

Comments

[d'Ivan]

Спасибо!

Answer 2

[Андрей]

В диалекте Oracle SQL:

DELETE
FROM
  ALL_SNAPSHOTS
WHERE
  SNAPSHOT_TIME                       <=SYSDATE-12/24
  AND (SNAPSHOT_TIME, ID_CHANNEL) NOT IN
  (
    SELECT MAX(SNAPSHOT_TIME), ID_CHANNEL FROM ALL_SNAPSHOTS GROUP BY ID_CHANNEL
  );

Comments

[Сергей Горностаев]

Зачем?

[Андрей]

Сергей Горностаев: Сергей, что "зачем"?

[Сергей Горностаев]

Андрей: зачем в диалекте Oracle, если у автора MySQL?

[d'Ivan]

Андрей: быстрее LEFT JOIN или NOT IN, как у Rsa97?

[d'Ivan]

я имел ввиду наоборот

[Rsa97]

Роман Мирр: EXPLAIN'ом надо смотреть.

Answer 3

[Александр]

вначале надо получить ид верхних записей. группируя по типу датчика, потом удалить все кроме этих

DELETE `tab` WHERE `id` NOT IN (SELECT max(id) FROM `tab` GROUP BY `channel`)