@ Clemens, Petite Abeille,
Thanks, that's what I thought, but it's comforting to know for sure...
@ Jean-Luc,
Thanks a lot for the detailed answer, that's awesome ! I'll give it a try and
see how it compares with an external "manual" grouping
Jonathan
------------------------------
Message: 79
Date: Wed, 15 Feb 2017 11:16:24 +0100
From: Clemens Ladisch <clem...@ladisch.de>
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Group contiguous rows (islands)
Message-ID: <d689fbd5-0f4f-34ac-db98-72872d7a7...@ladisch.de>
Content-Type: text/plain; charset=us-ascii
Rossel, Jonathan wrote:
> Other database engines have solutions for this task (like windowing in
> postgre) but I wonder if there is an efficient recipe in SQLite.
SQLite does not have windowing functions. So the most efficient method
would be to read the data with a simple ORDER BY, and do the grouping
in your code.
Regards,
Clemens
------------------------------
Message: 83
Date: Wed, 15 Feb 2017 12:02:32 +0100
From: Jean-Luc Hainaut <jean-luc.hain...@unamur.be>
To: sqlite-users@mailinglists.sqlite.org
Subject: Re: [sqlite] Group contiguous rows (islands)
Message-ID: <58a43548....@unamur.be>
Content-Type: text/plain; charset=UTF-8; format=flowed
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
------------------------------
Message: 89
Date: Wed, 15 Feb 2017 12:58:07 +0100
From: Petite Abeille <petite.abei...@gmail.com>
To: SQLite mailing list <sqlite-users@mailinglists.sqlite.org>
Subject: Re: [sqlite] Group contiguous rows (islands)
Message-ID: <4b88b85b-75eb-4391-989e-198ebe31e...@gmail.com>
Content-Type: text/plain; charset=us-ascii
> On Feb 15, 2017, at 11:16 AM, Clemens Ladisch <clem...@ladisch.de> wrote:
>
> SQLite does not have windowing functions.
A continuous/continual tragedy indeed :|
Still, worthwhile mentioning The Tabibitosan Method, for reference purpose:
http://www.orchestrapit.co.uk/?p=53
https://community.oracle.com/message/3991678
Rather nifty in its simplicity and power. Sadly, out of reach to SQLite
dwellers.
------------------------------
Subject: Digest Footer
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
------------------------------
End of sqlite-users Digest, Vol 110, Issue 15
*********************************************
*******************************************************************************
This e-mail message is intended only for the addressee(s) and contains
information which may be confidential. If you are not the intended
recipient please do not read, save, forward, disclose or copy the contents
of this e-mail. If this e-mail has been sent to you in error, please delete
this
e-mail and any copies or links to this e-mail completely and immediately
from your system. We also like to inform you that communication via e-mail
over the Internet is insecure because third parties may have the possibility
to access and manipulate e-mails.
Any views expressed in this message are those of the individual sender,
except where the sender specifically states them to be the views of
The Swatch Group Ltd.
*******************************************************************************
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
