Split data for equal group (PL/SQL, SQL)

Question

I have number of id's:

Select count(*) Into count_id From table_I;--4

I know that I have total total_user = 109 (number of records). So I want to split it to equal group:

Select round(count(*)/count_user,0) Into mapUser From table_U;

So I have 4 group. In the first three will be 27 and last should be 28 users.

Now I want to for each group assign the unique ID.

set serveroutput on 
declare 
      CURSOR cur IS Select * From table_U FOR UPDATE OF USER_ID;
      mapUser NUMBER;
      l_rec table_U%rowtype;
      x_mapUser Number := 0;--number between 0-27
      c_obj_id NUMBER := 1;
      count_id NUMBER := 0;

      type T1 is table of number(10) index by binary_integer;
      V1 T1;    

begin
     Select count(*) Into count_id From table_I;--count_id = 4
     Select round(count(*)/count_id,0) Into mapUser From table_U; --mapUser = 27

     SELECT id BULK COLLECT INTO V1 FROM table_I;--it's 4 id (id_1, id_2, id_3, id_4)

    OPEN cur;
        LOOP FETCH cur INTO l_rec;
         EXIT WHEN cur%notfound;

           IF x_mapUser > mapUser Then --0-27 > 27
                x_mapUser := 1;                   
                c_obj_id := c_obj_id +1;--next value from V1
           End if;

          UPDATE table_U SET USER_ID = V1(c_obj_id) WHERE CURRENT OF cur;

         x_mapUser := x_mapUser +1;

        END LOOP;
       CLOSE cur;        
end;

But I don't know how to change my IF and last value from cur assign as well id_4. I'm doing something wrong here :/

Answer 1

This is something which worked for me:

merge into table_u a
using (select rd, i.id
         from (select u.rowid rd, cnt - mod(rownum-1, cnt) rn 
                 from table_u u, (select count(1) cnt from table_i) ) u
         join (select row_number() over( order by id) rn, id from table_i) i using (rn)) b
on (a.rowid = b.rd)         
when matched then update set a.user_id = b.id

My test tables:

create table table_i as (
  select level*10 id from dual connect by level <= 4);

create table table_u as (
  select cast(null as number(3)) user_id, level id from dual connect by level <= 109);

The highest value from second table was assigned 28 times, others 27 times. It's because I used

cnt - mod(rownum-1, cnt) rn

to count joining column. I don't know if it is important for you though. :) The base of this solution is mod() which allows us to cycle between 1 and cnt (in this case 4).

You can do it in PLSQL as you showed, but SQL solutions are typically faster and preferred when possible.