15 feb 2017, Jean-Luc Hainaut:
You could try this, inspired by classic algorithms of temporal
databases:
create table T(date integer,test char(12));
insert into T
values (1,'clim'),(3,'clim'),(7,'amb'),(10,'amb'),(12,'xxx'),
(13,'clim'),(15,'clim'),(20,'clim'),(22,'amb'),(25,'amb');
create table TT(seq integer not null primary key autoincrement,date
integer,test char(12));
insert into TT(date,test) select * from T order by date;
select T1.date, T3.date, T1.test
from TT T1, TT T3
-- More efficient than "where T1.date <= T3.date"
where T1.seq <= T3.seq
and T1.test = T3.test
and not exists(select * from TT where seq = T1.seq-1 and test =
T1.test)
and not exists(select * from TT where seq = T3.seq+1 and test =
T3.test)
and not exists(select *
from TT T2
-- More efficient than "where T2.date between
T1.date and T3.date"
where T2.seq between T1.seq and T3.seq
and T2.test <> T1.test);
Result:
+------+------+------+
| date | date | test |
+------+------+------+
| 1 | 3 | clim |
| 7 | 10 | amb |
| 12 | 12 | xxx |
| 13 | 20 | clim |
| 22 | 25 | amb |
+------+------+------+
Hello, the query below is simpler. May be slower. But looks pretty
relational. Thanks, E Pasma.
create table T(date integer,test char(12));
insert into T
values (1,'clim'),(3,'clim'),(7,'amb'),(10,'amb'),(12,'xxx'),
(13,'clim'),(15,'clim'),(20,'clim'),(22,'amb'),(25,'amb');
select min(date) as fromdate, max(date) as enddate, test
from (--get closest preceeding different key
select t.*, max(t2.date) as key2
from t
left join t t2
on t2.date<t.date and t2.test<>t.test
group by t.date
)
group by key2
;
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
