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 |
``` |