analytic question

From: yossarian <yossarian99_at_operamail.com>
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 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. Received on Tue Aug 17 2010 - 07:05:45 CDT

Original text of this message