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.