Given a table of IDs in an Oracle database, what is the best method to randomly flag (x) percent of them? In the example below, I am randomly flagging 20% of all records.
ID ELIG 1 0 2 0 3 0 4 1 5 0
My current approach shown below works fine, but I am wondering if there is a more efficient way to do this?
WITH DAT AS ( SELECT LEVEL AS "ID" FROM DUAL CONNECT BY LEVEL <= 5), TICKETS AS (SELECT "ID", 1 AS "ELIG" FROM ( SELECT * FROM DAT ORDER BY DBMS_RANDOM.VALUE ()) WHERE ROWNUM <= (SELECT ROUND (COUNT (*) / 5, 0) FROM DAT)), RAFFLE AS (SELECT "ID", 0 AS "ELIG" FROM DAT WHERE "ID" NOT IN (SELECT "ID" FROM TICKETS) UNION SELECT * FROM TICKETS) SELECT * FROM RAFFLE;
Advertisement
Answer
You could use a ROW_NUMBER
approach here:
WITH cte AS ( SELECT t.*, ROW_NUMBER() OVER (ORDER BY dbms_random.value) rn, COUNT(*) OVER () cnt FROM yourTable t ) SELECT t.*, CASE WHEN rn / cnt <= 0.2 THEN 'FLAG' END AS flag -- 0.2 to flag 20% FROM cte t ORDER BY ID;
Sample output for one run of the above query:
Note that one of five records is flagged, which is 20%.