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.

yossarian wrote:
Is this a valid syntax for Oracle?

On Mar 2, 9:03*am, "Gerard H. Pille" <g... (AT) skynet (DOT) be> wrote:
TTST> alter session set nls_date_format="MM-DD-YYYY";

Session altered.

TTST> select date'2007-01-01' from dual;

DATE'2007-
----------
01-01-2007


Learn something new every day. Though this one sounds kind of
familiar.

jg
--
@home.com is bogus.
Indian musical chairs: http://www.thehindubusinessline.com/...cle1503683.ece

Gerard H. Pille wrote:

Undocumented but valid AFAIK.

Y.

On Mar 3, 9:15*am, yossarian <yossaria... (AT) operamail (DOT) com> wrote:
This is ANSI SQL. Most databases will accept this form as a valid
sentence.

Cheers.

Carlos.

On Mar 3, 3:15*am, yossarian <yossaria... (AT) operamail (DOT) com> wrote:
Documented for quite a while I think:
http://download.oracle.com/docs/cd/B....htm#sthref365

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

Co-author of the "OakTable Expert Oracle Practices" book:
http://www.apress.com/book/view/1430226684
http://www.amazon.com/Expert-Oracle-.../dp/1430226684

Randolf Geist wrote:

Mea culpa. I was never able to find it.

Y.

On 02.03.2011 16:54, yossarian wrote:
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

Maxim Demenko wrote:

Works like a charm as usual, Maxim. Thank you.

Kind regards

Y.