analytic question
Date: Wed, 02 Mar 2011 16:54:07 +0100
Message-ID: <4d6e681f$0$30910$5fc30a8_at_news.tiscali.it>
I have a data table like this (Oracle 10.2.0.3.0):
SQL> set null '---'
SQL> select day, value_a, value_b from x where day>=date'2007-01-01'
order by day ;
DAY VALUE_A VALUE_B
---------- ---------- ---------- 03-01-2007 293 ! 24-01-2007 195 ! 06-02-2007 140 ! 20-02-2007 208 ! 02-03-2007 142 ! 20-03-2007 179 ! 03-04-2007 167 ! 17-04-2007 145 ! 07-05-2007 270 ! 14-05-2007 190 ! 22-05-2007 221 ! 05-06-2007 181 ! 18-06-2007 245 ! 10-07-2007 259 ! 19-07-2007 175 ! 09-08-2007 180 ! 20-08-2007 295 ! 10-09-2007 198 ! 20-09-2007 294 ! 04-10-2007 202 ! 23-10-2007 218 ! 12-11-2007 183 ! 16-11-2007 225 14 03-12-2007 167 22 21-12-2007 188 51
First, I have to calculate a running average of value A and value B with different periods. This is easy:
select day, value_a, value_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
from x
where day>=date'2007-01-01'
order by day ;
But I have to calculate two other values: the number of months in period A with non-null values of A and the number of months in period B with non-null values of B.
Unfortunately I cannot use COUNT(DISTINCT ...) :
select
day,
value_a,
value_b,
avg(value_a) over(order by day range between numtodsinterval(89,'day')
preceding and numtodsinterval(0,'day') following) avg_a,
count(distinct(case when value_a is null then null else
trunc(day,'mm') end)) over(order by day range between
numtodsinterval(89,'day') preceding and numtodsinterval(0,'day')
following) cnt_a,
avg(value_b) over(order by day range between numtodsinterval(59,'day')
preceding and numtodsinterval(0,'day') following) avg_b,
count(distinct(case when value_b is null then null else
trunc(day,'mm') end)) over(order by day range between
numtodsinterval(59,'day') preceding and numtodsinterval(0,'day')
following) cnt_b
from x
where day>=date'2007-01-01'
order by day ;
ERROR at line 6:
ORA-30487: ORDER BY not allowed here
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. Received on Wed Mar 02 2011 - 09:54:07 CST