Home » SQL & PL/SQL » SQL & PL/SQL » select based on ratio from second table (Oracle, 11.2.0.4 )
select based on ratio from second table [message #650930] |
Sat, 07 May 2016 07:14 |
|
merlin1989
Messages: 5 Registered: May 2016
|
Junior Member |
|
|
Hey,
I would need a query which fetches x rows based on a ratio in a field of a second table.
So I have two tables, TABLE_X and TABLE_Y, TABLE_X holds 3 or more rows:
TABLE_Y has n rows with each row matching the name of TABLE_X:
test1 test2 name test3
_____________________________
test1 test2 A test3
test1 test2 A test3
test1 test2 A test3
test1 test2 B test3
test1 test2 B test3
test1 test2 B test3
test1 test2 B test3
test1 test2 C test3
test1 test2 C test3
test1 test2 C test3
test1 test2 C test3
test1 test2 C test3
test1 test2 C test3
so in this case I have a ratio of 1:1:2, C has 6 rows so I am allowed to fetch 3 A and 3 B. Now I need a query which allows me to fetch based on the ratio provided by TABLE_X .
Do you have any suggestions?
[mod-edit: code tags added by bb; next time please add them yourself]
[Updated on: Sat, 07 May 2016 14:00] by Moderator Report message to a moderator
|
|
|
|
Re: select based on ratio from second table [message #650934 is a reply to message #650930] |
Sat, 07 May 2016 08:18 |
|
merlin1989
Messages: 5 Registered: May 2016
|
Junior Member |
|
|
Hi, here is a test case with the two tables:
create table ratios (
name varchar2(400) not null,
ratio number not null
);
/
create table test_data (
column_1 varchar2(400) not null,
column_2 varchar2(400) not null,
name varchar2(400),
);
/
insert into ratios values ( 'A',1);
insert into ratios values ( 'B',2);
insert into ratios values ( 'C',4);
insert into test_data values ( 'test_a_col_1_1', 'test_a_col_2_1', 'A');
insert into test_data values ( 'test_a_col_1_2', 'test_a_col_2_2', 'A');
insert into test_data values ( 'test_b_col_1_1', 'test_b_col_2_1', 'B');
insert into test_data values ( 'test_b_col_1_2', 'test_b_col_2_2', 'B');
insert into test_data values ( 'test_b_col_1_3', 'test_b_col_2_3', 'B');
insert into test_data values ( 'test_c_col_1_1', 'test_c_col_2_1', 'C');
insert into test_data values ( 'test_c_col_1_2', 'test_c_col_2_2', 'C');
insert into test_data values ( 'test_c_col_1_3', 'test_c_col_2_3', 'C');
insert into test_data values ( 'test_c_col_1_4', 'test_c_col_2_4', 'C');
insert into test_data values ( 'test_c_col_1_5', 'test_c_col_2_5', 'C');
and now if ratio for A:B:C is 1:2:4 I need to retrieve 2x more rows of B than A and 4x more rows of C than A and but still 2x more rows of C than B.
|
|
|
|
Re: select based on ratio from second table [message #650936 is a reply to message #650935] |
Sat, 07 May 2016 10:25 |
|
merlin1989
Messages: 5 Registered: May 2016
|
Junior Member |
|
|
Oh I am sorry, I totally overread it, I am not able to edit my post, so I post the formatted code here.
to your question "Which ones?": It doesn't matter which, just the count matters because it hast to be in the ratio.
The result should bed the whole rows from table "test_data" matching the ration on name from ratios table.
CREATE TABLE ratios
(
name VARCHAR2(400) NOT NULL,
ratio number NOT NULL
);/
CREATE TABLE test_data
(
column_1 varchar2(400) NOT NULL,
column_2 varchar2(400) NOT NULL,
name varchar2(400),
);/
INSERT INTO ratios VALUES
(
'A',
1
);
INSERT INTO ratios VALUES
(
'B',
2
);
INSERT INTO ratios VALUES
(
'C',
4
);
INSERT INTO test_data VALUES
(
'test_a_col_1_1',
'test_a_col_2_1',
'A'
);
INSERT INTO test_data VALUES
(
'test_a_col_1_2',
'test_a_col_2_2',
'A'
);
INSERT INTO test_data VALUES
(
'test_b_col_1_1',
'test_b_col_2_1',
'B'
);
INSERT INTO test_data VALUES
(
'test_b_col_1_2',
'test_b_col_2_2',
'B'
);
INSERT INTO test_data VALUES
(
'test_b_col_1_3',
'test_b_col_2_3',
'B'
);
INSERT INTO test_data VALUES
(
'test_c_col_1_1',
'test_c_col_2_1',
'C'
);
INSERT INTO test_data VALUES
(
'test_c_col_1_2',
'test_c_col_2_2',
'C'
);
INSERT INTO test_data VALUES
(
'test_c_col_1_3',
'test_c_col_2_3',
'C'
);
INSERT INTO test_data VALUES
(
'test_c_col_1_4',
'test_c_col_2_4',
'C'
);
INSERT INTO test_data VALUES
(
'test_c_col_1_5',
'test_c_col_2_5',
'C'
);
|
|
|
|
|
|
|
|
|
Re: select based on ratio from second table [message #650947 is a reply to message #650945] |
Sat, 07 May 2016 13:20 |
|
merlin1989
Messages: 5 Registered: May 2016
|
Junior Member |
|
|
Quote:So what are you looking for? You don't make it easy, you know. You don't say "thank you for trying to assist" either.
I am sorry I was a bit in a hurry when writing that, I really appreciate the help of all of you.
I will do my best to describe it again:
I have a table which has stored a ratio between names stored, the names and the actual data are in a second table. Now I would need a statement to query rows from the second table ( table name is test_data in the example) by the ratio mentioned before. If the ratio cannot be fulfilled I need the nearest possible to it.
Example 1 (ratio is always shown as A:B:C):
ratio: 2:4:8
table holds:
10 rows A
20 rows B
40 rows C
query should return: 10 rows A, 20 rows B and 40 rows C because the ratio matches the rows stored in the table.
Example 2 (ratio is always shown as A:B:C):
ratio: 10:20:30
table holds:
10 rows A
20 rows B
40 rows C
query should return: 10 rows A, 20 rows B and 30 rows C, 10 rows of C are not in the result because it would not match the ratio then.
Example 3 (ratio is always shown as A:B:C):
ratio: 10:20:30
table holds:
5 rows A
10 rows B
20 rows C
query should return: 5 rows A, 10 rows B and 15 rows C, 5 rows of C are not in the result because it would not match the ratio then.
Example 4 (ratio is always shown as A:B:C):
ratio: 0.2:0.4:0.8
table holds:
5 rows A
10 rows B
15 rows C
query should return: 5 rows A, 10 rows B and 15 rows C, all rows returned because ratio matches.
If the calculation would return a half row it should get round up, so in case 20,5 rows would be needed, 21 would be the row count to return.
Quote:
And for C?
Show us for ALL The cases we mentioned? ALL OF THEM!
I hope you can now better understand what I need.
Thank you all for your help!
|
|
|
|
Re: select based on ratio from second table [message #650949 is a reply to message #650947] |
Sat, 07 May 2016 14:50 |
John Watson
Messages: 8929 Registered: January 2010 Location: Global Village
|
Senior Member |
|
|
It would really help if you gave a proper example. Have you written any code at all? So far, the only attempt I see is my query, which you rejected.
Is it correct to say that you want every A row, and multiply from there? Are A, B, and C the only possibilities?
|
|
|
Re: select based on ratio from second table [message #650950 is a reply to message #650949] |
Sat, 07 May 2016 17:54 |
|
Barbara Boehmer
Messages: 9088 Registered: November 2002 Location: California, USA
|
Senior Member |
|
|
My understanding of the problem is that the ratios are more like proportions of rows, so ratios of 1A, 2B, and 4C would be like 1 row A for every 2 rows B and every 4 rows C, so that each complete set must have a total of 7 such rows and there could be any number of these sets of rows. Also, as these are just proportions, 10A, 20B, and 40C would be the same as 1A, 2B, and 4C. The following is the closest that I have come. It may not be exact when it comes to fractions and there may be some extra unnecessary stuff and there may be a simpler way, but it seems to work for the data provided. In the tests below, I have used the same values in the test_data table that were provided with various values in the ratios table.
SCOTT@orcl_12.1.0.2.0> COLUMN column_1 FORMAT A14
SCOTT@orcl_12.1.0.2.0> COLUMN column_2 FORMAT A14
SCOTT@orcl_12.1.0.2.0> COLUMN name FORMAT A4
SCOTT@orcl_12.1.0.2.0> SELECT * FROM test_data
2 /
COLUMN_1 COLUMN_2 NAME
-------------- -------------- ----
test_a_col_1_1 test_a_col_2_1 A
test_a_col_1_2 test_a_col_2_2 A
test_b_col_1_1 test_b_col_2_1 B
test_b_col_1_2 test_b_col_2_2 B
test_b_col_1_3 test_b_col_2_3 B
test_c_col_1_1 test_c_col_2_1 C
test_c_col_1_2 test_c_col_2_2 C
test_c_col_1_3 test_c_col_2_3 C
test_c_col_1_4 test_c_col_2_4 C
test_c_col_1_5 test_c_col_2_5 C
10 rows selected.
SCOTT@orcl_12.1.0.2.0> create table ratios (
2 name varchar2(400) not null,
3 ratio number not null
4 );
Table created.
SCOTT@orcl_12.1.0.2.0> insert into ratios values ( 'A',1);
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into ratios values ( 'B',1);
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into ratios values ( 'C',2);
1 row created.
SCOTT@orcl_12.1.0.2.0> SELECT DISTINCT column_1, column_2, name
2 FROM (SELECT column_1, column_2, name, ratio, tot, rn, dr,
3 COUNT (*) OVER (PARTITION BY rn, name) name_cnt,
4 COUNT (*) OVER (PARTITION BY rn) cnt
5 FROM (SELECT column_1, column_2, name, ratio, tot, rn, dr
6 FROM (SELECT d.column_1, d.column_2, d.name, r.ratio, r.tot,
7 DENSE_RANK () OVER (PARTITION BY d.name ORDER BY d.ROWID) dr
8 FROM test_data d,
9 (SELECT name,
10 ratio / (MIN (ratio) OVER (PARTITION BY NULL)) ratio,
11 (SUM (ratio) OVER (PARTITION BY NULL)) /
12 (MIN (ratio) OVER (PARTITION BY NULL)) tot
13 FROM ratios) r
14 WHERE d.name = r.name),
15 (SELECT ROWNUM rn
16 FROM DUAL
17 CONNECT BY LEVEL <=
18 (SELECT MIN (COUNT (*))
19 FROM test_data
20 GROUP BY name)) t
21 WHERE dr <= ratio * t.rn))
22 WHERE MOD (cnt, tot) = 0
23 AND MOD (name_cnt, ratio) = 0
24 ORDER BY name, column_1, column_2
25 /
COLUMN_1 COLUMN_2 NAME
-------------- -------------- ----
test_a_col_1_1 test_a_col_2_1 A
test_a_col_1_2 test_a_col_2_2 A
test_b_col_1_1 test_b_col_2_1 B
test_b_col_1_2 test_b_col_2_2 B
test_c_col_1_1 test_c_col_2_1 C
test_c_col_1_2 test_c_col_2_2 C
test_c_col_1_3 test_c_col_2_3 C
test_c_col_1_4 test_c_col_2_4 C
8 rows selected.
SCOTT@orcl_12.1.0.2.0> delete from ratios;
3 rows deleted.
SCOTT@orcl_12.1.0.2.0> insert into ratios values ( 'A',1);
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into ratios values ( 'B',2);
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into ratios values ( 'C',4);
1 row created.
SCOTT@orcl_12.1.0.2.0> SELECT DISTINCT column_1, column_2, name
2 FROM (SELECT column_1, column_2, name, ratio, tot, rn, dr,
3 COUNT (*) OVER (PARTITION BY rn, name) name_cnt,
4 COUNT (*) OVER (PARTITION BY rn) cnt
5 FROM (SELECT column_1, column_2, name, ratio, tot, rn, dr
6 FROM (SELECT d.column_1, d.column_2, d.name, r.ratio, r.tot,
7 DENSE_RANK () OVER (PARTITION BY d.name ORDER BY d.ROWID) dr
8 FROM test_data d,
9 (SELECT name,
10 ratio / (MIN (ratio) OVER (PARTITION BY NULL)) ratio,
11 (SUM (ratio) OVER (PARTITION BY NULL)) /
12 (MIN (ratio) OVER (PARTITION BY NULL)) tot
13 FROM ratios) r
14 WHERE d.name = r.name),
15 (SELECT ROWNUM rn
16 FROM DUAL
17 CONNECT BY LEVEL <=
18 (SELECT MIN (COUNT (*))
19 FROM test_data
20 GROUP BY name)) t
21 WHERE dr <= ratio * t.rn))
22 WHERE MOD (cnt, tot) = 0
23 AND MOD (name_cnt, ratio) = 0
24 ORDER BY name, column_1, column_2
25 /
COLUMN_1 COLUMN_2 NAME
-------------- -------------- ----
test_a_col_1_1 test_a_col_2_1 A
test_b_col_1_1 test_b_col_2_1 B
test_b_col_1_2 test_b_col_2_2 B
test_c_col_1_1 test_c_col_2_1 C
test_c_col_1_2 test_c_col_2_2 C
test_c_col_1_3 test_c_col_2_3 C
test_c_col_1_4 test_c_col_2_4 C
7 rows selected.
SCOTT@orcl_12.1.0.2.0> delete from ratios;
3 rows deleted.
SCOTT@orcl_12.1.0.2.0> insert into ratios values ( 'A',4);
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into ratios values ( 'B',4);
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into ratios values ( 'C',2);
1 row created.
SCOTT@orcl_12.1.0.2.0> SELECT DISTINCT column_1, column_2, name
2 FROM (SELECT column_1, column_2, name, ratio, tot, rn, dr,
3 COUNT (*) OVER (PARTITION BY rn, name) name_cnt,
4 COUNT (*) OVER (PARTITION BY rn) cnt
5 FROM (SELECT column_1, column_2, name, ratio, tot, rn, dr
6 FROM (SELECT d.column_1, d.column_2, d.name, r.ratio, r.tot,
7 DENSE_RANK () OVER (PARTITION BY d.name ORDER BY d.ROWID) dr
8 FROM test_data d,
9 (SELECT name,
10 ratio / (MIN (ratio) OVER (PARTITION BY NULL)) ratio,
11 (SUM (ratio) OVER (PARTITION BY NULL)) /
12 (MIN (ratio) OVER (PARTITION BY NULL)) tot
13 FROM ratios) r
14 WHERE d.name = r.name),
15 (SELECT ROWNUM rn
16 FROM DUAL
17 CONNECT BY LEVEL <=
18 (SELECT MIN (COUNT (*))
19 FROM test_data
20 GROUP BY name)) t
21 WHERE dr <= ratio * t.rn))
22 WHERE MOD (cnt, tot) = 0
23 AND MOD (name_cnt, ratio) = 0
24 ORDER BY name, column_1, column_2
25 /
COLUMN_1 COLUMN_2 NAME
-------------- -------------- ----
test_a_col_1_1 test_a_col_2_1 A
test_a_col_1_2 test_a_col_2_2 A
test_b_col_1_1 test_b_col_2_1 B
test_b_col_1_2 test_b_col_2_2 B
test_c_col_1_1 test_c_col_2_1 C
5 rows selected.
SCOTT@orcl_12.1.0.2.0> delete from ratios;
3 rows deleted.
SCOTT@orcl_12.1.0.2.0> insert into ratios values ( 'A',2);
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into ratios values ( 'B',4);
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into ratios values ( 'C',8);
1 row created.
SCOTT@orcl_12.1.0.2.0> SELECT DISTINCT column_1, column_2, name
2 FROM (SELECT column_1, column_2, name, ratio, tot, rn, dr,
3 COUNT (*) OVER (PARTITION BY rn, name) name_cnt,
4 COUNT (*) OVER (PARTITION BY rn) cnt
5 FROM (SELECT column_1, column_2, name, ratio, tot, rn, dr
6 FROM (SELECT d.column_1, d.column_2, d.name, r.ratio, r.tot,
7 DENSE_RANK () OVER (PARTITION BY d.name ORDER BY d.ROWID) dr
8 FROM test_data d,
9 (SELECT name,
10 ratio / (MIN (ratio) OVER (PARTITION BY NULL)) ratio,
11 (SUM (ratio) OVER (PARTITION BY NULL)) /
12 (MIN (ratio) OVER (PARTITION BY NULL)) tot
13 FROM ratios) r
14 WHERE d.name = r.name),
15 (SELECT ROWNUM rn
16 FROM DUAL
17 CONNECT BY LEVEL <=
18 (SELECT MIN (COUNT (*))
19 FROM test_data
20 GROUP BY name)) t
21 WHERE dr <= ratio * t.rn))
22 WHERE MOD (cnt, tot) = 0
23 AND MOD (name_cnt, ratio) = 0
24 ORDER BY name, column_1, column_2
25 /
COLUMN_1 COLUMN_2 NAME
-------------- -------------- ----
test_a_col_1_1 test_a_col_2_1 A
test_b_col_1_1 test_b_col_2_1 B
test_b_col_1_2 test_b_col_2_2 B
test_c_col_1_1 test_c_col_2_1 C
test_c_col_1_2 test_c_col_2_2 C
test_c_col_1_3 test_c_col_2_3 C
test_c_col_1_4 test_c_col_2_4 C
7 rows selected.
SCOTT@orcl_12.1.0.2.0> delete from ratios;
3 rows deleted.
SCOTT@orcl_12.1.0.2.0> insert into ratios values ( 'A',10);
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into ratios values ( 'B',20);
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into ratios values ( 'C',30);
1 row created.
SCOTT@orcl_12.1.0.2.0> SELECT DISTINCT column_1, column_2, name
2 FROM (SELECT column_1, column_2, name, ratio, tot, rn, dr,
3 COUNT (*) OVER (PARTITION BY rn, name) name_cnt,
4 COUNT (*) OVER (PARTITION BY rn) cnt
5 FROM (SELECT column_1, column_2, name, ratio, tot, rn, dr
6 FROM (SELECT d.column_1, d.column_2, d.name, r.ratio, r.tot,
7 DENSE_RANK () OVER (PARTITION BY d.name ORDER BY d.ROWID) dr
8 FROM test_data d,
9 (SELECT name,
10 ratio / (MIN (ratio) OVER (PARTITION BY NULL)) ratio,
11 (SUM (ratio) OVER (PARTITION BY NULL)) /
12 (MIN (ratio) OVER (PARTITION BY NULL)) tot
13 FROM ratios) r
14 WHERE d.name = r.name),
15 (SELECT ROWNUM rn
16 FROM DUAL
17 CONNECT BY LEVEL <=
18 (SELECT MIN (COUNT (*))
19 FROM test_data
20 GROUP BY name)) t
21 WHERE dr <= ratio * t.rn))
22 WHERE MOD (cnt, tot) = 0
23 AND MOD (name_cnt, ratio) = 0
24 ORDER BY name, column_1, column_2
25 /
COLUMN_1 COLUMN_2 NAME
-------------- -------------- ----
test_a_col_1_1 test_a_col_2_1 A
test_b_col_1_1 test_b_col_2_1 B
test_b_col_1_2 test_b_col_2_2 B
test_c_col_1_1 test_c_col_2_1 C
test_c_col_1_2 test_c_col_2_2 C
test_c_col_1_3 test_c_col_2_3 C
6 rows selected.
SCOTT@orcl_12.1.0.2.0> delete from ratios;
3 rows deleted.
SCOTT@orcl_12.1.0.2.0> insert into ratios values ( 'A',0.2);
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into ratios values ( 'B',0.4);
1 row created.
SCOTT@orcl_12.1.0.2.0> insert into ratios values ( 'C',0.8);
1 row created.
SCOTT@orcl_12.1.0.2.0> SELECT DISTINCT column_1, column_2, name
2 FROM (SELECT column_1, column_2, name, ratio, tot, rn, dr,
3 COUNT (*) OVER (PARTITION BY rn, name) name_cnt,
4 COUNT (*) OVER (PARTITION BY rn) cnt
5 FROM (SELECT column_1, column_2, name, ratio, tot, rn, dr
6 FROM (SELECT d.column_1, d.column_2, d.name, r.ratio, r.tot,
7 DENSE_RANK () OVER (PARTITION BY d.name ORDER BY d.ROWID) dr
8 FROM test_data d,
9 (SELECT name,
10 ratio / (MIN (ratio) OVER (PARTITION BY NULL)) ratio,
11 (SUM (ratio) OVER (PARTITION BY NULL)) /
12 (MIN (ratio) OVER (PARTITION BY NULL)) tot
13 FROM ratios) r
14 WHERE d.name = r.name),
15 (SELECT ROWNUM rn
16 FROM DUAL
17 CONNECT BY LEVEL <=
18 (SELECT MIN (COUNT (*))
19 FROM test_data
20 GROUP BY name)) t
21 WHERE dr <= ratio * t.rn))
22 WHERE MOD (cnt, tot) = 0
23 AND MOD (name_cnt, ratio) = 0
24 ORDER BY name, column_1, column_2
25 /
COLUMN_1 COLUMN_2 NAME
-------------- -------------- ----
test_a_col_1_1 test_a_col_2_1 A
test_b_col_1_1 test_b_col_2_1 B
test_b_col_1_2 test_b_col_2_2 B
test_c_col_1_1 test_c_col_2_1 C
test_c_col_1_2 test_c_col_2_2 C
test_c_col_1_3 test_c_col_2_3 C
test_c_col_1_4 test_c_col_2_4 C
7 rows selected.
[Updated on: Sat, 07 May 2016 18:42] Report message to a moderator
|
|
|
Goto Forum:
Current Time: Thu Apr 18 03:58:54 CDT 2024
|