Re: analytic question
From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Tue, 17 Aug 2010 21:18:46 +0200
Message-ID: <4C6AE096.3050509_at_gmail.com>
On 17.08.2010 15:22, yossarian wrote:
> Mark D Powell wrote:
>
>> Why didn't you post the DDL and inserts to create the test data so
>> someone could help you?
>
> Sorry...
>
>> 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
>
> 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.
>
where inv.test_date = inv.last_date
and month_count >= 3
Date: Tue, 17 Aug 2010 21:18:46 +0200
Message-ID: <4C6AE096.3050509_at_gmail.com>
On 17.08.2010 15:22, yossarian wrote:
> Mark D Powell wrote:
>
>> Why didn't you post the DDL and inserts to create the test data so
>> someone could help you?
>
> Sorry...
>
>> 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
>
> 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.
>
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 Received on Tue Aug 17 2010 - 14:18:46 CDT