So, by "return the ratio", I'm assuming you mean:
- Scale up the ratio column in table_x based on the number of rows in table_y
If so, you can do this by:
- Summing the ratios in table_x
- Counting the rows in table_y
- Dividing the results of count / sum
- Return the number of rows for each name < this multiplied by the ratio
You can use analytic functions to do the sum, count and assign the row number:
create table table_x (
name varchar2(1),
ratio int
);
insert into table_x values ('A', 1);
insert into table_x values ('B', 1);
insert into table_x values ('C', 2);
create table table_y (
name varchar2(1)
);
insert into table_y values ('A');
insert into table_y values ('A');
insert into table_y values ('A');
insert into table_y values ('B');
insert into table_y values ('B');
insert into table_y values ('B');
insert into table_y values ('B');
insert into table_y values ('C');
insert into table_y values ('C');
insert into table_y values ('C');
insert into table_y values ('C');
insert into table_y values ('C');
insert into table_y values ('C');
with r1 as (
select table_x.*,
sum(ratio) over () sm
from table_x
), r2 as (
select table_y.*,
count(*) over () ct,
row_number() over (partition by name order by name) rn
from table_y
)
select r2.name, r2.rn
from r1
join r2
on r1.name = r2.name
where (r2.ct/r1.sm * ratio) > rn;
N RN
- ----------
A 1
A 2
A 3
B 1
B 2
B 3
C 1
C 2
C 3
C 4
C 5
C 6
12 rows selected