# analytic question

From: yossarian <yossarian99_at_operamail.com>
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

Original text of this message