Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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
1,047 views