|
|
 | | From: | adam etienne | | Subject: | update from multiple rows | | Date: | Sat, 22 Jan 2005 12:51:20 +0000 |
|
|
 | hi
I have some trouble updating a table like this one :
date | data_raw | data_sys
12-01 | 5 | 4.5
13-01 | 6 | 6
14-01 | 7 | 8
I would like to update the 'data_sys' row by computing values of multiple
'data_raw' values. I mean for example :
data_sys(13-01) = (data_raw['12-01'] + data_raw['13-01'] +
data_raw['14-01'] )/3;
I thought of a function that fetch the 3 data_raw rows for each rows....
but it was obviously too much slow...
Is there a more efficient way to achieve this ?
Thanks in advance.. This could help me very much..
Etienne Adam
_________________________________________________________________
無料メールならやっぱり 「MSN Hotmail」 http://www.hotmail.com/
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
|
|
| | From: | Michael Fuhr | | Subject: | Re: update from multiple rows | | Date: | Sat, 22 Jan 2005 12:35:39 -0700 |
|
|
| On Sat, Jan 22, 2005 at 12:51:20PM +0000, adam etienne wrote:
>
> I have some trouble updating a table like this one :
> date | data_raw | data_sys
> 12-01 | 5 | 4.5
> 13-01 | 6 | 6
> 14-01 | 7 | 8
>
> I would like to update the 'data_sys' row by computing values of multiple
> 'data_raw' values. I mean for example :
> data_sys(13-01) = (data_raw['12-01'] + data_raw['13-01'] +
> data_raw['14-01'] )/3;
Is there a reason to maintain data_sys in the table? Could you use
a view instead? A view could do self joins (join the table against
itself) and perform the necessary calculations on the fly when you
make a query. That could be expensive if you select large data
sets, but for small slices of data it might suffice.
Another possibility might be to use a trigger to recalculate data_sys
when records are inserted, updated, or deleted. You'd still have
to do a potentially expensive one-time update of the entire table,
but future updates would then touch only the rows that depend on
the data being inserted, updated, or deleted, and the calculated
values would always be current. Using a trigger would require some
care, however, to avoid cascading updates that are unnecessary or
that could result in infinite recursion.
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
|
|
| | From: | adam etienne | | Subject: | Re: update from multiple rows | | Date: | Sun, 23 Jan 2005 11:36:11 +0000 |
|
|
| Thanks for your answer
In fact the computation is somewhat more complex than an average and the
data set is quite large... I did some test with view & triggers but it's
too slow..
Moreover, sometime i need to do big insertion or update and then other time
i need juste little update of this table...
I would like to apply a trigger only for little update but i don't know how
to proceed.. Maybe with a condition into the trigger.. But it's adding
computation time...
Thanks again,
Etienne Adam
> > I have some trouble updating a table like this one :
> > date | data_raw | data_sys
> > 12-01 | 5 | 4.5
> > 13-01 | 6 | 6
> > 14-01 | 7 | 8
> >
> > I would like to update the 'data_sys' row by computing values of
multiple
> > 'data_raw' values. I mean for example :
> > data_sys(13-01) = (data_raw['12-01'] + data_raw['13-01'] +
> > data_raw['14-01'] )/3;
>
>Is there a reason to maintain data_sys in the table? Could you use
>a view instead? A view could do self joins (join the table against
>itself) and perform the necessary calculations on the fly when you
>make a query. That could be expensive if you select large data
>sets, but for small slices of data it might suffice.
>
>Another possibility might be to use a trigger to recalculate data_sys
>when records are inserted, updated, or deleted. You'd still have
>to do a potentially expensive one-time update of the entire table,
>but future updates would then touch only the rows that depend on
>the data being inserted, updated, or deleted, and the calculated
>values would always be current. Using a trigger would require some
>care, however, to avoid cascading updates that are unnecessary or
>that could result in infinite recursion.
_________________________________________________________________
無料メールならやっぱり 「MSN Hotmail」 http://www.hotmail.com/
---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
|
|
| | From: | Michael Fuhr | | Subject: | Re: update from multiple rows | | Date: | Sun, 23 Jan 2005 23:45:49 -0700 |
|
|
| On Sun, Jan 23, 2005 at 11:36:11AM +0000, adam etienne wrote:
> In fact the computation is somewhat more complex than an average and the
> data set is quite large... I did some test with view & triggers but it's
> too slow..
Can you provide any more detail about the algorithm and the number
of rows that you might have to insert or update? How did the test
triggers work? If you used row-level triggers and if a trigger on
one row updated multiple rows, then you might have been updating
rows more times than necessary (once by explicit update and one or
more unnecessary times by triggers on other rows).
--
Michael Fuhr
http://www.fuhr.org/~mfuhr/
---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
|
|
|
