mysql - 如何从具有多个条件的mysql数据库中随机抽取样本

Question

我有一张表，里面有一些用户信息，性别，出生日期，“类别”等

我需要随机抽样，但结果如下:

75% Women
25% Men

50% Age between 25-35
40% Age between 36-40
10% Age between 41-60

总共有2000条记录。

有没有直接在 MySQL 中执行此操作的方法？

Answer 1

您不能在联合语句中为每个 sql 查询使用 order by 或 limit。它们可以在最后使用。

所以最好对输出使用存储过程。

CREATE PROCEDURE USER_DATA_RAND()
BEGIN
CREATE TEMPORARY TABLE USER_TMP
ID INT,
DATE_OF_BIRTH DATE,
CATEGORY STRING,
AGE INT)
INSERT INTO USER_TMP
select sex, date_of_birth, `category`,age from users where age between 25 and 35 and sex = 'male' order by rand() limit 2000*0.5*0.25) ;
INSERT INTO USER_TMP
select sex, date_of_birth, `category`,age from users where age between 25 and 35 and sex = 'female' order by rand() limit 2000*0.5*0.75) ;
INSERT INTO USER_TMP
select sex, date_of_birth, `category`,age from users where age between 36 and 40 and sex = 'male' order by rand() limit 2000*0.40*0.25) ;
INSERT INTO USER_TMP
select sex, date_of_birth, `category`,age from users where age between 36 and 40 and sex = 'female' order by rand() limit 2000*0.40*0.75) ;
INSERT INTO USER_TMP
select sex, date_of_birth, `category`,age from users where age between 41 and 60 and sex = 'male' order by rand() limit 2000*0.10*0.25) ;
INSERT INTO USER_TMP
select sex, date_of_birth, `category`,age from users where age between 41 and 60 and sex = 'female' order by rand() limit 2000*0.10*0.75) ;

SELECT * FROM USER_TMP;
END