Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Please ask technical questions in the appropriate category. Thank you!

Amalgamating groups to be beyond a given size threshold

JonWatNov 2 2016 — edited Nov 3 2016

I'm having a hard time thinking of a good way to do this.

I have a bunch of small groups that have a category, and an order within the category. If the groups are below a certain size threshold I want to merge them with the group that is just below them in the order, and keep on doing that until the amalgamated group is beyond the threshold.

For example, I have a table like this and my threshold is 10:

cat gives the category -- there is no merging between categories.

lev gives the ordering within the category.

create table small_groups

(id number(3),

cat varchar2(10),

lev number(2),

grpsize number(3),

final_grp number(3));

insert into small_groups values(1,'Rural',10,2,null);

insert into small_groups values(2,'Rural',9,3,null);

insert into small_groups values(3,'Rural',8,1,null);

insert into small_groups values(4,'Rural',7,4,null);

insert into small_groups values(5,'Rural',6,11,null);

insert into small_groups values(6,'Rural',5,2,null);

insert into small_groups values(7,'Rural',4,2,null);

insert into small_groups values(8,'Rural',3,4,null);

insert into small_groups values(9,'Rural',2,30,null);

insert into small_groups values(10,'Rural',1,12,null);

insert into small_groups values(11,'Urban',10,1,null);

insert into small_groups values(12,'Urban',9,12,null);

insert into small_groups values(13,'Urban',8,2,null);

insert into small_groups values(14,'Urban',7,5,null);

insert into small_groups values(15,'Urban',6,7,null);

insert into small_groups values(16,'Urban',5,15,null);

insert into small_groups values(17,'Urban',4,25,null);

insert into small_groups values(18,'Urban',3,2,null);

insert into small_groups values(19,'Urban',2,1,null);

insert into small_groups values(20,'Urban',1,8,null);

ID 1 has only two people, so it would be merged with the Rural 9 group. That would still only have 5 people, so merge with Rural 8, still only six people, so merge with Rural 7, which is ID #4. That gives me 10 people, so IDs 1 through 4 get their final_grp numbers set to 4.

ID 5 is big enough on its own, so its final_grp is 5.

Ids 7 and 8 become part of 9

10 is fine by itself

11 gets a final_grp of 12,

13 and 14 get a final_grp of 15

16 and 17 are fine by themselves and 18 and 19 get a final_grp of 20.

I think I could probably manage to do this procedurally using PL/SQL, but I suspect there is a clever method using analytic functions that, at the moment, escapes me.

Thanks.

This post has been answered by mathguy on Nov 2 2016
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Dec 1 2016
Added on Nov 2 2016
9 comments
996 views