Home » SQL & PL/SQL » SQL & PL/SQL » Generate Random Id's of existing Id's (12.2)
Generate Random Id's of existing Id's [message #672731] Thu, 25 October 2018 13:10 Go to next message
lott42_gmail
Messages: 142
Registered: June 2010
Senior Member
I would like to generate a set of 10 random's ID's based upon existing ID's from a table

I have a table with 3 id's (1,2,4)
WITH data AS (
    SELECT
        1 id
    FROM
        dual
    UNION ALL
    SELECT
        2 id
    FROM
        dual
    UNION ALL
    SELECT
        4 id
    FROM
        dual
),max_min AS (
    SELECT
        MIN(id) mn,
        MAX(id) mx
    FROM
        data
) SELECT
    trunc(dbms_random.value(m.mn,m.mx + 1) ) id_random
  FROM
    max_min m
CONNECT BY
    level <= 10

Using those 3 values, I want 10 random numbers consisting of only those 3 id's.

This code is close but I get something like
ID_RANDOM
4
2
3
2
2
4
1
2
2
3

when in reality, I want something like
ID_RANDOM
4
2
1
2
2
4
1
2
2
4
because id "3" never existed in the first place

Somewhere I need to add a where condition to check to see if a particular id exists or not. If NO, don't use that value but generate a different one between (1,2,4) but still give me 10 records


something like:
  WHERE
    EXISTS (
        SELECT
            NULL
        FROM
            data
        WHERE
            r.id_random = data.id
    )
Re: Generate Random Id's of existing Id's [message #672732 is a reply to message #672731] Thu, 25 October 2018 13:38 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Well if this is a true random, as you have very few rows, you will most likely have the same row in the result. Smile

SQL> with
  2    data as (select 2*level+3 id from dual connect by level <= 100),
  3    with_rn as (select id, row_number() over (order by id) rn from data),
  4    cnt as (select count(*) cnt from data),
  5    lines as (select ceil(dbms_random.value(0,cnt)) line from cnt connect by level <= 10)
  6  select id, (id-3)/2 "(Original level in data)"
  7  from with_rn, lines
  8  where rn = line
  9  /
        ID (Original level in data)
---------- ------------------------
       189                       93
        63                       30
        31                       14
        75                       36
        55                       26
       105                       51
        95                       46
       171                       84
        23                       10
        53                       25

10 rows selected.

[Updated on: Thu, 25 October 2018 13:40]

Report message to a moderator

Re: Generate Random Id's of existing Id's [message #672735 is a reply to message #672732] Thu, 25 October 2018 15:50 Go to previous messageGo to next message
lott42_gmail
Messages: 142
Registered: June 2010
Senior Member
Michel, that is the whole point Quote:
you will most likely have the same row in the result
I ONLY want the numbers 1, 2 or 4 to possibly repeat in a result set of 10

ID_RANDOM
4
2
1
2
2
4
1
2
2
4

I DON'T wan't "3" to show up in any of random data because "3" doesn't exist in my sample of 3 rows

WITH data AS (
    SELECT
        1 id
    FROM
        dual
    UNION ALL
    SELECT
        2 id
    FROM
        dual
    UNION ALL
    SELECT
        4 id
    FROM
        dual

These id's actually go from 1..n but some id's MIGHT be missing
1
2
4
...
23
24
26
27
...
1001
1002
...
3, 25 are missing

I hope this explains a little more Razz
Re: Generate Random Id's of existing Id's [message #672739 is a reply to message #672735] Fri, 26 October 2018 00:25 Go to previous messageGo to next message
pablolee
Messages: 2881
Registered: May 2007
Location: Scotland
Senior Member
So you want to generate a list of 10 IDs from a list of IDs that has >10 values?

select ID from yourtable where rownum<10;

If the above is not what you are looking for then you need to explain in more detail. I'd suggest that you explain the business case i.e. what is it that you're trying to do that requires you to create this set of values.
Re: Generate Random Id's of existing Id's [message #672741 is a reply to message #672735] Fri, 26 October 2018 01:38 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

The example I gave is one possible solution.

Re: Generate Random Id's of existing Id's [message #672742 is a reply to message #672741] Fri, 26 October 2018 02:41 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Assuming this assumption is true:

pablolee wrote on Fri, 26 October 2018 07:25
So you want to generate a list of 10 IDs from a list of IDs that has >10 values?

A way is to randomly number your rows then get the first 10:
SQL> with
  2    data as (select 2*level+3 id from dual connect by level <= 100),
  3    ordered as (select id from data order by dbms_random.value)
  4  select id
  5  from ordered
  6  where rownum <= 10
  7  /
        ID
----------
        49
       181
         7
         9
       131
        95
        35
        79
       191
       163

10 rows selected.

SQL> /
        ID
----------
        85
         9
        75
       155
       197
        79
        77
        45
       109
       177

10 rows selected.

SQL> /
        ID
----------
       179
       161
        73
       145
       125
        99
       103
        77
       195
        47

10 rows selected.
Re: Generate Random Id's of existing Id's [message #672787 is a reply to message #672742] Fri, 26 October 2018 09:56 Go to previous messageGo to next message
lott42_gmail
Messages: 142
Registered: June 2010
Senior Member
Quote:
So you want to generate a list of 10 IDs from a list of IDs that has >10 values?
NO, I want to generate a list of 10 values based upon my current id's in my "psuedo" table from above

1
2
4


I created this Proc to HOPEFULLY show you output of what I'm looking for.

CREATE OR REPLACE PROCEDURE random_existing_ids AS

    v_cnt_actual     NUMBER := 0;
    v_cnt_found      NUMBER := 0;
    v_existing_ids   varchar2(100);
    v_random_id      NUMBER;
BEGIN
    v_existing_ids := '1,2,4';
    LOOP
        v_cnt_actual := v_cnt_actual + 1;
        v_random_id := trunc(dbms_random.value(1,4 + 1) );
        IF
            instr(v_existing_ids,v_random_id) > 0
        then
            --I found a random value between 1,2 or 4
            v_cnt_found := v_cnt_found + 1;
            dbms_output.put_line(v_random_id);
        END IF;

        EXIT WHEN v_cnt_found = 10;
    END LOOP;

    dbms_output.put_line('Actual iterations: '
    || v_cnt_actual);
END random_existing_ids;


If you run this a few times, in my runs, my output was thus:
4
4
2
4
1
1
4
1
1
2
Actual iterations: 13
Process exited.


4
1
1
2
1
4
2
2
1
2
Actual iterations: 12
Process exited.

You'll notice there is NO 3 because 3 did NOT exist as a valid id candidate
1
2
4

If you'll notice, the two result set answer's differ. I assume almost every time this is procedure is run that we'll get a different result set. The answer can ONLY be numbers between 1-4 EXCLUDING 3 in this example.

I was just trying to figure out how to generate the same "type" of list in sql vs using a pl/sql procedure.


Re: Generate Random Id's of existing Id's [message #672794 is a reply to message #672787] Fri, 26 October 2018 10:59 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why do you ignore my posts that give you the answer?

So do you want more rows than in the table or less rows than in the table?
Your posts are inconsistent.

Re: Generate Random Id's of existing Id's [message #672795 is a reply to message #672794] Fri, 26 October 2018 11:16 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And I have one more question, Larry, why did you create a new account? Are you no more in University of California?

[Updated on: Fri, 26 October 2018 11:18]

Report message to a moderator

Re: Generate Random Id's of existing Id's [message #672796 is a reply to message #672794] Fri, 26 October 2018 11:26 Go to previous messageGo to next message
lott42_gmail
Messages: 142
Registered: June 2010
Senior Member
Quote:

Why do you ignore my posts that give you the answer?

So do you want more rows than in the table or less rows than in the table?
Your posts are inconsistent.
Michel, using your last code
with
  2    data as (select 2*level+3 id from dual connect by level <= 100),
  3    ordered as (select id from data order by dbms_random.value)
  4  select id
  5  from ordered
  6  where rownum <= 10
  7  /

and some of your results you posted it doesn't match what I was asking for.

all my "expected results" are always values 1,2,4 and there are always 10 total values.
4
4
2
4
1
1
4
1
1
2

I suppose my requirement was not clear enough because yourself and others seem to be confused on what I'm looking for. I'm sorry I can't seem to clearly express my needs so let's not waste more time on this. I can get what I need with a pl/sql procedure("random_existing_ids") that I provided.

Sorry for the confusion...

To answer your other question regarding my new account, I created a new account because I no longer have access to that email address and I didn't remember my password to be able to modify my address to a different one.


[Updated on: Fri, 26 October 2018 11:34]

Report message to a moderator

Re: Generate Random Id's of existing Id's [message #672797 is a reply to message #672796] Fri, 26 October 2018 11:37 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
and some of your results you posted it doesn't match what I was asking for.
Which ones?

Quote:
I can get what I need with a pl/sql procedure("random_existing_ids") that I provided.
Your procedure is wrong, just add 13 in your list and you will see.

My latest query answers the question "set of 10 random's ID's based upon existing ID's from a table" assuming you have more than 10 rows in the table.

Re: Generate Random Id's of existing Id's [message #672798 is a reply to message #672796] Fri, 26 October 2018 11:43 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Quote:
To answer your other question regarding my new account, I created a new account because I no longer have access to that email address and I didn't remember my password to be able to modify my address to a different one.
I merged your previous account to this one, you can change the current account name to the old one if you want clicking on "Control Panel" above.

Re: Generate Random Id's of existing Id's [message #672799 is a reply to message #672798] Fri, 26 October 2018 11:59 Go to previous messageGo to next message
Michel Cadot
Messages: 67374
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Here's a general query (doesn't care if you want more or less rows than you have in the table).
Maybe this example is easier to understand.

First with your 1, 2, 4 list:
SQL> create table t (id int);

Table created.

SQL> insert into t values(1);

1 row created.

SQL> insert into t values(2);

1 row created.

SQL> insert into t values(4);

1 row created.

SQL> def NB_ROWS=10

SQL> with
  2    data as (
  3      select id
  4      from t,
  5           (select 1 from dual connect by level <= ceil(&NB_ROWS/(select count(*) from t)))
  6    ),
  7    ordered as (select id from data order by dbms_random.value)
  8  select id
  9  from ordered
 10  where rownum <= &NB_ROWS
 11  /
        ID
----------
         2
         4
         1
         4
         2
         2
         1
         1
         4
         4

10 rows selected.

SQL> /
        ID
----------
         2
         4
         2
         2
         1
         2
         4
         1
         4
         1

10 rows selected.

SQL> /
        ID
----------
         1
         2
         4
         1
         2
         4
         2
         1
         4
         4

10 rows selected.
Now I add 20 rows: the first 20 even numbers above 4 (so all odd numbers but 1 are missing) and query again 10 rows:
SQL> insert into t select 4+2*level from dual connect by level <= 20;

20 rows created.

SQL> select * from t order by 1;
        ID
----------
         1
         2
         4
         6
         8
        10
        12
        14
        16
        18
        20
        22
        24
        26
        28
        30
        32
        34
        36
        38
        40
        42
        44

23 rows selected.

SQL> with
  2    data as (
  3      select id
  4      from t,
  5           (select 1 from dual connect by level <= ceil(&NB_ROWS/(select count(*) from t)))
  6    ),
  7    ordered as (select id from data order by dbms_random.value)
  8  select id
  9  from ordered
 10  where rownum <= &NB_ROWS
 11  /
        ID
----------
        24
        32
        12
        26
         1
        42
         2
         6
        20
         8

10 rows selected.

SQL> /
        ID
----------
        20
        30
        16
        40
        10
        36
        24
         1
        28
        14

10 rows selected.

SQL> /
        ID
----------
        18
         4
        30
        44
        12
        26
        34
        28
        36
        32

10 rows selected.

[Updated on: Sat, 27 October 2018 01:20]

Report message to a moderator

Re: Generate Random Id's of existing Id's [message #672800 is a reply to message #672798] Fri, 26 October 2018 12:06 Go to previous messageGo to next message
lott42_gmail
Messages: 142
Registered: June 2010
Senior Member
Quote:
I merged your previous account to this one, you can change the current account name to the old one if you want clicking on "Control Panel" above.
Thanks, I'll try to do that today.

Quote:
Your procedure is wrong, just add 13 in your list and you will see.
oops, you're right. A quick fix could be

CREATE OR REPLACE PROCEDURE random_existing_ids AS

    v_cnt_actual     NUMBER := 0;
    v_cnt_found      NUMBER := 0;
    v_existing_ids   varchar2(100);
    v_random_id      NUMBER;
begin
    v_existing_ids := '1,2,4,13';
    LOOP
        v_cnt_actual := v_cnt_actual + 1;
        v_random_id := trunc(dbms_random.value(1,13 + 1) );
        IF
            --instr(v_existing_ids,v_random_id) > 0
            instr(v_existing_ids,','||to_char(v_random_id)||',') > 0
        then
            --I found a random value between 1,2 or 4
            v_cnt_found := v_cnt_found + 1;
            dbms_output.put_line('match: ' || v_random_id);
        END IF;

        EXIT WHEN v_cnt_found = 10;
    END LOOP;

    dbms_output.put_line('Actual iterations: '
    || v_cnt_actual);
END random_existing_ids;

result
match: 2
match: 4
match: 2
match: 13
match: 2
match: 2
match: 13
match: 2
match: 4
match: 2
Actual iterations: 37
Process exited.

although, a better test should be created.

Quote:

My latest query answers the question "set of 10 random's ID's based upon existing ID's from a table" assuming you have more than 10 rows in the table.
I don't just want any 10 random id's created. I want 10 random id's created which are the same Id's as my primer id's. You mentioned "13" won't work in my original procedure. So in that case my existing"Primer" id's would now be 1,2,4,13. I want 10 random number's that only match those 4 numbers



Re: Generate Random Id's of existing Id's [message #672802 is a reply to message #672799] Fri, 26 October 2018 12:13 Go to previous message
lott42_gmail
Messages: 142
Registered: June 2010
Senior Member
Quote:

SQL> with
2 data as (
3 select id
4 from t,
5 (select 1 from dual connect by level <= ceil(&NB_ROWS/(select count(*) from t)))
6 ),
7 ordered as (select id from data order by dbms_random.value)
8 select id
9 from ordered
10 where rownum <= &NB_ROWS
OK, let me try this out. I'm off to a meeting right now.

Thanks for sticking with me here. I'm not always the best at giving good clarifications of what I'm looking for... Smile
Previous Topic: Replace strings [merged by jd]
Next Topic: How to tune Query on Large Oracle Table
Goto Forum:
  


Current Time: Tue Sep 29 22:30:15 CDT 2020