Dear all,
I need to perform a kind of partial GROUP BY to determine the beginnings and
ends of sets of identical data. I can't use a full GROUP BY because these sets
can be repeated and their repetition must be conserved. Other database engines
have solutions for this task (like windowing in postgre) but I wonder if there
is an efficient recipe in SQLite.
Example:
=======
Table: mytable
--------
date test
------ ------
1 clim
3 clim
7 amb
10 amb
13 clim
15 clim
20 clim
22 amb
25 amb
Desired result
---------------------
date_from date_to test
--------------- ------------ ------
1 3 clim
7 10 amb
13 15 clim
22 25 amb
(non optimal) solution found
=====================
CREATE VIEW mytablebydate
AS -- Pre-order table to avoid ordering it twice in sub-queries
SELECT * FROM mytable ORDER BY date
CREATE VIEW mytablenext
AS
SELECT date,
test,
(
-- first row > this row
SELECT date -- NULL if not exists
FROM mytablebydate
WHERE date > MT.date
LIMIT 1
) as date_next,
(
-- first row > this row
SELECT test -- NULL if not exists
FROM mytablebydate
WHERE date > MT.date
LIMIT 1
) as test_next
FROM mytable MT
WHERE test != test_next
-- Get desired results
SELECT (
-- Date of the previous row
SELECT MAX( date_next )
FROM mytablenext
WHERE date_next < mt.date
) AS date_from,
date AS date_to, -- this row
test
FROM mytablenext mt
Comments
========
This method returns a Null for the first date_from and the last group is not
returned. It is therefore incomplete. In addition, it involves quite a lot of
subqueries. For completeness, it is inspired by
http://stackoverflow.com/questions/30455227/date-range-for-set-of-same-data/30460263#30460263.
So, is there a better / official way in SQLite ?
Any help will be welcome,
Jonathan
*******************************************************************************
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
