Re: analytic question

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Tue, 17 Aug 2010 06:08:12 -0700 (PDT)
Message-ID: <327186f7-efa2-482b-b257-2ae005d541f7_at_t20g2000yqa.googlegroups.com>

On Aug 17, 8:05 am, yossarian <yossaria..._at_operamail.com> wrote:
> 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.

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 -- Received on Tue Aug 17 2010 - 08:08:12 CDT