Помогите составить запрос - нужно сравнивать соседние записи

[rgl]

MySQL

Есть таблица, поле первичный ключ типа datetime, остальные поля не важны. В таблице ок. 5 млн записей. Обычно записи следуют одна за другой с интервалом 1 минута, как-то так:

Spoiler

SELECT * FROM (имя таблицы) WHERE `TIME` BETWEEN '2017-07-21 08:58' AND '2017-07-21 10:57' ORDER BY `TIME` ASC;

результат:

Array (

Array ( [TIME] => 2017-07-21 08:58:00 [SHORT] => 0.00000000641 [LONG] => 0.0000000665 [SOURCE] => 15 )

Array ( [TIME] => 2017-07-21 08:59:00 [SHORT] => 0.00000000583 [LONG] => 0.0000000664 [SOURCE] => 15 )

Array ( [TIME] => 2017-07-21 09:00:00 [SHORT] => 0.00000000435 [LONG] => 0.0000000666 [SOURCE] => 15 )

Array ( [TIME] => 2017-07-21 09:01:00 [SHORT] => 0.00000000518 [LONG] => 0.0000000698 [SOURCE] => 15 )

Array ( [TIME] => 2017-07-21 09:02:00 [SHORT] => 0.0000000056 [LONG] => 0.0000000679 [SOURCE] => 15 )

и т.д.

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

Все, что сам я смог придумать, так это создать временную таблицу с полем id AUTO_INCREMENTи полем datetime, скопировать в нее, а затем сделать запрос из нее со джоином на себя же, по условию t1.id+1 = t2.id

А можно ли без создания временной таблицы?

[Николя223]

плохой первичный ключ. возьмите за правило в любую таблицу добавлять первичный ключ id с номером. 

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

t1.id+1 = t2.id поля из таблицы не удаляются?  

 

 

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

[rgl]

1. Первичный ключ хороший, но предлагаю не спорить на эту тему (нужно ли всегда добавлять поле id, которое в некоторых книжках называют "суррогатным ключом"). Единственное, что плохо, это имя поля TIME, совпадающее с зарезервированным словом.

2. Записи из таблицы не удалаются.

3. Добавлять дельты, или делать что-либо с этой таблицей не хочу, т.к. это для работы (нормальной) не нужно, а только для решений внештатных ситуаций.

4. Что такое "итератор ключа" я не знаю, я только начинаю осваивать SQL.

Хочется понять, можно ли не меняя имеющуюся таблицу сделать запрос, чтобы получить дельты. Я вижу только два варианта 1. Создать временную таблицу для этой задачи, которую потом дропнуть. 2. Вывести данные в текстовый файл, и дальше уже скриптовым языком независимо от базы найти что хочется.

[Switch74]

возможно прокатит следующая идея:
SELECT * FROM (имя таблицы) as t1, (имя таблицы) as t2 WHERE t1.id > 1 AND t1.id = t2.id-1 AND t1.TIME - t2.TIME > 1
запрос не корректен, только передаю идею

[Николя223]

не.  у него id нет.  да и лучше с большими таблицами такие запросы не проводить, JOIN в этом плане лучше

[wwt]

Грубо говоря есть у нас таблица test с известным интервалом равным 10-ти

 

num
10
20
30
41
50
60
70
80
95
100
110
120
130

Как узнать на какой из записей заканчивается яма:

SELECT * FROM test WHERE num%10 != 0

Получим выборку:

num
41
95

Как получить и начало ямы и конец, ну как-то так

SELECT 
A.num AS start_num, B.num AS stop_num
FROM test B
LEFT JOIN test A ON A.num = FLOOR( (B.num / 10) ) * 10 - 10
WHERE B.num %10 != 0

получим вот такой вывод

start_num 	stop_num
30 			41
80 			95

 

 

[Николя223]

@wwt а с датами то как быть?   %10  - я так понимаю остаток от деления

[Switch74]

дату переводить в минуты например или банальный timestamp, получится какое-нибудь число, его и делить

[Николя223]

дак мне вот это и не нравится.  я просто не задавался вопросом как это работает в mysql.  какие механизмы использует сервер при конверсии.  но что то мне подсказывает - если таблица большая это требует много ресурсов. 5 миллионов записей 

[Switch74]

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

[wwt]

27 минут назад, Николя223 сказал:

%10  - я так понимаю остаток от деления

оператор деления по модулю =) Возвращает остаток все верно.

