Here is a proposed syntax for your averaging (3-day average). Make absolutely sure
you have an index on (locid + obsdate)!!!! Repeat for 7 days and 15 days, then combine
them into a master view
CREATE VIEW ObsAverages (LocID,ObsDate,Avg3Days,Avg7Days,Avg15Days) AS +
SELECT t1.LocID,t1.ObsDate,t1.Avg3Days,t2.Avg7days,t3.Avg15days +
FROM Obs3days t1, Obs7days t2, Obs15days t3 +
WHERE t1.LocID = t2.LocID and t1.ObsDate = t2.ObsDate +
AND t1.LocID = t3.LocID and t1.ObsDate = t3.ObsDate
CREATE VIEW Obs3days (LocID,ObsDate,Avg3days) AS +
SELECT t1.LocId,t1.ObsDate,AVG(t2.ObsValue) AS Avg3days +
FROM DailyObs t1, DailyObs t2 +
WHERE t2.LocID = t1.LocID +
AND t2.ObsDate BETWEEN t1.(ObsDate-2) AND t1.ObsDate +
GROUP BY t1.LocID, t1.ObsDate
"Clarence W. Robison" <[EMAIL PROTECTED]> wrote:
>Oh knowledgable SQL stars, I have a question on a query.
>
>Given: A table with daily observations such as:
> �
> �create table DailyObs ( +
> � �LocID integer, +
> � �ObsDate date, +
> � �ObsValue real )
>
>Wanted: Running averages of the observed value (obsvalue) for various
>periods (3 day, 7 day, 15 day).
>
>Is this possible with a select statement, or will I need to use a cursor?
>
>Example: �
>------- Table DailyObs ----- � � � --- Results wanted --
>LocID � �ObsDate � � �ObsValue � � � � � 3-day �7-day �15-day
>100010 � 12/1/2000 � � � 5.6 � � � � � �
>100010 � 12/2/2000 � � � 2.8 � � � � � �
>100010 � 12/3/2000 � � � 2.8 � � � � 3.7 � � � �
>100010 � 12/4/2000 � � � 2.2 � � � � 2.6 � � � �
>100010 � 12/5/2000 � � � 3.3 � � � � 2.8 � � � �
>100010 � 12/6/2000 � � � 3.3 � � � � 2.9 � � � �
>100010 � 12/7/2000 � � �-2.8 � � � � 1.3 � � �2.5 �
>100010 � 12/8/2000 � � � 1.1 � � � � 0.5 � � �1.8 �
>100010 � 12/9/2000 � � �-0.6 � � � �-0.8 � � �1.3 �
>100010 � 12/10/2000 � � �4.4 � � � � 1.6 � � �1.6 �
>100010 � 12/11/2000 � � -1.7 � � � � 0.7 � � �1.0 �
>100010 � 12/12/2000 � � -2.2 � � � � 0.2 � � �0.2 �
>100010 � 12/13/2000 � � -2.2 � � � �-2.0 � � -0.6 �
>100010 � 12/14/2000 � � �1.1 � � � �-1.1 � � �0.0 �
>100010 � 12/15/2000 � � �2.8 � � � � 0.6 � �0.2 � � 1.3
>100010 � 12/16/2000 � � �1.1 � � � � 1.7 � � �0.5 � � � 1.0
>100010 � 12/17/2000 � � �2.2 � � � � 2.0 � � �0.2 � � � 1.0
>100010 � 12/18/2000 � � �0.0 � � � � 1.1 � � �0.4 � � � 0.8
>100010 � 12/19/2000 � � -1.1 � � � � 0.4 � � �0.6 � � � 0.6
>100010 � 12/20/2000 � � �0.0 � � � �-0.4 � � �0.9 � � � 0.4
>100010 � 12/21/2000 � � -2.2 � � � �-1.1 � � �0.4 � � � 0.0
>100010 � 12/22/2000 � � -2.8 � � � �-1.7 � � -0.4 � � � 0.0
>100010 � 12/23/2000 � � �2.8 � � � �-0.7 � � -0.2 � � � 0.1
>100010 � 12/24/2000 � � �3.3 � � � � 1.1 � � �0.0 � � � 0.4
>100010 � 12/25/2000 � � �1.1 � � � � 2.4 � � �0.2 � � � 0.1
>100010 � 12/26/2000 � � -1.7 � � � � 0.9 � � �0.1 � � � 0.1
>100010 � 12/27/2000 � � -5.0 � � � �-1.9 � � -0.6 � � � 0.0
>100010 � 12/28/2000 � � -1.7 � � � �-2.8 � � -0.6 � � � 0.0
>100010 � 12/29/2000 � � -5.6 � � � �-4.1 � � -1.0 � � �-0.5
>100010 � 12/30/2000 � � -2.2 � � � �-3.2 � � -1.7 � � �-0.8
>100010 � 12/31/2000 � � -1.1 � � � �-3.0 � � -2.3 � � �-0.9
>
>I have tried the following select without success:
>
> �select locid,obsdate,count(*),avg(obsvalue) from dailyobs +
> �where obsdate between (obsdate-2) and obsdate group by +
> �locid,obsdate
>
>TIA
>Clarence
>
>
> �--
>Clarence W. Robison, P.E.
>[EMAIL PROTECTED]
>
>================================================
>TO SEE MESSAGE POSTING GUIDELINES:
>Send a plain text email to [EMAIL PROTECTED]
>In the message body, put just two words: INTRO rbase-l
>================================================
>TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
>In the message body, put just two words: UNSUBSCRIBE rbase-l
>================================================
>TO SEARCH ARCHIVES:
>http://www.mail-archive.com/rbase-l%40sonetmail.com/
>
--
Albert Berry
Full Time Consultant to
PSD Solutions
350 West Hubbard, Suite 210
Chicago, IL 60610
312-828-9253 Ext. 32
__________________________________________________________________
The NEW Netscape 7.0 browser is now available. Upgrade now!
http://channels.netscape.com/ns/browsers/download.jsp
Get your own FREE, personal Netscape Mail account today at http://webmail.netscape.com/
================================================
TO SEE MESSAGE POSTING GUIDELINES:
Send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: INTRO rbase-l
================================================
TO UNSUBSCRIBE: send a plain text email to [EMAIL PROTECTED]
In the message body, put just two words: UNSUBSCRIBE rbase-l
================================================
TO SEARCH ARCHIVES:
http://www.mail-archive.com/rbase-l%40sonetmail.com/
