Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Moving Average
asif.shariff_at_gmail.com wrote:
> I have a base table with the 2 columns, IMSI and Date. I would like to
> calculate a moving average of distinct IMSIs in a given window. I have
> developed my query to this point but am receiving an "ORDER BY not
> allowed here" error when I try to execute the query.
>
> select ir_date, count(distinct imsi) OVER (ORDER BY ir_date ASC RANGE 5
> PRECEDING)
> from ir_daily_unique
> where ir_date between to_date('03-AUG-06','DD-MON-YY') and
> to_date('15-AUG-06','DD-MON-YY')
> group by ir_date
Does moving the order by next to the group by do what you want?
http://download-west.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_10002.htm#i2066419
More examples at
http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:12864646978683
jg
-- @home.com is bogus. http://www.explodingcigar.com/Received on Tue Sep 05 2006 - 18:21:00 CDT