CODEWITHSUNDEEP
sqloracle-databaseplsql
TheMan
TheMan

Reputation: 87

Random allocation of records between two tables

I want to assign the values from Table B to Table A, but so that each record in Table B occurs in the same number of repetitions.

enter image description here

Fiddle SQL

Upvotes: 0

Views: 114

Answers (3)

Ayubxon Ubaydullayev
Ayubxon Ubaydullayev

Reputation: 349

Try this,

with Employees as
 (select Emp, Row_Number() Over(order by 1) Rn
    from B
   cross join (select 1
                from Dual
              connect by level < (select count(1)
                                    from A) / (select count(1)
                                                          from B) + 1)
   order by Dbms_Random.Value),
Colours as
 (select Colour, Rownum Rn
    from A)
select t.Colour, k.Emp
  from Colours t
  join Employees k
    on t.Rn = k.Rn

Upvotes: 0

Popeye
Popeye

Reputation: 35920

You can use the ROWNUM for achieving the same:

SELECT
    COLOUR,
    EMP
FROM
    (
        SELECT
            COLOUR,
            ROWNUM RN
        FROM
            T1
    ) T1,
    (
        SELECT
            EMP,
            ROWNUM RN
        FROM
            T2
    ) T2
WHERE
    MOD(T1.RN, 2) + 1 = T2.RN

Fiddler SQL QUERY

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1270361

You can use window functions for this and mod arithmetic. For simple repetition:

with a as (
      select a.*, rownum as seqnum
      from a
     ),
     b as (
      select b.*, rownum as seqnum, count(*) over () as cnt
      from b
     )
select a.col, b.col
from a join
     b
     on mod(a.seqnum - 1, b.cnt) = b.seqnum - 1;

For more random assignment, randomize the seqnums:

with a as (
      select a.*,
             row_number() over (order by dbms_random.random) as seqnum
      from a
      order by db
     ),
     b as (
      select b.*, count(*) over () as cnt,
             row_number() over (order by dbms_random.random) as seqnum
      from b
     )
select a.col, b.col
from a join
     b
     on mod(a.seqnum - 1, b.cnt) = b.seqnum - 1;

Upvotes: 1

Related Questions