Jump to content
  • Sign Up
rgl

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

Recommended Posts

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

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

Share this post


Link to post
Share on other sites

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

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

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

 

 

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

Share this post


Link to post
Share on other sites

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

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

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

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

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

Грубо говоря есть у нас таблица 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

 

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites
27 минут назад, Николя223 сказал:

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

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

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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

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);

Share this post


Link to post
Share on other sites

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

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)

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

 

 

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Member Statistics

    46,897
    Total Members
    1,451
    Most Online
    Neovo
    Newest Member
    Neovo
    Joined
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Спрашивают сейчас

  • Пишут сейчас

    • Дорогие Друзья! Рад вам сообщить о выходе новой версии движка Flextype 0.9.9! В Flextype 0.9.9 достаточно много важных изменений определяющих — то как будет выглядеть финальная версия Flextype 1.0.0, к которой я иду уже более двух лет! Были пересмотрены, обновлены и переписаны ключевые моменты ядра, ответственные за работу с Записями, Коллекциями, Кешем и Rest API. Подробнее в статье: https://vk.com/@flextype-flextype-099-reliz Большое спасибо всем за то, что вы являетесь частью сообщества Flextype!
    • Привет! Меня зовут Евгения и я ищу талантливых программистов в компанию DevCube. https://mydevcube.com/ Это международная компания, которая занимается DevOps, web и software разработками. На данный момент в команду требуются Full stack .NET (Junior & Senior) — 3+ year of commercial experience; — Strong knowledge of Angular 5+; — Strong knowledge of Typescript; — Experience with HTML/HTML5, CSS/CSS3, SCSS; — Good understanding of Git; — Conversational English. Будет плюсом: VB experience Условия: Удаленная работа full-time Зарплата от 10-20$/час по результатам собеседования. Обязательное условие - быть на связи до 21-22:00 по Москве. Для отклика прошу писать на почту: evgeniia.ko@mydevcube.com
    • Привет! Меня зовут Евгения и я ищу талантливых программистов в компанию DevCube. https://mydevcube.com/ Это международная компания, которая занимается DevOps, web и software development. На данный момент в команду требуются PHP developers (Junior & Senior) Requirements: — 3+ year of commercial experience; — Strong knowledge PHP 7+ — Framework knowledge — Conversational English — CSS, Json, HTML5 Будет плюсом: Shopify implementation expertise. Условия: Удаленная работа full-time Зарплата от 10-20$/час по результатам собеседования. Обязательное условие - быть на связи до 21-22:00 по Москве. Для отклика прошу писать на почту: evgeniia.ko@mydevcube.com
  • Popular Contributors

    Nobody has received reputation this week.

×
×
  • Create New...