Message-Id: <10625.117518@fatcity.com>
From: "LGE"
Date: Wed, 20 Sep 2000 20:30:32 -0500
Subject: RE: rolling group by
Dave,
I haven't worked with them much, yet, but if you are on 8.1.6, you might
want to look into using the "analytic" functions and the STDDEV functions.
Here is a snippet from the SQL manual:
<<>>
Analytic Functions
Analytic functions compute an aggregate value based on a group of rows. The
group of rows is called a window and is defined by the analytic clause. For
each row, a "sliding" window of rows is defined. The window determines the
range of rows used to perform the calculations for the "current row". Window
sizes can be based on either a physical number of rows or a logical interval
such as time.
Analytic functions are the last set of operations performed in a query
except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and
HAVING clauses are completed before the analytic functions are processed.
Therefore, analytic functions can appear only in the select list or ORDER BY
clause.
Analytic functions are commonly used to compute cumulative, moving,
centered, and reporting aggregates.
<<>>
If prior to 8.1.6, you might try it using STDDEV and a self join with
criteria setting the boundaries:
select M1.Tdate, stddev(m2.measure)