Message-ID: <41F5340D.8020601@akyasociados.com.ar>
Date: Mon, 24 Jan 2005 14:44:45 -0300
From: franco <franco@akyasociados.com.ar>
User-Agent: Mozilla Thunderbird 1.0 (Windows/20041206)
MIME-Version: 1.0
To: mrblonde@locked.myftp.org
Cc: pgsql-sql@postgresql.org
Subject: Re: update from multiple rows
References: <BAY13-F384058140ADAAD16F8BE889830@phx.gbl>
In-Reply-To: <BAY13-F384058140ADAAD16F8BE889830@phx.gbl>
Content-Type: text/plain; charset=ISO-2022-JP
Content-Transfer-Encoding: 7bit

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
>
> _________________________________________________________________
> 無料メールならやっぱり 「MSN Hotmail」 http://www.hotmail.com/
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>