Use Oracle analysis functions to randomly extract N records

Use Oracle analysis functions to randomly extract N records

Simulate a song table with a song ID and Author ID, and initialize some data

Create table music (
 
Music_id int primary key,

Userid int

);

Insert into music (music_id)

Select rownum rn from dual connect by level <1000;

Update music set userid = mod (music_id, 5 );

Commit;
Requirements:
Each author randomly selects three of his works.
 
The SQL statement is as follows:
 

Select * from
 
(

Select music_id, userid, ROW_NUMBER () OVER (partition by userid order by dbms_random.random () rn from music

)

Where rn <= 3;
 
First execution:

Second execution:

This method is only effective for Oracle (using its analysis functions)

Detailed examples of Oracle function GREATEST Functions

Single-row conversion functions of Oracle Functions

Oracle functions that generate dynamic prefixes and auto-increment numbers

Replace () for Oracle Functions ()

Oracle Functions

Differences in case and decode usage and Performance Comparison of Oracle Functions