SQL: сравнение целой строки данных с числом

Question

Я в основном записываю данные в базу данных SQL каждую минуту, но я хочу иметь возможность проверить, не пропущены ли какие-либо записи (т. Е. Посмотреть, есть ли различия больше минуты).

Возьмите это к примеру:

5 4 2 1

Я хотел бы отобразить строки, где существуют 4 и 2, чтобы я знал, что между ними что-то не хватает. Фактические данные в следующем формате.

date_time

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" на соответствующие даты начала и окончания (но используйте один и тот же формат "ГГГГ-ММ-ДД ЧЧ: ММ: СС") -

   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.