Re: Aggregation Procedure?

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 18 Jun 2002 12:15:33 -0700
Message-ID: <aeo0sl02qvu@drn.newsguy.com>

In article <aen6t2$kdc$1_at_lust.ihug.co.nz>, "Howard says...
>
>Calling all PL/SQL gurus (since I'm not one).
>
>Here's my data:
>
>AREA SITE FTR Plot SQUARE_MTR AGGFLAG
>1 BRITPK BR1 1 10.00 Y
>1 BRITPK BR1 2 10.00 Y
>1 BRITPK BR1 3 15.00 N
>1 BRITPK BS1 1 5.00 Y
>1 BRITPK BS1 2 7.00 Y
>1 ANDSO GR1 1 100.00 Y
>1 ANDSO GR1 2 120.00 Y
>1 ANDSO BR1 1 150.00 Y
>
>In other words, sites contain multiple features. The Primary Key is
>area||site||ftr||plot.
>
>I want to write a procedure that aggregates similar features on a site into
>a single 'aggregate plot', provided the real features' agflags are set to Y.
>Aggregate plots are identified with a '9999' plot number, and an agflag of
>"N" (since aggregates can't themselves be aggregated). Plots which are not
>to be aggregated (or which can't be because there is nothing to aggregate
>them with) just have their Aggflag set to "N".
>
>Aggregating the above data following that rule should yield the following
>(missing out the original plots for clarity's sake, unless they are
>non-aggregateable):
>
>AREA SITE FTR Plot SQUARE_MTR AGGFLAG
>1 BRITPK BR1 3 15.00 N
>1 BRITPK BR1 9999 20.00 N
>1 BRITPK BS1 9999 12.00 N
>1 ANDSO GR1 9999 220.00 N
>1 ANDSO BR1 1 150.00 N
>
>Note Plot 3 on BRITPK escaped aggregation because its aggflag was "N". The
>BR1 on ANDSO also escaped, because it was the only plot of that feature on
>the site.
>
>I wrote something in Visual Basic (!!) years ago to achieve something
>similar, and it involved searching through the table in AREA+SITE+FTR order,
>noting when there was a change, and bunging in the totals where appropriate.
>
>But I'd like to do it in PL/SQL... so if anyone has any ideas, I'd be
>grateful.
>
>Regards
>HJR
>
>

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> select area,

  2             site,
  3             ftr,
  4             decode( count(*), 1, max( plot ), '9999' ) plot,
  5             sum(square_mtr),
  6             'N'

  7 from t
  8 group by area, site, ftr, decode( aggflag, 'Y', 9999, plot )   9 /

      AREA SITE FTR PLOT SUM(SQUARE_MTR) ' ---------- ---------- ----- ---------- --------------- -

         1 ANDSO      BR1            1             150 N
         1 ANDSO      GR1         9999             220 N
         1 BRITPK     BR1            3              15 N
         1 BRITPK     BR1         9999              20 N
         1 BRITPK     BS1         9999              12 N

may not be intuitive ;)

but it works...

we group by area, site, ftr AND IF aggflag=Y -- a constant, else the rest of the primary key. That "folds" all of the records to be aggregatd together. Then, we can use

  4 decode( count(*), 1, max( plot ), '9999' ) plot,

to either

o return 9999 if we actually aggregated. o max(plot) -- which is just really plot -- if we did not

--
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 Jun 18 2002 - 14:15:33 CDT