yambejp score 57380
2017/03/03 23:17 投稿
| 曲解していたらスミマセン。以下のような感じでしょうか？ |
| - 元データ |
| ```SQL |
| create table tbl(id int primary key,name varchar(20),dt datetime,status varchar(10),index(name,dt)); |
| insert into tbl values |
| (1,'c','2017-03-03 21:46:55','live'), |
| (2,'a','2017-03-03 21:46:57','live'), |
| (3,'b','2017-03-03 21:46:59','live'), |
| (4,'c','2017-03-03 21:47:01','live'), |
| (5,'a','2017-03-03 21:47:03','live'), |
| (6,'d','2017-03-03 21:47:06','live'), |
| (7,'c','2017-03-03 21:47:08','live'), |
| (8,'d','2017-03-03 21:47:10','live'), |
| (9,'d','2017-03-03 21:47:12','live'), |
| (10,'b','2017-03-03 21:47:14','live'), |
| (11,'b','2017-03-03 21:47:16','live'), |
| (12,'c','2017-03-03 21:47:18','live'), |
| (13,'b','2017-03-03 21:47:20','live'), |
| (14,'d','2017-03-03 21:47:22','live'), |
| (15,'b','2017-03-03 21:47:24','dead'), |
| (16,'a','2017-03-03 21:47:26','dead'), |
| (17,'c','2017-03-03 21:47:28','dead'), |
| (18,'d','2017-03-03 21:47:30','dead'), |
| (19,'d','2017-03-03 21:47:32','dead'), |
| (20,'b','2017-03-03 21:47:34','dead'), |
| (21,'c','2017-03-03 21:48:17','dead'), |
| (22,'a','2017-03-03 21:48:19','dead'), |
| (23,'a','2017-03-03 21:48:21','dead'), |
| (24,'c','2017-03-03 21:48:23','dead'), |
| (25,'a','2017-03-03 21:48:25','dead'), |
| (26,'d','2017-03-03 21:48:27','dead'), |
| (27,'c','2017-03-03 21:48:29','dead'), |
| (28,'d','2017-03-03 21:48:31','dead'), |
| (29,'d','2017-03-03 21:48:33','dead'), |
| (30,'d','2017-03-03 21:48:35','dead'), |
| (31,'b','2017-03-03 21:48:37','live'), |
| (32,'b','2017-03-03 21:48:39','live'), |
| (33,'c','2017-03-03 21:48:41','live'), |
| (34,'d','2017-03-03 21:48:43','live'), |
| (35,'b','2017-03-03 21:48:46','live'), |
| (36,'a','2017-03-03 21:48:48','live'), |
| (37,'b','2017-03-03 21:48:50','live'), |
| (38,'b','2017-03-03 21:48:52','live'), |
| (39,'a','2017-03-03 21:48:54','live'), |
| (40,'b','2017-03-03 21:48:56','live'), |
| (41,'b','2017-03-03 21:48:58','live'), |
| (42,'c','2017-03-03 21:49:00','live'), |
| (43,'a','2017-03-03 21:49:02','live'), |
| (44,'c','2017-03-03 21:49:04','live'), |
| (45,'b','2017-03-03 21:49:06','live'), |
| (46,'a','2017-03-03 21:49:08','live'), |
| (47,'d','2017-03-03 21:49:10','dead'), |
| (48,'c','2017-03-03 21:49:12','dead'), |
| (49,'b','2017-03-03 21:49:14','dead'), |
| (50,'b','2017-03-03 21:49:16','dead'), |
| (51,'b','2017-03-03 21:49:19','dead'), |
| (52,'d','2017-03-03 21:49:21','dead'), |
| (53,'d','2017-03-03 21:49:23','dead'), |
| (54,'d','2017-03-03 21:49:25','dead'), |
| (55,'c','2017-03-03 21:49:27','dead'), |
| (56,'b','2017-03-03 21:49:29','dead'), |
| (57,'a','2017-03-03 21:49:31','dead'), |
| (58,'a','2017-03-03 21:49:33','dead'), |
| (59,'d','2017-03-03 21:49:35','dead'), |
| (60,'c','2017-03-03 21:49:37','dead'), |
| (61,'c','2017-03-03 21:49:39','live'), |
| (62,'c','2017-03-03 21:49:41','live'), |
| (63,'a','2017-03-03 21:49:43','live'), |
| (64,'d','2017-03-03 21:49:46','live'), |
| (65,'c','2017-03-03 21:49:48','live'), |
| (66,'c','2017-03-03 21:49:50','live'), |
| (67,'c','2017-03-03 21:49:52','live'), |
| (68,'d','2017-03-03 21:49:54','live'), |
| (69,'c','2017-03-03 21:49:56','live'), |
| (70,'d','2017-03-03 21:49:58','live'); |
| ``` |
| # 修正 |
| 前後がいる？ |
| - viewを用意します |
| ```SQL |
| create view vw as select id,name,dt,status,(select count(*)+1 from tbl as t2 where t2.name=t1.name and t2.dt<t1.dt) as rank from tbl as t1; |
| ``` |
| - 遷移抽出 |
| nameごとに遷移があった箇所を抽出 |
| nameごとに前後いずれかに遷移があったレコードを抽出 |
| ```SQL |
| select * |
| from (select id,name,status,(select count(*)+1 from tbl as t2 where t2.name=t1.name and t2.dt<t1.dt) as rank from tbl as t1) as t3 |
| inner join (select id,name,status,(select count(*)+1 from tbl as t5 where t5.name=t4.name and t5.dt<t4.dt) as rank from tbl as t4) as t6 |
| on t3.name=t6.name and t3.rank+1=t6.rank |
| and not(t3.status=t6.status) |
| ``` |
| select v2.id,v2.rank,v2.name,v2.dt |
| ,v1.status as beforestatus |
| ,v2.status |
| ,v3.status as afterstatus |
| from vw as v1 |
| inner join vw as v2 on v1.name=v2.name and v1.rank+1=v2.rank |
| inner join vw as v3 on v2.name=v3.name and v2.rank+1=v3.rank |
| where not(v1.status=v2.status) or not(v2.status=v3.status) |
| order by v2.name,v2.id |
| ``` |
