From: franco <franco@akyasociados.com.ar>
To: mrblonde@locked.myftp.org
Cc: pgsql-sql@postgresql.org
Subject: Re: update from multiple rows
Date: Mon, 24 Jan 2005 14:44:45 -0300
Message-ID: <41F5340D.8020601@akyasociados.com.ar> (raw)
In-Reply-To: <BAY13-F384058140ADAAD16F8BE889830@phx.gbl>
References: <BAY13-F384058140ADAAD16F8BE889830@phx.gbl>
I understand data_sys is the average value for the 3 days, from at the
day before to the day after.
This should do what you want, in one pass. Check the average function in
the subselect. If what you want is to divide by 3 no matter how many
records where found, enable the commented line.
UPDATE mytable SET data_sys=TMP.average
FROM (
--get the averages by date
SELECT
MT1.date AS date,
avg(MT2.data_raw) AS average
--sum(MT2.data_raw)/3 AS average
FROM
mytable MT1
INNER JOIN mytable MT2 ON (MT2.date BETWEEN MT1.date-1 AND MT1.date+1)
GROUP BY
MT1.date
) AS TMP
WHERE
mytable.date=TMP.date
Hope this is what you where looking for.
adam etienne wrote:
> 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
>
> _________________________________________________________________
> $BL5NA%a!<%k$J$i$d$C$Q$j(B $B!V(BMSN Hotmail$B!W(B http://www.hotmail.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: franco@akyasociados.com.ar, mrblonde@locked.myftp.org
Subject: Re: update from multiple rows
In-Reply-To: <41F5340D.8020601@akyasociados.com.ar>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox