Re: tough SELECT statement (Absolute Deviation)...

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 14 Jan 2003 07:32:48 -0800
Message-ID: <b01aj00v3o@drn.newsguy.com>

In article <b00qu4$k7s67$1_at_ID-114658.news.dfncis.de>, "Giovanni says...
>
>Hello all,
>
>I am developing a simple tool for generating the required DDL and DML
>for building and maintaining analytic tables to achieve Data Mining. The
>automatically generated tables will contain lot of columns as result of
>applying
>several different aggregation functions on each different grouping criteria
>e.g.
>(Account_ID, Person_ID, Month).
>
>The problem is that the SQL statements that feed those tables should
>be as simple and self contained as possible e.g. would be much prefered
>that the SELECT query which generates each column does not imply
>introducing special clauses in the global FROM or WHERE clauses, so
>perhaps Analytic functions would be a very good candidate.
>
>The concrete problem I currently have is that one of the columns required
>is the not natively supported "Absolute Deviation" i.e.
>sum(abs(amount) - avg(amount)) and this statement is not accepted because
>it has nested aggregations sum(...avg()).
>
>**INCORRECT**
>
>SELECT
>ACCOUNT_ID,
>to_char(transactiondate,'YYYYMM') AS MONTH,
>(sum(abs(amount - avg(amount)))) AS ABSDEV_AMOUNT,
>(max(transactiondate) - min(transactiondate)) AS INTERVAL_DATE
>FROM transactions
>GROUP BY ACCOUNT_ID, to_char(transactiondate,'YYYYMM');
>
>**CORRECT**
>
>SELECT
>t1.ACCOUNT_ID,
>to_char(t1.transactiondate,'YYYYMM') AS MONTH,
>(sum(abs(t1.amount - t2.AVERAGE_AMOUNT))) AS ABSDEV_AMOUNT,
>(max(t1.transactiondate) - min(t1.transactiondate)) AS INTERVAL_DATE
>FROM transactions t1, ( SELECT
> ACCOUNT_ID,
> to_char(transactiondate,'YYYYMM') AS MONTH,
> avg(amount) AS AVERAGE_AMOUNT
> FROM transactions
> GROUP BY ACCOUNT_ID, to_char(transactiondate,'YYYYMM')) t2
>GROUP BY t1.ACCOUNT_ID, to_char(t1.transactiondate,'YYYYMM');
>

are you missing a where clause in there?

  GROUP BY ACCOUNT_ID, to_char(transactiondate,'YYYYMM')) t2 where t1.account_id = t2.account_id and to_char(t1.transactiondate,'yyyymm') = t2.month
GROUP BY t1.ACCOUNT_ID, to_char(t1.transactiondate,'YYYYMM');

else you seem to have a cartesian product. If you are, then you can use analytics like this:

select account_id, month, sum( abs(amount-avg_amt) ) absdev_amount,

       max(transactiondate) - min(transactiondate) interval_date   from (select account_id,

               trunc(transactiondate,'mm') month,
               amount,
avg(amount) over (partition by account_id,trunc(transactiondate,'mm')) avg_amt,
               transactiondate
          from transactions )

 group by account_id, month
/

to achieve the same effect.

>Any suggestions?
>
>TIA,
>Best regards,
>Giovanni
>
>
>

--
Thomas Kyte (tkyte@oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Tue Jan 14 2003 - 09:32:48 CST