Как найти непрерывную последовательность?

Question

[dedulka]

Добрый день. Есть таблица

CREATE TABLE `test_table`
 (
	`id` INT(11) NOT NULL AUTO_INCREMENT,
	`current_status` TEXT NULL DEFAULT NULL COLLATE 'utf8_general_ci',
	`dateadd` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP,
        `user_id` INT(11) NULL DEFAULT NULL,
         PRIMARY KEY (`id`) USING BTREE
)
COLLATE='utf8_general_ci'
ENGINE=InnoDB
AUTO_INCREMENT=528;

Пример данных
1,'new','2020-10-01 03:00:59',3
2,'old','2020-10-11 02:00:59',3
3,'new','2020-10-11 05:00:59',3
4,'new','2020-10-12 07:00:59',3
5,'old','2020-11-01 03:00:59',3
6,'new','2020-11-12 03:00:59',3
7,'new','2020-11-14 03:00:59',3
8,'old','2020-11-15 03:00:59',3
Как можно найти и выбрать непрерывную последовательность (with group)
с 2020-10-01 03:00:59 по 2020-10-11 02:00:59 user_id=3 имел current_status='new'
с 2020-10-11 02:00:59 по 2020-10-11 05:00:59 user_id=3 имел current_status='old'
и т.д.

Comments

[Slava Rozhnev]

Не очень понятен вопрос. Можете привести желаемый результат? Так же уточните версию MySQL

[dedulka]

Slava Rozhnev, Нужно получить значения интервала, между событиями.

Например

Две записи имеют общий признак user_id.
1,'new','2020-10-01 03:00:59',3
Интервал начался (new) в 2020-10-01 03:00:59 для user_id=3

2,'old','2020-10-11 02:00:59',3
В 2020-10-11 02:00:59 вставка новой записи для user_id=3 но с current_status='old'

Т.е. интервал между двумя записями 2020-10-01 03:00:59 2020-10-11 02:00:59
В этом интервале значение было current_status='new'

Соответственно нужен такой запрос который выведет (select * from ... with group скорее всего)
'2020-10-01 03:00:59','2020-10-11 02:00:59','new',3

По простому
Выбрать все интервалы(начало, завершение),статус если user_id=3

Answer 1

[Slava Rozhnev]

Получился немного сложный запрос:

SELECT 
  user_id, 
  MIN(current_status) current_status, 
  MIN(dateadd) dateadd, 
  status_changed 
FROM 
  (
    SELECT 
      tt.*, 
      MIN(tt1.dateadd) as status_changed 
    FROM 
      `test_table` tt 
      LEFT JOIN `test_table` tt1 ON tt1.user_id = tt.user_id 
      AND tt1.dateadd > tt.dateadd 
      AND tt1.current_status <> tt.current_status 
    GROUP BY 
      tt.id, 
      tt.current_status, 
      tt.user_id
  ) aggregated 
GROUP BY 
  user_id, 
  status_changed 
ORDER BY 
  dateadd;

выполнить SQL запрос

результат:

+=========+================+=====================+=====================+
| user_id | current_status | dateadd             | status_changed      |
+=========+================+=====================+=====================+
| 3       | new            | 2020-10-01 03:00:59 | 2020-10-11 02:00:59 |
+---------+----------------+---------------------+---------------------+
| 3       | old            | 2020-10-11 02:00:59 | 2020-10-11 05:00:59 |
+---------+----------------+---------------------+---------------------+
| 3       | new            | 2020-10-11 05:00:59 | 2020-11-01 03:00:59 |
+---------+----------------+---------------------+---------------------+
| 3       | old            | 2020-11-01 03:00:59 | 2020-11-12 03:00:59 |
+---------+----------------+---------------------+---------------------+
| 3       | new            | 2020-11-12 03:00:59 | 2020-11-15 03:00:59 |
+---------+----------------+---------------------+---------------------+
| 3       | old            | 2020-11-15 03:00:59 | (null)              |
+---------+----------------+---------------------+---------------------+

Этот запрос работает для MeSQL 5..x , начиная с MySQL 8.0 можно написать более короткий вариант

Comments

[dedulka]

Да! Самое то!
Спасибо!