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  |
 |
lott42_gmail
Messages: 146
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
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  |
 |
Michel Cadot
Messages: 68447
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. 
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 #672739 is a reply to message #672735] |
Fri, 26 October 2018 00:25 |
pablolee
Messages: 2882
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 #672787 is a reply to message #672742] |
Fri, 26 October 2018 09:56 |
|
lott42_gmail
Messages: 146
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
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
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 #672796 is a reply to message #672794] |
Fri, 26 October 2018 11:26 |
|
lott42_gmail
Messages: 146
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.
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 #672799 is a reply to message #672798] |
Fri, 26 October 2018 11:59 |
|
Michel Cadot
Messages: 68447
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.
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 |
|
lott42_gmail
Messages: 146
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
|
|
|
|
|
|
Goto Forum:
Current Time: Sun May 28 17:22:07 CDT 2023
|
] 
Search
Help
Members
Register
Login
Home
