Re: Complex SQL

Russell Wolfe <wolfer_at_peak.org> wrote in message news:<7sajruoq8hmtvms5ddt3rgj4hg90222srb_at_4ax.com>...
> Ummm,
>
> Steve, I don' think your sql will give Jim the answer he seeks. Your
> statement gives the averages for each date, but Jim wants the
> cumulative average of all past dates up to and including the reported
> date.
>
> Jim, I can't think of a pure sql approach either, certainly not in one
> statement. Is there only one value per date? If so, I'd be tempted
> to add a column to the table to hold the running average, and add a
> pre-insert trigger to compute the value at the time the new row is
> inserted. Even then, I think you'd need a temp table to avoid the
> mutating table error.
>
> If you're working with existing data, I fear a pl/sql function might
> be needed.
>
> hth
>
> rww
>
> On Fri, 25 Oct 2002 12:43:23 -0700, "Steve M"
> <steve.mcdaniels_at_vuinteractive.com> wrote:
>
> >select trunc(the_date) the_date,
> > avg(the_numeric_value) the_average_value
> >from the_table
> >group by trunc(the_date)
> >
> >I'm sure that you are aware that this is covered in the Oracle
> >documentation,
> >but like many of us, you are too lazy to look it up.
> >
> >"Jim" <jschneider_at_kjinteractive.com> wrote in message
> >news:8e6a91e3.0210250837.57983b3b_at_posting.google.com...
> >> Maybe not so complex, but I don't know how to do it.
> >>
> >> My table is setup like this:
> >>
> >> Date Numeric Value
> >> -------------- --------------
> >> 01/02/2002 10
> >> 01/03/2002 17
> >> 01/04/2002 83
> >> ....
> >>
> >> What I'd like to do is to get the average (AVG) of
> >> the "Numeric Value" column at each date interval, with a
> >> single SQL statement. That is, an SQL statement that
> >> returns:
> >>
> >> 01/02/2002 10
> >> 01/03/2002 13.5 [(10 + 17)/2]
> >> 01/04/2002 55 [(10 + 17 + 83)/3]
> >>
> >> Thanks in advance for any help.
> >
>
> Russ Wolfe
> wolfer_at_peak.org

Hmmm, dont know what happened to my earlier post the other day. Anyhow, you can solve this by joining the table with itself using the >= operator as in:

select x1.mydate, avg(x2.myval) from x as x1, x as x2 where x1.mydate >= x2.mydate group by x1.mydate

MYDATE 2
---------- -----------

01/02/2002          10
01/03/2002          13
01/04/2002          36

  3 record(s) selected.

As someone else pointed out, you could also use "olap window functions" as in:

select mydate, avg(myval) over (order by mydate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as val from x

However, if you have several values each date you have to make sure that you only average on complete dates. Not sure about this, but something like:

select distinct mydate, avg(myval) OVER (order by mydate range BETWEEN UNBOUNDED PRECEDING AND CURRENT row) AS val from x

should probably do it. Note that I have no idea whether the last two works with Oracle, but the first one definitely should do so.

HTH
/Lennart Received on Sun Oct 27 2002 - 22:06:26 CET