Efficient way to Insert the data after generating random sequence-CodePudding

I use the below query to randomly select 10% of IDs (MYSQL(version MySQL 8.0))

select id from
(WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY RAND()) rn, COUNT(*) OVER () cnt
    FROM table1
    where submitted between date_sub(now(),INTERVAL 1 WEEK) and now()
)
SELECT *
FROM cte
WHERE rn < 0.1 * cnt
) as tt;

Sample IDs generated: IDs: 4 7 8 9

Now, I want the selected IDs to be inserted to Tom, Dick and Harry

I can write the Insert statement something like this:

INSERT INTO table2 (ID,analystName)
select id, 'Tom' from
(WITH cte AS (
    SELECT *, ROW_NUMBER() OVER (ORDER BY RAND()) rn, COUNT(*) OVER () cnt
    FROM table1
    where submitted between date_sub(now(),INTERVAL 1 WEEK) and now()
)
SELECT *
FROM cte
WHERE rn < 0.1 * cnt
) as tt;

However, I cannot use the SQL again to insert it for 'Dick' and 'Harry' as the id's generated would be different. Is there an efficient way to insert the data for the three person in a single INSERT statement?

I want the end result to be:

IDs AnalystName
4    Tom
7    Tom
8    Tom
9    Tom
4    Dick
7    Dick
8    Dick
9    Dick
4    Harry
7    Harry
8    Harry
9    Harry

Table2 where this data would be inserted, doesn't have a data field to filter the data, and this insertion would be a weekly exercise, such that these three users get assigned the same random IDs.

CodePudding user response：

INSERT INTO
  table2 (
    ID,
    analystName
  )
WITH
  sample
AS
(
  SELECT *, ROW_NUMBER() OVER (ORDER BY RAND()) rn, COUNT(*) OVER () cnt
    FROM table1
   WHERE submitted BETWEEN DATE_SUB(now(),INTERVAL 1 WEEK) AND now()
),
  analyst(name)
AS
(
            SELECT 'tom'
  UNION ALL SELECT 'dick'
  UNION ALL SELECT 'harry'
)
SELECT
  sample.id,
  analyst.name
FROM
  sample
CROSS JOIN
  analyst
WHERE
  sample.rn < sample.cnt * 0.1