[wwt]

Мой пример лишь пример, на самом деле запрос может усложниться очень сильно, к примеру если при известной периодичности, запись произойдет не через 1 минуту, а через 2-3 и т.д.  Тогда идет смещение во времени по всей таблице и вычислять начало ямы будет уже труднее.

 

[Николя223]

в данном случае если период ровно 1 минута. можно выкрутиться и обычным NOT LIKE

[CoDy]

Это не совсем запрос. Но как вариант рассматривать можно.

DELIMITER ;

DROP TABLE IF EXISTS `test_table`;
CREATE TABLE `test_table` (
  `date_time` DATETIME NOT NULL,
  PRIMARY KEY (`date_time`)
);

INSERT INTO `test_table`
VALUES
  ('2000-01-01 00:00:00'),
  ('2000-01-01 00:01:00'),
  ('2000-01-01 00:02:00'),
  ('2000-01-01 00:05:00'),
  ('2000-01-01 00:06:00'),
  ('2000-01-01 00:07:00');

DELIMITER $$

DROP PROCEDURE IF EXISTS `test`$$
CREATE PROCEDURE `test` (IN time_interval INT UNSIGNED)
BEGIN
  DECLARE done BOOL DEFAULT FALSE;  
  DECLARE datetime_previous DATETIME DEFAULT NULL;
  DECLARE datetime_current DATETIME DEFAULT NULL;
  DECLARE cur CURSOR FOR SELECT `date_time` FROM `test_table`;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
  
  CREATE TEMPORARY TABLE `temp_result`
  SELECT `date_time`
  FROM `test_table`
  LIMIT 0;
 
  OPEN cur;  

  FETCH cur INTO datetime_previous;
  
  WHILE done = FALSE DO 
    FETCH cur INTO datetime_current;

    IF UNIX_TIMESTAMP(datetime_current) - UNIX_TIMESTAMP(datetime_previous) > time_interval THEN
      INSERT INTO `temp_result`
      VALUES
        (datetime_previous),
        (datetime_current);
    END IF;

    SET datetime_previous = datetime_current;     
  END WHILE;

  CLOSE cur;

  SELECT *
  FROM `test_table`
  WHERE date_time IN (
    SELECT date_time FROM `temp_result`
  );

  DROP TEMPORARY TABLE `temp_result`;
END$$

DELIMITER ;

SELECT * FROM test_table tt;

CALL test(60);

[Николя223]

вы когда нибудь такой хренотенью сервер БД хлопните. бред полнейший

[rgl]

Скопировал часть таблицы к себе на компьютер, на тестовую базу, чтобы поинраться. Вот результаты:

1. Вариант без создания временной таблицы, работает правильно, но с увеличением размера время растет невероятно быстро. На рабочем сервере даже думать нельзя такое запускать.

Spoiler

-- для начала, вот такая таблица у нас есть

mysql> desc gtest;
+——--+———-+——+—--+———————+——-+
| Field  | Type     | Null | Key | Default             | Extra |
+——--+———-+——+—--+———————+——-+
| TIME   | datetime | NO   | PRI | 0000-00-00 00:00:00 |       | 
| SHORT  | float    | YES  |     | NULL                |       | 
| LONG   | float    | YES  |     | NULL                |       | 
| SOURCE | int(11)  | YES  |     | NULL                |       | 
+——--+———-+——+—--+———————+——-+
4 rows in set (0.02 sec)

