Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: sliding aggregates

Re: sliding aggregates

From: JTommaney <cdos_jtommaney_at_comcast.net>
Date: 21 Oct 2005 07:09:35 -0700
Message-ID: <1129903773.443558.264220@g49g2000cwa.googlegroups.com>


> How can I specified where clause to meet the need?

To help get the best response you may want to include your Oracle version as well as additional detail regarding the actual date values. Also, it wasn't entirely clear whether you wanted to represent 'days' with no rows.

One approach is analytic functions. It wasn't clear from your example whether your date values were truncated or not. This was tested on 9.2.0.6.0.

This example will provide an avg based on the last 5 date values. Whether those are 5 distinct days depends on the rows entered. There is alternate syntax to support window/range based on a date range, but I read your question as you wanted exactly the previous 5 entries.

create table AI3
as select sysdate - rownum ai3_date,

        rownum ai3_index
from all_objects where rownum < 10;

select ai3_date,
  avg(ai3_index) over(order by dt_rn asc rows 4 preceding) avg_ai3_index,
  count(ai3_index) over(order by dt_rn asc rows 4 preceding) cnt_ai3_index
from (select AI3_DATE,

	row_number() over(order by AI3_DATE) dt_rn,
	ai3_index

  from AI3 )
order by 1 desc; Received on Fri Oct 21 2005 - 09:09:35 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US