Re: grouping subsets

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(at)postgresql(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql