From: | "Oliveiros d'Azevedo Cristina" <oliveiros(dot)cristina(at)marktest(dot)pt> |
---|---|
To: | <pgsql-sql(at)postgresql(dot)org>, "Rainer Stengele" <rainer(dot)stengele(at)diplan(dot)de> |
Subject: | Re: grouping subsets |
Date: | 2010-07-27 10:37:49 |
Message-ID: | 61EBCE3AA9564FFDB88B18177F726FCB@marktestcr.marktest.pt |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
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
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2010-07-27 20:50:24 | Re: [TRIGGER] Returning values from BEFORE UPDATE trigger, without using them |
Previous Message | Osvaldo Kussama | 2010-07-26 15:44:56 | Re: Returning only alphanumeric values from a query |