@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'
и т.д.
  • Вопрос задан
  • 51 просмотр
Решения вопроса 1
rozhnev
@rozhnev
Fullstack programmer, DBA, медленно, дорого
Получился немного сложный запрос:
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 можно написать более короткий вариант
Ответ написан
Пригласить эксперта
Ваш ответ на вопрос

Войдите, чтобы написать ответ

Войти через центр авторизации
Похожие вопросы