OraFAQ Forum: SQL & PL/SQL » select based on ratio from second table

Home » SQL & PL/SQL » SQL & PL/SQL » select based on ratio from second table (Oracle, 11.2.0.4 )

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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:

name	ratio
A	1
B	1
C	2

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 #650931 is a reply to message #650930]

Sat, 07 May 2016 07:46

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Welcome to the forum.
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.

Quote:

so I am allowed to fetch 3 A and 3 B.

Which ones?
Are all rows with the same ones?
If not then provide a test case with representative data and post the result for these ones.

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.

Report message to a moderator

Re: select based on ratio from second table [message #650935 is a reply to message #650934]

Sat, 07 May 2016 09:24

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So what should be the result for the data you gave?
And once again:
Please read OraFAQ Forum Guide and How to use [code] tags and make your code easier to read.

And you didn't answer to my question "Which ones?".

[Updated on: Sat, 07 May 2016 09:25]

Report message to a moderator

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'
            );

Report message to a moderator

Re: select based on ratio from second table [message #650940 is a reply to message #650936]

Sat, 07 May 2016 11:36

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

How about this:

select * from test_data where rownum <= (select ratio from ratios where ratios.name=test_data.name);

but I'm really not sure what you want. Is this a college homework question?

Report message to a moderator

Re: select based on ratio from second table [message #650942 is a reply to message #650940]

Sat, 07 May 2016 12:06

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

In the original question the number of C in test_data determine the number of A and B to return from the ratio.
But what is the result if there are not sufficient rows in test_data to return? For instance if the ratio is 4:4:2?
You still did not give us the result you want for the data you gave.
Should it return 5 C or only 4?
If it is 5 then how many B as 5*2/4=2.5? and how many A?

Report message to a moderator

Re: select based on ratio from second table [message #650943 is a reply to message #650942]

Sat, 07 May 2016 12:10

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

can RATIO be not INTEGER; such as 1.5 or 2/3?

Report message to a moderator

Re: select based on ratio from second table [message #650944 is a reply to message #650943]

Sat, 07 May 2016 12:21

merlin1989
Messages: 5
Registered: May 2016

Junior Member

@John no thats not really what I am looking for, because it's not based on the ratio. And no it's no homework.

@Michel If there are less rows than the ratio would tell then it should return these rows, if there is nothing, just return nothing for this name.
I wrote the result I need: "The result should bed the whole rows from table "test_data" matching the ration on name from ratios table."
If 4:4:2 is A:B:C then I need the same row count for A and B ( if possible, in case there are less rows available for A then for B return all of them and the same the other way around), if it's a float it should be rounded to the next higher number.

@BlackSwan ratio can be integer or float.

Report message to a moderator

Re: select based on ratio from second table [message #650945 is a reply to message #650944]

Sat, 07 May 2016 12:43

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

I wrote the result I need:

I don't care.
I want you SHOW me the result.

Quote:

If 4:4:2 is A:B:C then I need the same row count for A and B

And for C?

Show us for ALL The cases we mentioned? ALL OF THEM!

Report message to a moderator

Re: select based on ratio from second table [message #650946 is a reply to message #650944]

Sat, 07 May 2016 12:48

John Watson
Messages: 8929
Registered: January 2010
Location: Global Village

Senior Member

Quote:

@John no thats not really what I am looking for, because it's not based on the ratio.

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.

Report message to a moderator

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!

Report message to a moderator

Re: select based on ratio from second table [message #650948 is a reply to message #650947]

Sat, 07 May 2016 13:29

Michel Cadot
Messages: 68641
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

So how and when do you apply this sentence: "If there are less rows than the ratio would tell then it should return these rows..."?

In example 4: the result your gave (5,10,15) does not match the ratio (0.2:0.4:0.8).

[Updated on: Sat, 07 May 2016 13:30]

Report message to a moderator

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?

Report message to a moderator

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

Previous Topic:	Index is not used when additing one more selection criteria
Next Topic:	Revoke insert and Update access from schema owner

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Thu Apr 18 03:58:54 CDT 2024