Метод поиска пробелов в данных временных рядов в MySQL?

Для начала давайте суммируем количество записей по часам в вашей таблице.

SELECT CAST(DATE_FORMAT(entry_time,'%Y-%m-%d %k:00:00') AS DATETIME) hour,
       COUNT(*) samplecount
  FROM table
 GROUP BY CAST(DATE_FORMAT(entry_time,'%Y-%m-%d %k:00:00') AS DATETIME)

Теперь, если вы что-то регистрируете каждые шесть минут (десять раз в час), все ваши значения SampleCount должны быть десятью. Это выражение: CAST(DATE_FORMAT(entry_time,'%Y-%m-%d %k:00:00') AS DATETIME) выглядит запутанным, но оно просто усекает ваши временные метки до часа, в котором они встречаются, путем обнуления минут и секунд.

Это достаточно эффективно и поможет вам начать. Очень эффективно, если вы можете поместить индекс в столбец entry_time и ограничить свой запрос, скажем, вчерашними образцами, как показано здесь.

SELECT CAST(DATE_FORMAT(entry_time,'%Y-%m-%d %k:00:00') AS DATETIME) hour,
       COUNT(*) samplecount
  FROM table
 WHERE entry_time >= CURRENT_DATE - INTERVAL 1 DAY
   AND entry_time < CURRENT_DATE
 GROUP BY CAST(DATE_FORMAT(entry_time,'%Y-%m-%d %k:00:00') AS DATETIME)

Но это не очень хорошо для обнаружения целых часов, которые проходят с отсутствующими образцами. Он также немного чувствителен к джиттеру при сэмплировании. То есть, если ваша первая за час выборка иногда на полсекунды раньше (10:59:30), а иногда на полсекунды позже (11:00:30), ваши почасовые итоговые подсчеты будут отключены. Таким образом, эта сводка по часам (или по дням, по минутам и т. д.) не является пуленепробиваемой.

Вам нужен запрос на самосоединение, чтобы получить все правильно; это немного больше похоже на комок шерсти и не так эффективно.

Давайте начнем с создания себе такой виртуальной таблицы (подзапроса) с пронумерованными примерами. (Это проблема MySQL; некоторые другие дорогие СУБД делают это проще. Неважно.)

  SELECT @sample:=@sample+1 AS entry_num, c.entry_time, c.value
    FROM (
        SELECT entry_time, value
      FROM table
         ORDER BY entry_time
    ) C,
    (SELECT @sample:=0) s

Эта маленькая виртуальная таблица дает entry_num, entry_time, значение.

Следующим шагом мы присоединяем его к себе.

SELECT one.entry_num, one.entry_time, one.value, 
       TIMEDIFF(two.value, one.value) interval
  FROM (
     /* virtual table */
  ) ONE
  JOIN (
     /* same virtual table */
  ) TWO ON (TWO.entry_num - 1 = ONE.entry_num)

Это выравнивает две следующие друг за другом таблицы со смещением одной записью, регулируемой предложением ON в JOIN.

Наконец, мы выбираем значения из этой таблицы с interval большим, чем ваш порог, и есть времена выборок прямо перед отсутствующими.

Общий запрос на самосоединение таков. Я говорил тебе, что это комок шерсти.

SELECT one.entry_num, one.entry_time, one.value, 
       TIMEDIFF(two.value, one.value) interval
  FROM (
    SELECT @sample:=@sample+1 AS entry_num, c.entry_time, c.value
      FROM (
          SELECT entry_time, value
            FROM table
           ORDER BY entry_time
      ) C,
      (SELECT @sample:=0) s
  ) ONE
  JOIN (
    SELECT @sample2:=@sample2+1 AS entry_num, c.entry_time, c.value
      FROM (
          SELECT entry_time, value
            FROM table
           ORDER BY entry_time
      ) C,
      (SELECT @sample2:=0) s
  ) TWO ON (TWO.entry_num - 1 = ONE.entry_num)

Если вам нужно сделать это в производстве на большой таблице, вы можете сделать это для подмножества ваших данных. Например, вы можете делать это каждый день для образцов за предыдущие два дня. Это было бы довольно эффективно, а также гарантировало бы, что вы не пропустите ни одного пропущенного образца прямо в полночь. Для этого ваши маленькие виртуальные таблицы с нумерацией строк будут выглядеть так.

  SELECT @sample:=@sample+1 AS entry_num, c.entry_time, c.value
    FROM (
        SELECT entry_time, value
      FROM table
         ORDER BY entry_time
         WHERE entry_time >= CURRENT_DATE - INTERVAL 2 DAY
           AND entry_time < CURRENT_DATE /*yesterday but not today*/
    ) C,
    (SELECT @sample:=0) s

Очень эффективный способ сделать это — использовать хранимую процедуру с использованием курсоров. Я думаю, что это проще и эффективнее, чем другие ответы.

Эта процедура создает курсор и перебирает его по проверяемым записям даты и времени. Если когда-либо будет пробел больше, чем вы укажете, он запишет начало и конец пробела в таблицу.

    CREATE PROCEDURE findgaps()
    BEGIN    
    DECLARE done INT DEFAULT FALSE;
    DECLARE a,b DATETIME;
    DECLARE cur CURSOR FOR SELECT dateTimeCol FROM targetTable
                           ORDER BY dateTimeCol ASC;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;     
    OPEN cur;       
    FETCH cur INTO a;       
    read_loop: LOOP
        SET b = a;
        FETCH cur INTO a;   
        IF done THEN
            LEAVE read_loop;
        END IF;     
        IF DATEDIFF(a,b) > [range you specify] THEN
            INSERT INTO tmp_table (gap_begin, gap_end)
            VALUES (a,b);
        END IF;
    END LOOP;           
    CLOSE cur;      
    END;

В этом случае предполагается, что tmp_table существует. Вы можете легко определить это как ВРЕМЕННУЮ таблицу в процедуре, но я исключил ее из этого примера.

Я пробую это на MariaDB 10.3.27, поэтому эта процедура может не работать, но я получаю сообщение об ошибке при создании процедуры, и я не могу понять, почему! У меня есть таблица electric_use с полем Intervaldatetime DATETIME, в котором я хочу найти пробелы. Я создал целевую таблицу electric_use_gaps с полями gap_begin datetime и gap_end datetime.

Данные собираются каждый час, и я хочу знать, не пропускаю ли я хотя бы час данных за 5 лет.

 DELIMITER $$  
  CREATE PROCEDURE findgaps()
    BEGIN    
    DECLARE done INT DEFAULT FALSE;
    DECLARE a,b DATETIME;
    DECLARE cur CURSOR FOR SELECT Intervaldatetime FROM electric_use
                           ORDER BY Intervaldatetime ASC;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;     
    OPEN cur;       
    FETCH cur INTO a;       
    read_loop: LOOP
        SET b = a;
        FETCH cur INTO a;   
        IF done THEN
            LEAVE read_loop;
        END IF;     
        IF TIMESTAMPDIFF(MINUTE,a,b) > [60] THEN
            INSERT INTO electric_use_gaps(gap_begin, gap_end)
            VALUES (a,b);
        END IF;
    END LOOP;           
    CLOSE cur;      
    END&&
    
    DELIMITER ;

Это ошибка:

Query: CREATE PROCEDURE findgaps() BEGIN DECLARE done INT DEFAULT FALSE; DECLARE a,b DATETIME; DECLARE cur CURSOR FOR SELECT Intervalda...

Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '[60] THEN
            INSERT INTO electric_use_gaps(gap_begin, gap_end)
   ...' at line 16