| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Aggregation Procedure?
In article <aep5im$dqe$1_at_lust.ihug.co.nz>, "Howard says...
>
>HI Thomas,
>
>Appreciate this, since it's small, compact and effective. Trouble is, I
>would like to actually insert a new 9999-plot row if appropriate, or update
>the existing plot's Y agflag to N if it's a singleton. Not just report on
>the same... do real DML. Which is why I though PL/SQL would be required.
>
>Any further insights?
>
9i can do this. We can generate a set that is
we do this by modifying our query to include a "having" clause to get just aggregates (having count(*) > 1 -- these are candidates for insertion) or singletons whose aggflag is Y (these are ones that need to be updated). Once we have those, we'll only keep
Then, we merge. Here is the entire thing:
ops$tkyte_at_ORA9I.WORLD> select * from t;
AREA SITE FTR PLOT SQUARE_MTR A ---------- ---------- ----- ---------- ---------- -
1 BRITPK BR1 1 10 Y
1 BRITPK BR1 2 10 Y
1 BRITPK BR1 3 15 N
1 BRITPK BS1 1 5 Y
1 BRITPK BS1 2 7 Y
1 ANDSO GR1 1 100 Y
1 ANDSO GR1 2 120 Y
1 ANDSO BR1 1 150 Y
8 rows selected.
that is what we start with, then we merge:
ops$tkyte_at_ORA9I.WORLD>
ops$tkyte_at_ORA9I.WORLD> merge into t
2 using
3 ( select *
4 from (select area, site, ftr, 5 decode( count(*), 1, max( plot ), '9999' ) plot, 6 sum(square_mtr) square_mtr, 'N' aggflag, 7 count(*) cnt 8 from t 9 group by area, site, ftr, decode( aggflag, 'Y', 9999, plot ) 10 having count(*) > 1 11 or ( count(*) = 1 and max(aggflag) = 'Y' ) 12 ) a 13 where not exists ( select null 14 from t 15 where t.area = a.area 16 and t.site = a.site 17 and t.ftr = a.ftr 18 and t.plot = a.plot) 19 or cnt = 1
21 on ( t.area = t2.area and 22 t.site = t2.site and 23 t.ftr = t2.ftr and 24 t.plot = t2.plot ) 25 when matched then 26 update set t.aggflag = t2.aggflag27 when not matched then
4 rows merged.
Note that only 4 rows were merged. We in fact had 3 to insert and one to update.
ops$tkyte_at_ORA9I.WORLD> select * from t;
AREA SITE FTR PLOT SQUARE_MTR A ---------- ---------- ----- ---------- ---------- -
1 BRITPK BR1 1 10 Y
1 BRITPK BR1 2 10 Y
1 BRITPK BR1 3 15 N
1 BRITPK BS1 1 5 Y
1 BRITPK BS1 2 7 Y
1 ANDSO GR1 1 100 Y
1 ANDSO GR1 2 120 Y
1 ANDSO BR1 1 150 N
1 ANDSO GR1 9999 220 N
1 BRITPK BR1 9999 20 N
1 BRITPK BS1 9999 12 N
11 rows selected.
I believe that is the set you wanted to generate -- the three new rows and the one ANDSO row's aggflag updated...
If we rerun the merge right now:
ops$tkyte_at_ORA9I.WORLD>
ops$tkyte_at_ORA9I.WORLD> merge into t
2 using
3 ( select *
4 from (select area, site, ftr, 5 decode( count(*), 1, max( plot ), '9999' ) plot, 6 sum(square_mtr) square_mtr, 'N' aggflag, 7 count(*) cnt 8 from t 9 group by area, site, ftr, decode( aggflag, 'Y', 9999, plot ) 10 having count(*) > 1 11 or ( count(*) = 1 and max(aggflag) = 'Y' ) 12 ) a 13 where not exists ( select null 14 from t 15 where t.area = a.area 16 and t.site = a.site 17 and t.ftr = a.ftr 18 and t.plot = a.plot) 19 or cnt = 1
21 on ( t.area = t2.area and 22 t.site = t2.site and 23 t.ftr = t2.ftr and 24 t.plot = t2.plot ) 25 when matched then 26 update set t.aggflag = t2.aggflag27 when not matched then
0 rows merged.
We can see it finds no new rows to insert or existing rows to update...
ops$tkyte_at_ORA9I.WORLD> select * from t;
AREA SITE FTR PLOT SQUARE_MTR A ---------- ---------- ----- ---------- ---------- -
1 BRITPK BR1 1 10 Y
1 BRITPK BR1 2 10 Y
1 BRITPK BR1 3 15 N
1 BRITPK BS1 1 5 Y
1 BRITPK BS1 2 7 Y
1 ANDSO GR1 1 100 Y
1 ANDSO GR1 2 120 Y
1 ANDSO BR1 1 150 N
1 ANDSO GR1 9999 220 N
1 BRITPK BR1 9999 20 N
1 BRITPK BS1 9999 12 N
11 rows selected.
>Regards
>HJR
>
>
>"Thomas Kyte" <tkyte_at_oracle.com> wrote in message
>news:aeo0sl02qvu_at_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
>>
>
>
-- 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 CorpReceived on Wed Jun 19 2002 - 07:02:26 CDT
 |
 |