Message-ID: <61EBCE3AA9564FFDB88B18177F726FCB@marktestcr.marktest.pt>
From: "Oliveiros d'Azevedo Cristina" <oliveiros.cristina@marktest.pt>
To: <pgsql-sql@postgresql.org>, "Rainer Stengele" <rainer.stengele@diplan.de>
References: <i28ubc$ie9$1@dough.gmane.org>
Subject: Re: grouping subsets
Date: Tue, 27 Jul 2010 11:37:49 +0100
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="ISO-8859-15";
	reply-type=original
Content-Transfer-Encoding: 7bit

Howdy, Rainer.

It's been a while, so I don't know if you are still interested in this 
problem or if you, in the meantime, found yourself a solution,
but I've tried this on a local copy of the example you provided   and it 
seems to work.

The problem is that I suspect that if you have several thousands of records 
on your table it will become slow...

Best,
Oliveiros

SELECT SUM(tudo.parcela),tudo.a
FROM
(
SELECT fo.parcela,fo.a,fo.b,fo.c,MIN(th.c) as d
FROM
(
SELECT se.a as parcela,se.b as a,se.c as b, MAX(pr.c) as c
FROM
yourTable se
LEFT JOIN
(
SELECT a.*
FROM yourTable a
JOIN yourTable b
ON (b.b <> a.b)
AND ((age(a.c,b.c) = '1 day'::interval)

)
) pr
ON pr.b = se.b
AND    se.c >= pr.c
GROUP BY se.a,se.b,se.c
) fo
LEFT JOIN
(
SELECT a.*
FROM yourTable a
JOIN yourTable b
ON (b.b <> a.b)
AND ((age(a.c,b.c) = '-1 day'::interval)
)
) th
ON fo.a = th.b
AND fo.b <= th.c
GROUP BY fo.parcela,fo.a,fo.b,fo.c
) tudo
GROUP BY tudo.a,tudo.c,tudo.d


To: <pgsql-sql@postgresql.org>
Sent: Thursday, July 22, 2010 9:09 AM
Subject: [SQL] grouping subsets


> Hi,
>
> having a table similar to
>
> | 1 | B | [2010-07-15 Do] |
> | 1 | B | [2010-07-16 Fr] |
> |---+---+-----------------|
> | 2 | C | [2010-07-17 Sa] |
> | 2 | C | [2010-07-18 So] |
> |---+---+-----------------|
> | 1 | B | [2010-07-19 Mo] |
> | 1 | B | [2010-07-20 Di] |
> | 1 | B | [2010-07-21 Mi] |
> | 1 | B | [2010-07-22 Do] |
> |---+---+-----------------|
> | 3 | D | [2010-07-23 Fr] |
>
> a simple group by gives me:
>
> | 6 | B |
> | 4 | C |
> | 3 | D |
>
>
> What I want to get is the values grouped by "subset", where a subset is a 
> set of rows with identical column until the colum changes.
> Is there a way to get
>
> | 2 | B |
> | 4 | C |
> | 4 | B |
> | 3 | D |
>
> by SQL only?
>
> - Rainer
>
>
>
>
>
> -- 
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql