mysql时间戳查询，选择给定分钟除第一条记录之外的所有记录

Question

我有一个表，用于存储有关我的 IP 摄像机拍摄的快照的数据。一般来说，相机每分钟拍摄多张快照，尽管我的一台相机配置为每分钟仅拍摄一张快照。

我从表(和磁盘)中清除项目，但根据以下规则保留:

1. 前 7 天，所有图像都会保留
2. 任何超过 7 天的内容，只需保留当天每小时的第一个快照
3. 任何超过 4 周的时间，只需保留当天 06、12 和 18 小时的第一个快照即可
4. 任何超过 3 个月的内容，只需保留当天第 12 个小时的第一个快照即可。

以下是我当前的查询，它工作正常，除了它保留在任何小时的第一分钟内拍摄的所有快照。

SELECT camera_id,
       timestamp,
       frame,
       filename
FROM snapshot_frame
WHERE ((timestamp < subdate(now(), INTERVAL 7 DAY)
        AND minute(timestamp) != 0)
       OR (timestamp < subdate(now(), INTERVAL 4 WEEK)
           AND (hour(timestamp) NOT IN (6,
                                        12,
                                        18)
                OR minute(timestamp) != 0))
       OR (timestamp < subdate(now(), INTERVAL 3 MONTH)
           AND (hour(timestamp) != 12
                OR minute(timestamp) != 0)))

如何根据上述规则保留超过 7 天的时间戳的每分钟第一个快照？

如果有帮助的话，表/索引结构:

mysql> describe snapshot_frame;
+-----------+--------------+------+-----+---------+-------+
| Field     | Type         | Null | Key | Default | Extra |
+-----------+--------------+------+-----+---------+-------+
| camera_id | int(11)      | NO   |     | NULL    |       |
| timestamp | datetime     | NO   | MUL | NULL    |       |
| frame     | int(11)      | YES  |     | NULL    |       |
| filename  | varchar(100) | YES  | UNI | NULL    |       |
+-----------+--------------+------+-----+---------+-------+
4 rows in set (0.04 sec)

mysql> show index from snapshot_frame;
+----------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table          | Non_unique | Key_name        | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| snapshot_frame |          0 | filename        |            1 | filename    | A         |     3052545 |     NULL | NULL   | YES  | BTREE      |         |               |
| snapshot_frame |          1 | idx_time_camera |            1 | timestamp   | A         |     3052545 |     NULL | NULL   |      | BTREE      |         |               |
| snapshot_frame |          1 | idx_time_camera |            2 | camera_id   | A         |     3052545 |     NULL | NULL   |      | BTREE      |         |               |
+----------------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.42 sec)

mysql> select count(*) from snapshot_frame;
+----------+
| count(*) |
+----------+
|  3030214 |
+----------+
1 row in set (18.47 sec)

更新:所以我设法创建一个查询，根据我的规则提供我想要保留的所有快照:

SELECT camera_id,
       TIMESTAMP,
       frame,
       filename
FROM snapshot_frame
WHERE TIMESTAMP >= subdate(now(), INTERVAL 7 DAY)
UNION
  (SELECT camera_id,
          TIMESTAMP,
          frame,
          filename
   FROM snapshot_frame
   WHERE TIMESTAMP < subdate(now(), INTERVAL 7 DAY)
     AND TIMESTAMP >= subdate(now(), INTERVAL 4 WEEK)
     AND minute(TIMESTAMP) = 0
   GROUP BY camera_id,
            year(TIMESTAMP),
            month(TIMESTAMP),
            date(TIMESTAMP),
            hour(TIMESTAMP),
            minute(TIMESTAMP))
UNION
  (SELECT camera_id,
          TIMESTAMP,
          frame,
          filename
   FROM snapshot_frame
   WHERE TIMESTAMP < subdate(now(), INTERVAL 4 WEEK)
     AND TIMESTAMP >= subdate(now(), INTERVAL 3 MONTH)
     AND hour(TIMESTAMP) IN (6,
                             12,
                             18)
     AND minute(TIMESTAMP) = 0
   GROUP BY camera_id,
            year(TIMESTAMP),
            month(TIMESTAMP),
            date(TIMESTAMP),
            hour(TIMESTAMP),
            minute(TIMESTAMP))
UNION
  (SELECT camera_id,
          TIMESTAMP,
          frame,
          filename
   FROM snapshot_frame
   WHERE TIMESTAMP < subdate(now(), INTERVAL 3 MONTH)
     AND hour(TIMESTAMP) = 12
     AND minute(TIMESTAMP) = 0
   GROUP BY camera_id,
            year(TIMESTAMP),
            month(TIMESTAMP),
            date(TIMESTAMP),
            hour(TIMESTAMP),
            minute(TIMESTAMP))

我现在只是想弄清楚如何扭转这一情况，因此我返回一个结果集，其中包含 snapshot_frame 中不在上述查询中的所有行。

有什么指点吗？

Answer 1

我现在使用的解决方案是用我希望保留的行填充临时表:

CREATE
TEMPORARY TABLE IF NOT EXISTS retain_frames (INDEX idx_time_camera (timestamp, camera_id))AS
SELECT camera_id,
       timestamp,
       frame,
       filename
FROM (
        (SELECT camera_id,
                timestamp,
                frame,
                filename
         FROM snapshot_frame a
         WHERE timestamp >= subdate(now(), INTERVAL 7 DAY))
      UNION
        (SELECT camera_id,
                timestamp,
                frame,
                filename
         FROM snapshot_frame b
         WHERE timestamp < subdate(now(), INTERVAL 7 DAY)
           AND timestamp >= subdate(now(), INTERVAL 4 WEEK)
           AND minute(timestamp) = 0
         GROUP BY camera_id,
                  date(timestamp),
                  hour(timestamp),
                  minute(timestamp))
      UNION
        (SELECT camera_id,
                timestamp,
                frame,
                filename
         FROM snapshot_frame c
         WHERE timestamp < subdate(now(), INTERVAL 4 WEEK)
           AND timestamp >= subdate(now(), INTERVAL 3 MONTH)
           AND hour(timestamp) IN (6,
                                   12,
                                   18)
           AND minute(timestamp) = 0
         GROUP BY camera_id,
                  date(timestamp),
                  hour(timestamp),
                  minute(timestamp))
      UNION
        (SELECT camera_id,
                timestamp,
                frame,
                filename
         FROM snapshot_frame d
         WHERE timestamp < subdate(now(), INTERVAL 3 MONTH)
           AND hour(timestamp) = 12
           AND minute(timestamp) = 0
         GROUP BY camera_id,
                  date(timestamp),
                  hour(timestamp),
                  minute(timestamp))) e

然后使用以下查询选择过时的快照:

SELECT camera_id,
       timestamp,
       frame,
       filename
FROM snapshot_frame a
WHERE NOT EXISTS
    (SELECT camera_id,
            timestamp,
            frame,
            filename
     FROM retain_frames b
     WHERE a.camera_id = b.camera_id
       AND a.timestamp = b.timestamp
       AND a.frame = b.frame)

唯一的问题是临时表的创建需要大约 2 分钟，并且似乎会锁定数据库，从而导致偶发的操作错误:(1205，'超出锁定等待超时；尝试重新启动事务')被尝试插入同一个表的另一个线程抛出到我的 python 代码中。