mysql> select count(*) from gtest;
+———-+
| count(*) |
+———-+
|    10000 | 
+———-+
1 row in set (0.00 sec)


-- теперь запрос без создания временной таблицы. Все правильно работает, находит 4 разрыва:


mysql> SELECT t.TIME, (SELECT UNIX_TIMESTAMP(TIME) FROM gtest WHERE TIME>t.TIME ORDER BY TIME LIMIT 1)-UNIX_TIMESTAMP(t.TIME) AS dif FROM gtest AS t ORDER BY dif DESC LIMIT 20;
+———————+———+
| TIME                | dif     |
+———————+———+
| 2010-03-15 13:55:00 | 5911500 | 
| 2010-05-23 00:00:00 |  988920 | 
| 2010-06-03 13:19:00 |  186360 | 
| 2010-06-05 20:47:00 |   36720 | 
| 2010-06-03 10:42:00 |      60 | 
| 2010-06-03 10:43:00 |      60 | 
| 2010-06-03 10:44:00 |      60 | 
| 2010-06-03 10:45:00 |      60 | 
| 2010-06-03 10:46:00 |      60 | 
| 2010-06-03 10:47:00 |      60 | 
| 2010-06-03 10:48:00 |      60 | 
| 2010-06-03 10:49:00 |      60 | 
| 2010-06-03 10:50:00 |      60 | 
| 2010-06-03 10:51:00 |      60 | 
| 2010-06-03 10:52:00 |      60 | 
| 2010-06-03 10:53:00 |      60 | 
| 2010-06-03 10:54:00 |      60 | 
| 2010-06-03 10:55:00 |      60 | 
| 2010-06-03 10:56:00 |      60 | 
| 2010-06-03 10:57:00 |      60 | 
+———————+———+
20 rows in set (22.12 sec)

 

 

 

2. Вариант с временной таблицей несравненно шустрее, т.е. вполне ничего

Spoiler

-- а теперь с временной таблицей, для начала создадим ее и скопируем в нее данные


mysql> create table test (id integer auto_increment primary key, fld1 datetime);
Query OK, 0 rows affected (0.08 sec)

mysql> insert into test (fld1) select `TIME` from gtest order by `TIME`;
Query OK, 10000 rows affected (0.03 sec)
Records: 10000  Duplicates: 0  Warnings: 0

-- и собственно запрос



mysql> select t1.fld1 as time, UNIX_TIMESTAMP(t2.fld1)-UNIX_TIMESTAMP(t1.fld1) as dif from  test as t1 inner join test as t2 on t1.id=t2.id-1 order by dif desc limit 20;
+———————+———+
| time                | dif     |
+———————+———+
| 2010-03-15 13:55:00 | 5911500 | 
| 2010-05-23 00:00:00 |  988920 | 
| 2010-06-03 13:19:00 |  186360 | 
| 2010-06-05 20:47:00 |   36720 | 
| 2010-03-11 07:02:00 |      60 | 
| 2010-03-13 21:46:00 |      60 | 
| 2010-03-09 00:38:00 |      60 | 
| 2010-03-11 08:06:00 |      60 | 
| 2010-03-13 22:50:00 |      60 | 
| 2010-03-09 01:42:00 |      60 | 
| 2010-03-11 09:10:00 |      60 | 
| 2010-03-13 23:54:00 |      60 | 
| 2010-03-09 02:46:00 |      60 | 
| 2010-03-11 10:14:00 |      60 | 
| 2010-03-14 00:58:00 |      60 | 
| 2010-03-09 03:50:00 |      60 | 
| 2010-03-11 11:18:00 |      60 | 
| 2010-03-14 02:02:00 |      60 | 
| 2010-03-09 04:54:00 |      60 | 
| 2010-03-11 12:22:00 |      60 | 
+———————+———+
20 rows in set (0.09 sec)

-- тоже все правильно, но несравненно быстрее (уверен, чем больше исходная таблица, тем разница во времени будет существеннее)