CODEWITHSUNDEEP
sqloracle-database
DJC
DJC

Reputation: 1611

Randomly flagging records in an Oracle Table

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;

Upvotes: 1

Views: 42

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521804

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;

Demo

Sample output for one run of the above query:

screen capture

Note that one of five records is flagged, which is 20%.

Upvotes: 1

Related Questions