I have a chemical test set, more or less like this:

SUBJECT TEST_DATE RESULT
------- ---------- ------
01 2010-07-23 42
01 2010-08-04 84
01 2010-08-17 21
02 2010-07-25 13
02 2010-08-15 108
03 2010-07-21 25
03 2010-08-16 34

I have to calculate, for each subject, the average of the results of the
last 91 days available.

This is my solution:

select
subject,
last_date,
result_avg
from
(
select
subject,
test_date,
max(test_date) over (partition by subject) last_date,
avg(result) over (partition by subject order by test_date range
between numtodsinterval(91,'day') preceding and numtodsinterval(0,'day')
following) result_avg
from
chemical_tests
) inv
where
inv.test_date = inv.last_date
;

Unfortunately I have another requirement: to include only the subjects
that have at least 1 test in the last 3 months.

This doesn't work, as documented:

select
subject,
last_date,
result_avg
from
(
select
subject,
test_date,
max(test_date) over (partition by subject) last_date,
avg(result) over (partition by subject order by test_date range
between numtodsinterval(91,'day') preceding and numtodsinterval(0,'day')
following) result_avg,
count(distinct trunc(test_date,'month')) over (partition by
subject order by test_date range between numtodsinterval(91,'day')
preceding and numtodsinterval(0,'day') following) month_count
from
chemical_tests
) inv
where
inv.test_date = inv.last_date and
month_count>=3
;

The reason is: "If you specify DISTINCT, then you can specify only the
query_partition_clause of the analytic_clause. The order_by_clause and
windowing_clause are not allowed."

How can I do the same thing without using DISTINCT?

Thank you.

Kind regards, Y.

On Aug 17, 8:05*am, yossarian <yossaria... (AT) operamail (DOT) com> wrote:
Why didn't you post the DDL and inserts to create the test data so
someone could help you?


SQL> drop table testscores;

Table dropped.

SQL>
SQL> create table testscores (subject number, test_date date,
result number);

Table created.

SQL>
SQL> insert into testscores values (01,to_date('2010-07-23','YYYY-MM-
DD'), 42);

1 row created.

SQL> insert into testscores values (01,to_date('2010-08-04','YYYY-MM-
DD'), 84);

1 row created.

SQL> insert into testscores values (01,to_date('2010-08-17','YYYY-MM-
DD'), 21);

1 row created.

SQL> insert into testscores values (02,to_date('2010-07-25','YYYY-MM-
DD'), 13);

1 row created.

SQL> insert into testscores values (02,to_date('2010-08-15','YYYY-MM-
DD'), 108);

1 row created.

SQL> insert into testscores values (03,to_date('2010-07-21','YYYY-MM-
DD'), 25);

1 row created.

SQL> insert into testscores values (03,to_date('2010-08-16','YYYY-MM-
DD'), 34);

1 row created.

SQL> insert into testscores values (04,to_date('2010-01-10','YYYY-MM-
DD'), 80);

1 row created.

SQL>
SQL> select
2 subject,
3 last_date,
4 result_avg
5 from
6 (
7 select
8 subject,
9 test_date,
10 max(test_date) over (partition by subject) last_date,
11 avg(result) over (partition by subject order by test_date
range
12 between numtodsinterval(91,'day') preceding and
numtodsinterval(0,'day')
13 following) result_avg
14 from
15 testscores
16 ) inv
17 where
18 inv.test_date = inv.last_date
19 ;

SUBJECT LAST_DATE RESULT_AVG
---------- --------- ----------
1 17-AUG-10 49
2 15-AUG-10 60.5
3 16-AUG-10 29.5
4 10-JAN-10 80

SQL>
SQL> select
2 subject,
3 last_date,
4 result_avg
5 from
6 (
7 select
8 subject,
9 test_date,
10 max(test_date) over (partition by subject) last_date,
11 avg(result) over (partition by subject order by test_date
range
12 between numtodsinterval(91,'day') preceding and
numtodsinterval(0,'day')
13 following) result_avg
14 from
15 testscores
16 ) inv
17 where
18 test_date >= (trunc(sysdate) - 91) and
19 inv.test_date = inv.last_date
20 /

SUBJECT LAST_DATE RESULT_AVG
---------- --------- ----------
1 17-AUG-10 49
2 15-AUG-10 60.5
3 16-AUG-10 29.5


HTH -- Mark D Powell --

Mark D Powell wrote:

Sorry...

Sorry, my description of the requirement was wrong.

I have to include only the subjects that have at least 1 test in *each
one* of the last 3 months ("last" is relative to the last test date
available for the subject, non to sysdate).

For example: if the last test date of subject 01 is 2010-08-17, then 1
test in August, 3 in July, 2 in June is OK, 1 in August, 0 in July and 2
in June is not.

Thank you.

Kind regards, Y.

On 17.08.2010 15:22, yossarian wrote:
How about

select subject, last_date, result_avg
from (select subject,
test_date,
max(test_date) over(partition by subject) last_date,
avg(result) over(partition by subject
order by test_date
range between numtodsinterval(91, 'day') preceding
and numtodsinterval(0, 'day') following) result_avg,
count(decode(rn,1,1)) over(partition by subject
order by test_date
range between numtodsinterval(91, 'day') preceding
and numtodsinterval(0, 'day') following) month_count
from (select ct.*,
row_number()
over(partition by subject,trunc(test_date,'MM')
order by test_date) rn
from chemical_tests ct)
) inv
where inv.test_date = inv.last_date
and month_count >= 3

?

Btw, you can safely replace "and numtodsinterval(0, 'day') following" by
"and current row" imho.

Best regards

Maxim

Maxim Demenko wrote:

Seems to work well and fast, thank you.

Y.