Re: [SQL] grouping subsets

2010-07-27 Thread Oliveiros d'Azevedo Cristina 

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
ANDse.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: 
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 



--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] [TRIGGER] Returning values from BEFORE UPDATE trigger, without using them

2010-07-27 Thread Tom Lane 
=?ISO-8859-15?Q?Torsten_Z=FChlsdorff?=  writes:
> I have defined an BEFORE UPDATE trigger. The trigger catch every update, 
> change some columns of the new row, make an insert of the new row and 
> returns null to abort the update.

Why in the world would you do that?  Just return the modified row from
the trigger and let the update proceed normally.

regards, tom lane

-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

Re: [SQL] [TRIGGER] Returning values from BEFORE UPDATE trigger, without using them

2010-07-27 Thread Torsten Zühlsdorff 

Tom Lane schrieb:

=?ISO-8859-15?Q?Torsten_Z=FChlsdorff?=  writes:
I have defined an BEFORE UPDATE trigger. The trigger catch every update, 
change some columns of the new row, make an insert of the new row and 
returns null to abort the update.


Why in the world would you do that?  Just return the modified row from
the trigger and let the update proceed normally.


Because of content-revision. I have a lot of text which is modified and 
created from multiple persons. The requierment is, that i need every 
version of every content (+ their meta-data).
And i don't need just a log, which lists the changes. The different 
revisions of *one* content are used at the *same* time at *different* 
parts of the website. Therefore i rewrite every UPDATE to an INSERT. And 
except of the missing returning everything works fine and fast. :)


Greetings,
Torsten
--
http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 
verschiedenen Datenbanksystemen abstrahiert,
Queries von Applikationen trennt und automatisch die Query-Ergebnisse 
auswerten kann.


--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql