MySQL: Select N percent random sample from each group and update a field

Question

I have a MySQL table called tbltaskrecord and I have to pick a random sample of 10% for each UserId for each ReviewDate and update the field AuditStatus to 'Check'. This is the table script and some sample data:

CREATE TABLE tbltaskrecord(
ReviewDate  DATE  NOT NULL
,UserId      VARCHAR(50) NOT NULL
,TaskId      VARCHAR(50) NOT NULL PRIMARY KEY
,AuditStatus VARCHAR(50)
);

INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe1','R110000001',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe1','R110000002',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe1','R110000003',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe1','R110000004',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe1','R110000005',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe1','R110000006',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe1','R110000007',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe1','R110000008',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe1','R110000009',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe1','R110000010',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe1','R110000011',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe1','R110000012',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe1','R110000013',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe1','R110000014',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe1','R110000015',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe1','R110000016',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe1','R110000017',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe1','R110000018',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe1','R110000019',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe1','R110000020',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe2','R110000021',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe2','R110000022',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe2','R110000023',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe2','R110000024',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe2','R110000025',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe2','R110000026',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe2','R110000027',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe2','R110000028',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe2','R110000029',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe2','R110000030',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe2','R110000031',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe2','R110000032',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe2','R110000033',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe2','R110000034',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe2','R110000035',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe2','R110000036',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe2','R110000037',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe2','R110000038',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe2','R110000039',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-19','jdoe2','R110000040',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe1','R110000041',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe1','R110000042',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe1','R110000043',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe1','R110000044',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe1','R110000045',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe1','R110000046',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe1','R110000047',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe1','R110000048',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe1','R110000049',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe1','R110000050',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe1','R110000051',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe1','R110000052',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe1','R110000053',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe1','R110000054',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe1','R110000055',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe1','R110000056',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe1','R110000057',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe1','R110000058',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe1','R110000059',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe1','R110000060',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe2','R110000061',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe2','R110000062',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe2','R110000063',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe2','R110000064',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe2','R110000065',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe2','R110000066',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe2','R110000067',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe2','R110000068',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe2','R110000069',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe2','R110000070',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe2','R110000071',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe2','R110000072',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe2','R110000073',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe2','R110000074',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe2','R110000075',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe2','R110000076',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe2','R110000077',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe2','R110000078',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe2','R110000079',NULL);
INSERT INTO tbltaskrecord(ReviewDate,UserId,TaskId,AuditStatus) VALUES ('2018-09-20','jdoe2','R110000080',NULL);

Population:

+-------------+-----------+-----------+-------------+
|   UserId    | 9/19/2018 | 9/20/2018 | Grand Total |
+-------------+-----------+-----------+-------------+
| jdoe1       |        20 |        20 |          40 |
| jdoe2       |        20 |        20 |          40 |
| Grand Total |        40 |        40 |          80 |
+-------------+-----------+-----------+-------------+

Sample:

+-------------+-----------+-----------+-------------+
|   UserId    | 9/19/2018 | 9/20/2018 | Grand Total |
+-------------+-----------+-----------+-------------+
| jdoe1       |         2 |         2 |           4 |
| jdoe2       |         2 |         2 |           4 |
| Grand Total |         4 |         4 |           8 |
+-------------+-----------+-----------+-------------+

This is what I am trying to obtain:

+------------+--------+------------+-------------+
| ReviewDate | UserId |   TaskId   | AuditStatus |
+------------+--------+------------+-------------+
| 2018-09-19 | jdoe1  | R110000008 | Check       |
| 2018-09-19 | jdoe1  | R110000020 | Check       |
| 2018-09-19 | jdoe2  | R110000029 | Check       |
| 2018-09-19 | jdoe2  | R110000037 | Check       |
| 2018-09-20 | jdoe1  | R110000052 | Check       |
| 2018-09-20 | jdoe1  | R110000057 | Check       |
| 2018-09-20 | jdoe2  | R110000070 | Check       |
| 2018-09-20 | jdoe2  | R110000074 | Check       |
+------------+--------+------------+-------------+

Here is what I tried for selecting:

select * from tbltaskrecord WHERE RAND() < .10

But for 4 trial it gave 5, 6, 9 and 8 rows. Here is the output of last trial. Only 2 from jdoe1 of which none is from date 2018-09-19.

+------------+--------+-------------+-------------+
| ReviewDate | UserId |   TaskId    | AuditStatus |
+------------+--------+-------------+-------------+
| 2018-09-20 |  jdoe1 |  R110000043 |  NULL       |
| 2018-09-20 |  jdoe1 |  R110000052 |  NULL       |
| 2018-09-19 |  jdoe2 |  R110000022 |  NULL       |
| 2018-09-19 |  jdoe2 |  R110000028 |  NULL       |
| 2018-09-19 |  jdoe2 |  R110000031 |  NULL       |
| 2018-09-20 |  jdoe2 |  R110000062 |  NULL       |
| 2018-09-20 |  jdoe2 |  R110000064 |  NULL       |
| 2018-09-20 |  jdoe2 |  R110000080 |  NULL       |
+------------+--------+-------------+-------------+

How do I do that?

user_1529673 · Answer

This solution is teetering on the edge of being a 'hack' - but it's beyond my pay-grade to say whether or not it actually crosses that line...

Anyway, for good or bad, here's one idea...

SELECT a.ReviewDate 
     , a.UserId 
     , a.TaskId     
     , a.AuditStatus
  FROM 
     ( SELECT x.*
            , CASE WHEN @prev = userid THEN @i:=@i+1 ELSE @i:=1 END i
            , @prev := userid
         FROM
            ( SELECT *
                FROM tbltaskrecord 
               ORDER
                  BY userid,RAND()
            ) x -- my understanding is that this bit shouldn't work. But it does.
            , (SELECT @prev:=null,@i:=0) vars
        ORDER
           BY userid
            , i
     ) a
  JOIN
     (SELECT userid, COUNT(*)/10 pct FROM tbltaskrecord GROUP BY userid) b
    ON b.userid = a.userid
   AND b.pct >= a.i;

+------------+--------+------------+-------------+
| ReviewDate | UserId | TaskId     | AuditStatus |
+------------+--------+------------+-------------+
| 2018-09-20 | jdoe1  | R110000046 | NULL        |
| 2018-09-20 | jdoe1  | R110000042 | NULL        |
| 2018-09-19 | jdoe1  | R110000012 | NULL        |
| 2018-09-19 | jdoe1  | R110000016 | NULL        |
| 2018-09-20 | jdoe2  | R110000077 | NULL        |
| 2018-09-19 | jdoe2  | R110000034 | NULL        |
| 2018-09-19 | jdoe2  | R110000022 | NULL        |
| 2018-09-19 | jdoe2  | R110000026 | NULL        |
+------------+--------+------------+-------------+
8 rows in set (0.01 sec)

SqlFiddle http://sqlfiddle.com/#!9/bd3256/1

user_8251556 · Answer

Here is the final stored procedure.

CREATE PROCEDURE `spPickSample`(IN `pReviewDate` VARCHAR(50))
BEGIN
  UPDATE tbltaskrecord JOIN (SELECT a.ReviewDate 
       , a.UserId 
       , a.TaskId     
       , a.AuditStatus
    FROM 
       ( SELECT x.*
              , CASE WHEN @prev = userid THEN @i:=@i+1 ELSE @i:=1 END i
              , @prev := userid
           FROM
              ( SELECT *
                  FROM tbltaskrecord
                   WHERE ReviewDate = pReviewDate
                 ORDER
                    BY userid,RAND()
              ) x -- my understanding is that this bit shouldn't work. But it does.
              , (SELECT @prev:=null,@i:=0) vars
          ORDER
             BY userid
              , i
       ) a
    JOIN
       (SELECT userid, COUNT(*)/10 pct FROM tbltaskrecord WHERE ReviewDate = pReviewDate GROUP BY userid) b
      ON b.userid = a.userid
     AND b.pct >= a.i) q ON tbltaskrecord.TaskId=q.TaskId SET tbltaskrecord.AuditStatus='Check';
END

MySQL: Select N percent random sample from each group and update a field

2 Answers