SQL:将整行数据与数字进行比较

Question

我基本上每分钟都会将数据记录到 SQL 数据库中，但我希望能够检查是否有任何条目丢失(即:查看是否有超过一分钟的差异)。

以此为例:

54个2个1

我想显示 4 和 2 所在的行，以便我知道它们之间缺少某些内容。实际数据格式如下。

日期_时间

2010-09-12 10:45:00

2010-09-12 10:44:00

等..

我发现了如何使用 EXTRACT(MINUTE FROM date_time) 提取分钟值，但是我完全不知道如何对整个表进行比较。

任何帮助都会很棒。

谢谢

Answer 1

MySQL 没有递归功能，因此您只能使用 NUMBERS 表技巧 -

1. 创建一个只包含递增数字的表 - 使用 auto_increment 很容易做到:

   DROP TABLE IF EXISTS `example`.`numbers`;
   CREATE TABLE  `example`.`numbers` (
     `id` int(10) unsigned NOT NULL auto_increment,
      PRIMARY KEY  (`id`)
   ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
   

2. 使用以下方法填充表格:

   INSERT INTO NUMBERS
     (id)
   VALUES
     (NULL)
   

   ...根据您的需要获取尽可能多的值。

3. 使用 DATE_ADD构建日期列表，根据 NUMBERS.id 值增加天数。将“2010-06-06”和“2010-06-14”替换为您各自的开始日期和结束日期(但使用相同的格式，“YYYY-MM-DD HH:MM:SS”)-

   SELECT x.*
     FROM (SELECT DATE_ADD('2010-06-06', INTERVAL n.id - 1 MINUTE)
             FROM numbers n
            WHERE DATE_ADD('2010-06-06', INTERVAL n.id -1 MINUTE) <= '2010-06-14' ) x
   

4. 根据时间部分左连接到您的数据表:

      SELECT x.ts AS timestamp,
             COALESCE(yt.col, 'missing') AS cnt
        FROM (SELECT DATE_FORMAT(DATE_ADD('2010-06-06', INTERVAL n.id - 1 MINUTE), '%Y-%m-%d %k:%i') AS ts
                FROM numbers n
               WHERE DATE_ADD('2010-06-06', INTERVAL n.id - 1 MINUTE) <= '2010-06-14') x
   LEFT JOIN YOUR_TABLE yt ON DATE_FORMAT(yt.date, '%Y-%m-%d %k:%i') = x.ts
   

有关 DATE_FORMAT function 的更多信息，请参阅文档.