From: Christoph Haller <ch@rodos.fzk.de>
To: pgsql-sql@postgresql.org
Cc: Mark.Connelly@ntl.com
Subject: Re: using Avg()
Date: Tue, 04 Mar 2003 12:14:18 +0100
Message-ID: <3E648A8A.91C289CB@rodos.fzk.de> (raw)
>
> I have a table with a column named SwErr (Switch Error) with int
values
> date with datetime values and SwID with char(3)
> I am trying to get a subset back where the folowing is true
>
> select the avg(SwErr) for the last 30 days where SwId = 'XX1'
> Select the value of todays value of SwErr where SwId = 'XX1'
> if todays value of SwErr is greater than 4 times the SwErr Average
return in
> the value
>
Looks like you could use a plpgsql function, something like (untested)
CREATE OR REPLACE FUNCTION
get_dated_SwErr(CHAR) RETURNS INTEGER AS '
DECLARE
thisSwID ALIAS FOR $1;
todaysSwErr INT;
avgSwErr INT;
avg4SwErr INT;
dateLimit TIMESTAMP;
BEGIN
SELECT INTO dateLimit current_date - ''30 days''::interval ;
SELECT INTO todaysSwErr SwErr FROM <SwTable>
WHERE SwID = thisSwID AND SwDate = current_date ;
SELECT INTO avgSwErr AVG(SwErr)::int FROM <SwTable>
WHERE SwID = thisSwID AND SwDate BETWEEN current_date AND dateLimit ;
avg4SwErr := avgSwErr * 4 ;
IF todaysSwErr > avg4SwErr THEN
RETURN todaysSwErr;
ELSE
RETURN avgSwErr;
END IF;
END;
' LANGUAGE 'plpgsql' ;
then
SELECT SwID, get_dated_SwErr(SwID) FROM <SwTable>
WHERE SwID = 'XX1' AND SwDate = current_date ;
should bring up the result.
Regards, Christoph
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: ch@rodos.fzk.de, Mark.Connelly@ntl.com
Subject: Re: using Avg()
In-Reply-To: <3E648A8A.91C289CB@rodos.fzk.de>
* 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