analytic question
Date: Tue, 17 Aug 2010 14:05:45 +0200
Message-ID: <4c6a7b19$0$6825$5fc30a8_at_news.tiscali.it>
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 rangebetween 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 rangebetween 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. Received on Tue Aug 17 2010 - 07:05:45 CDT