From: | Christoph Haller <ch(at)rodos(dot)fzk(dot)de> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Cc: | Mark(dot)Connelly(at)ntl(dot)com |
Subject: | Re: using Avg() |
Date: | 2003-03-04 11:14:18 |
Message-ID: | 3E648A8A.91C289CB@rodos.fzk.de |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-sql |
>
> 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
From | Date | Subject | |
---|---|---|---|
Next Message | Gary Stainburn | 2003-03-04 11:19:30 | Re: Insert multiple Rows |
Previous Message | Oleg Samoylov | 2003-03-04 10:54:40 | Re: Insert multiple Rows |