Re: Force randomize within a set of numbers
Date: Fri, 4 Dec 2009 07:12:50 -0800 (PST)
Message-ID: <78ea86f1-80bf-44fd-96f2-10091453a854_at_o9g2000prg.googlegroups.com>
On Dec 3, 5:01 pm, CrazyKarma <ska..._at_gmail.com> wrote:
> Here is the last one for this week..I guess :)
>
> Is there way to force the randomize within a set of number in Oracle?
> Say I have a set of integers ( 2,8,6,5)
> and I want to force randomize function to randomly pick within that
> set only.
>
> I know about the DBMS_RANDOM package, sample and seed clause but none
> help what I need to do here.
>
> any thoughts?
>
> CK
Here is another way to do it, if you do not know how many elements will be in the list.
First, we return a row from DUAL with the rows of interest with a
comma appended at the start and end:
SELECT
',2,8,6,5,54,100,67,7778,6,' ITEMS
FROM
DUAL;
ITEMS
,2,8,6,5,54,100,67,7778,6,
Next, we need to determine the number of elements and pick an element
position at random:
SELECT
ITEMS,
SUM(SIGN(INSTR(ITEMS, ',',1,ROWNUM)))-1 NUM_ITEMS,
(TRUNC(DBMS_RANDOM.VALUE(0,SUM(SIGN(INSTR(ITEMS ,',',1,ROWNUM)))-1))
+1) SEL_ITEM
FROM
(SELECT
',2,8,6,5,54,100,67,7778,6,' ITEMS
FROM
DUAL)
CONNECT BY
LEVEL<20;
ITEMS NUM_ITEMS SEL_ITEM -------------------------- ---------- ---------- ,2,8,6,5,54,100,67,7778,6, 9 6
Finally, we push the above SQL statement into an inline view, search
for the specified number of commas according to SEL_ITEM column to
determine the starting position of the element, and then search for
the next comma to determine the ending position of the element:
SELECT
ITEMS,
SEL_ITEM,
SUBSTR(ITEMS,INSTR(ITEMS,',',1,SEL_ITEM)+1,(INSTR(ITEMS,',',
1,SEL_ITEM+1)) - (INSTR(ITEMS,',',1,SEL_ITEM)) -1) ITEM
FROM
(SELECT
ITEMS,
SUM(SIGN(INSTR(ITEMS, ',',1,ROWNUM)))-1 NUM_ITEMS,
(TRUNC(DBMS_RANDOM.VALUE(0,SUM(SIGN(INSTR(ITEMS ,',',1,ROWNUM)))-1))
+1) SEL_ITEM
FROM
(SELECT
',2,8,6,5,54,100,67,7778,6,' ITEMS
FROM
DUAL)
CONNECT BY
LEVEL<20);
ITEMS SEL_ITEM ITEM -------------------------- ---------- ---- ,2,8,6,5,54,100,67,7778,6, 6 100
--
You might also be able to do something like this:
SELECT
'2,8,6,5,54,100,67,7778,6' ITEMS,
DBMS_RANDOM.VALUE(0,1) PERCENT
FROM
DUAL;
ITEMS PERCENT
------------------------ ----------
2,8,6,5,54,100,67,7778,6 .582165524
SELECT
ROWNUM ITEM_NUMBER,
REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) ITEM,
PERCENT
FROM
(SELECT
'2,8,6,5,54,100,67,7778,6' ITEMS,
DBMS_RANDOM.VALUE(0,1) PERCENT
FROM
DUAL)
CONNECT BY
REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) IS NOT NULL;
ITEM_NUMBER ITEM PERCENT
----------- ----- ----------
1 2 .104480002
2 8 .81670697
3 6 .826051929
4 5 .477132421
5 54 .89077554
6 100 .640842927
7 67 .145088893
8 7778 .252241096
9 6 .490905924
As you can see from the above, we have a problem in that the random
percent changes for each row, which will cause a problem for us if we
try to use it in a WHERE clause.
SELECT
MAX(ITEM_NUMBER) OVER () NUM_ITEMS,
PERCENT_RANK() OVER (ORDER BY ITEM_NUMBER) PR,
ITEM_NUMBER,
ITEM,
PERCENT
FROM
(SELECT
ROWNUM ITEM_NUMBER,
REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) ITEM,
PERCENT
FROM
(SELECT
'2,8,6,5,54,100,67,7778,6' ITEMS,
DBMS_RANDOM.VALUE(0,1) PERCENT
FROM
DUAL)
CONNECT BY
REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) IS NOT NULL);
NUM_ITEMS PR ITEM_NUMBER ITEM PERCENT
--------- ---------- ----------- ----- ----------
9 0 1 2 .110718377
9 .125 2 8 .306241972
9 .25 3 6 .953005936
9 .375 4 5 .033518415
9 .5 5 54 .803485415
9 .625 6 100 .456278133
9 .75 7 67 .04461405
9 .875 8 7778 .249680394
9 1 9 6 .484834331
If we now use a FIRST_VALUE analytic function, we could just retrieve
the first PERCENT value and use that in a WHERE clause:
SELECT
NUM_ITEMS,
ITEM_NUMBER,
ITEM
FROM
(SELECT
MAX(ITEM_NUMBER) OVER () NUM_ITEMS,
ITEM_NUMBER,
ITEM,
FIRST_VALUE(PERCENT) OVER () PERCENT
FROM
(SELECT
ROWNUM ITEM_NUMBER,
REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) ITEM,
PERCENT
FROM
(SELECT
'2,8,6,5,54,100,67,7778,6' ITEMS,
DBMS_RANDOM.VALUE(0,1) PERCENT
FROM
DUAL)
CONNECT BY
REGEXP_SUBSTR(ITEMS,'\w+',1,LEVEL) IS NOT NULL))
WHERE
ITEM_NUMBER=(TRUNC(NUM_ITEMS*PERCENT)+1);
NUM_ITEMS ITEM_NUMBER ITEM
--------- ----------- ----
9 7 67
Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration
from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Received on Fri Dec 04 2009 - 09:12:50 CST