Note - this is more of an academic question as I have a resolution, I am just keen to see whether my alternative approach is possible.
I have a HR table with a list of names. For demo purposes I needed to shuffle these around to make them a bit more anonymous. I.e. given the set of data:
Select 1 person_id, 'Alice' first_name, 'Jones' last_name From dual Union All
Select 2 person_id, 'Bob' first_name, 'Smith' last_name From dual Union All
Select 3 person_id, 'Charles' first_name, 'Walker' last_name From dual Union All
Select 4 person_id, 'Dave' first_name, 'Simpson' last_name From dual Union All
Select 5 person_id, 'Elaine' first_name, 'Andrews' last_name From dual
I want a list of person_id's (which may not be continuous as in the example above), with a random first name and last name. I'm able to do this in a bit of a rudimentary way:
With xxdemo_tab As(
Select 1 person_id, 'Alice' first_name, 'Jones' last_name From dual Union All
Select 2 person_id, 'Bob' first_name, 'Smith' last_name From dual Union All
Select 3 person_id, 'Charles' first_name, 'Walker' last_name From dual Union All
Select 4 person_id, 'Dave' first_name, 'Simpson' last_name From dual Union All
Select 5 person_id, 'Elaine' first_name, 'Andrews' last_name From dual
)
Select x1.person_id, x2.first_name, x3.last_name From
(Select Row_Number() Over (Order By dbms_random.value) n, person_id From xxdemo_tab) x1,
(Select Row_Number() Over (Order By dbms_random.value) n, first_name From xxdemo_tab) x2,
(Select Row_Number() Over (Order By dbms_random.value) n, last_name From xxdemo_tab) x3
Where x1.n=x2.n
And x2.n=x3.n
Order By person_id
However I was sure that it was possible to do it another way. I've been keen to learn the model clause for a while, so I thought that this might be a candidate for that. However I am fighting somewhat with the issue of not being able to call a function for each row, something Tom explains very clearly here:
https://asktom.oracle.com/pls/apex/f?p=100:11:0::::p11_question_id:3181424400346795479 I managed to get something half working, but I have to hard-code in the number of possible rows to dbms_random.value().
With xxdemo_tab As(
Select 1 person_id, 'Alice' first_name, 'Jones' last_name From dual Union All
Select 2 person_id, 'Bob' first_name, 'Smith' last_name From dual Union All
Select 3 person_id, 'Charles' first_name, 'Walker' last_name From dual Union All
Select 4 person_id, 'Dave' first_name, 'Simpson' last_name From dual Union All
Select 5 person_id, 'Elaine' first_name, 'Andrews' last_name From dual
)
Select person_id, first_name, last_name
From xxdemo_tab
Model
Dimension By (Row_number () Over (Order By person_id) idx)
Measures (person_id, first_name, last_name)
Rules Automatic Order
(first_name [idx] = first_name[Round(Dbms_Random.Value(1,5))]);
Have you any suggestions on how I might be able to get this working a bit better? And how can I guarantee that dbms_random.value is called for every row because I'm not 100% sure it is at the moment - often I get all the same value in first_name, which I realise is possible with just 5 values, however seems to occur too frequently.
Ideally I'd like to do this in pure SQL without invoking PL/SQL functions. As I said, more of an academic exercise. :)
I dont think we can ever get away from at least a dbms_random call per item (ie, rows x cols )...but we can eliminate the join
SQL> WITH xxdemo_tab AS
2 ( SELECT 1 person_id, 'Alice' first_name, 'Jones' last_name FROM dual
3 UNION ALL
4 SELECT 2 person_id, 'Bob' first_name, 'Smith' last_name FROM dual
5 UNION ALL
6 SELECT 3 person_id, 'Charles' first_name, 'Walker' last_name FROM dual
7 UNION ALL
8 SELECT 4 person_id, 'Dave' first_name, 'Simpson' last_name FROM dual
9 UNION ALL
10 SELECT 5 person_id, 'Elaine' first_name, 'Andrews' last_name FROM dual
11 ),
12 t AS
13 ( SELECT 1 typ, TO_CHAR(person_id) x, dbms_random.value rnd FROM xxdemo_tab
14 UNION ALL
15 SELECT 2 typ, first_name , dbms_random.value FROM xxdemo_tab
16 UNION ALL
17 SELECT 3 typ, last_name , dbms_random.value FROM xxdemo_tab ORDER BY 3
18 ),
19 t1 AS
20 ( SELECT rownum r, t.* FROM t
21 ),
22 t2 AS
23 (SELECT t1.* ,
24 row_number() over ( partition BY typ order by r ) AS rnum
25 FROM t1
26 )
27 SELECT *
28 FROM
29 ( SELECT rnum, typ, x FROM t2
30 ) pivot ( MAX(x) AS x FOR ( typ ) IN ( '1' AS pid, '2' AS first_name, '3' AS last_name ));
RNUM PID_X FIRST_NAME_X LAST_NAME_X
---------- ---------------------------------------- ---------------------------------------- ------------------------
1 1 Elaine Simpson
2 2 Bob Andrews
4 3 Alice Walker
5 4 Dave Smith
3 5 Charles Jones
I'm sure there's probably better solutions - others welcome to add theirs.