Re: analytic question

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Thu, 03 Mar 2011 20:00:08 +0100
Message-ID: <4D6FE538.8050001_at_gmail.com>

On 02.03.2011 16:54, yossarian wrote:
> I have a data table like this (Oracle 10.2.0.3.0):

> Some time ago Maxim Demenko suggested a nice trick, but unfortunately it
> works only at row level, and it ignores the fact that A and B can be NULL:
>
> select
> day, value_a a,value_b b,
> avg(value_a) over(order by day range between numtodsinterval(89,'day')
> preceding and numtodsinterval(0,'day') following) avg_a,
> avg(value_b) over(order by day range between numtodsinterval(59,'day')
> preceding and numtodsinterval(0,'day') following) avg_b,
> count(decode(rn_a,1,1)) over(order by day range between
> numtodsinterval(89,'day') preceding and numtodsinterval(0,'day')
> following) months_a,
> count(decode(rn_b,1,1)) over(order by day range between
> numtodsinterval(59,'day') preceding and numtodsinterval(0,'day')
> following) months_b
> from (
> select
> day,
> value_a,
> value_b,
> row_number() over(partition by trunc(day,'MM') order by day) rn_a,
> row_number() over(partition by trunc(day,'MM') order by day) rn_b
> from x
> where day>=date'2007-01-01'
> )
> order by day
>
> DAY A B AVG_A AVG_B CNT_A CNT_B
> ---------- ---- ---- ---------- ---------- ---------- ----------
> 03-01-2007 293 ! 293 ! 1 1
> 24-01-2007 195 ! 244 ! 1 1
> 06-02-2007 140 ! 209.333333 ! 2 2
> 20-02-2007 208 ! 209 ! 2 2
> 02-03-2007 142 ! 195.6 ! 3 3
> 20-03-2007 179 ! 192.833333 ! 3 2
> 03-04-2007 167 ! 171.833333 ! 3 3
> 17-04-2007 145 ! 168 ! 3 2
> 07-05-2007 270 ! 185.166667 ! 3 2
> 14-05-2007 190 ! 185.857143 ! 3 2
> 22-05-2007 221 ! 187.714286 ! 3 2
> 05-06-2007 181 ! 193.285714 ! 3 2
> 18-06-2007 245 ! 202.714286 ! 3 2
> 10-07-2007 259 ! 215.857143 ! 3 2
> 19-07-2007 175 ! 220.142857 ! 3 2
> 09-08-2007 180 ! 207.285714 ! 3 2
> 20-08-2007 295 ! 222.5 ! 3 2
> 10-09-2007 198 ! 225.333333 ! 3 2
> 20-09-2007 294 ! 233.5 ! 3 2
> 04-10-2007 202 ! 229 ! 4 3
> 23-10-2007 218 ! 231.166667 ! 3 2
> 12-11-2007 183 ! 231.666667 ! 3 2
> 16-11-2007 225 14 230.714286 14 3 2
> 03-12-2007 167 22 212.428571 18 4 2
> 21-12-2007 188 51 197.166667 29 3 2
>
> Any suggestion?
>
> Thank you.
>
> Kind regards, Y.

Not sure, how the end result should look like, maybe this modification would work for you?

select
day, value_a a,value_b b,
avg(value_a) over(order by day range between numtodsinterval(89,'day') preceding and numtodsinterval(0,'day') following) avg_a, avg(value_b) over(order by day range between numtodsinterval(59,'day') preceding and numtodsinterval(0,'day') following) avg_b, count(decode(rn_a,1,1)) over(order by day range between numtodsinterval(89,'day') preceding and numtodsinterval(0,'day') following) months_a,
count(decode(rn_b,1,1)) over(order by day range between numtodsinterval(59,'day') preceding and numtodsinterval(0,'day') following) months_b
from (
select

   day,
   value_a,
   value_b,
   decode(value_a,null,null,row_number() over(partition by trunc(day,'MM'),nvl2(value_a,1,null) order by day)) rn_a,

   decode(value_b,null,null,row_number() over(partition by trunc(day,'MM'),nvl2(value_b,1,null) order by day)) rn_b from x
where day>=date'2007-01-01'
)
order by day
/

Best regards

Maxim Received on Thu Mar 03 2011 - 13:00:08 CST