RE: rolling group by

From: <Yosi_at_comhill.com>
Date: Thu, 21 Sep 2000 10:20:14 -0400
Message-Id: <10626.117579@fatcity.com>

Sounds like you could use a nested select with the stdev and group by in it as a column in your query. Something like:

SELECT col1, date1, (SELECT stdev (col1)

                      FROM    tab1
                      WHERE   col1 = x.col1
                      AND     date1 between x.date1 - 31 and x.date1 - 1 )

rolling_stdev
FROM tab1 x
WHERE criteria
/

I just tried this and it worked. (By the way, the above code lines up well in Courrier.)

Of course if your still back in Oracle7, this would be a good time to upgrade.

HTH, Yosi

> -----Original Message-----
> From: dsliwa [mailto:dave_at_classmates.com]
> Sent: Wednesday, September 20, 2000 8:01 PM
> To: Multiple recipients of list ORACLE-L
> Subject: rolling group by
>
>
> I have a table of data with a single row for each day.
> I would like to
> get a rolling 30-day stddev for a particular column. That
> is, a stddev for
> the 30 days prior to each day. The sql will return a 30 days
> of data. Is
> this possible to do in single select statement? Any help is greatly
> appreciated. Thanks.
>
>
> Dave
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: dsliwa
> INET: dave_at_classmates.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Thu Sep 21 2000 - 09:20:14 CDT