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 |
+------+------+------+
Working table TT is recommended to create an ordered sequence of rows in
which "next" and "previous" rows are more easily described than in the
source table. Avoid "order by" on views. It works in SQLite but it
should not!
The idea is to identify maximal sequences of identical "test" values as
follow:
- T1 denotes the first row of a sequence
- T3 the last row
- T2 any "disturbing" row lying between T1 and T3 but with a different
value of "test"
- first "not exists" condition states that T1 must be the very first of
the sequence: it must not be immediately preceded by a row with same
value of "test"
- same for second "not exists" condition: T3 must be the last
- the third "not exists" condition states that there is no "disturbing"
row between T1 and T3.
Valid if maximal sequences do not overlap. This query also detects
single row sequences (e.g., 'xxx').
An index on TT.test may be useful to support T1*T3 join.
For large tables, an iterative procedure will be faster, though less
elegant!
Regards
Jean-Luc Hainaut
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
