如何实现在一张存储用户提交数据的mysql表中按比例随机抽取每个用户提交的信息...

2019独角兽企业重金招聘Python工程师标准>>>

最近完成了一项目，其中有一个抽检的需求，大致描述一下相当于这么一个案例：

每位用户提交了数量不等的数据条目信息存储在一张mysql表内，如何实现按百分比抽取每位用户的提交信息？

实例化下这个需求：100个用户每人各自提交了数量不等的数据累加起来共1000条存放在一张表内，你如何抽取每位用户提交数量的10%来抽查？

如果用代码将这1000条数据全读出来，然后按用户分组统计数量计算百分比在整合出最终数据，那可非常耽误时间。

我们要的最终结果，其实可以用一条sql语句就可以完成了。

思路：通过不同sql语句的查询结果构造两张表，一张表（table_b）按用户分组，组内数据随机排列，并加上序号; 一张表(table_c)存储每个用户应该抽取的数据条数。然后根据这两张表就可以……

我们从一张表（tasklist）出发：

status =0 表示未选中的状态。

第一步,构建表table_b

我们将用户分组，组内顺序随机排列。

SELECT `id`,`uname`,`taskinfo` FROM `tasklist` ORDER BY `uname` ASC, RAND();

执行sql语句，我们可以得到这样的查询结果

将查询结果视作一张表

$table_b = '(SELECT `id`,`uname` FROM `user_work_info` ORDER BY `uname` ASC,RAND())';

给组内加上编号，sql语句变成

SELECT `a`.`id` AS `id`,`a`.`uname` AS `uname`,@row:=IF(@u=`uname`,@row+1,1) AS `no`, @u:=`uname` 
FROM $table_b AS `a`,(SELECT @row:=0,@u:=0) AS `b`

执行，可以得到这样的查询结果

将查询结果当成一张表

 $table_b = '(SELECT `a`.`id` AS `id`,`a`.`uname` AS `uname`,@row:=IF(@u=`uname`,@row+1,1) AS `no`, @u:=`uname` 
			   FROM ' . $table_b . ' AS `a`,(SELECT @row:=0,@u:=0) AS `b`) ';

现在我们构建的表table_b完毕；

第二步，我们来构建表table_c，这张表将存储了每个用户会被抽取数据的条数。

①执行sql语句

SELECT `uname`,COUNT(`id`) AS `total` FROM `tasklist` GROUP BY `uname`

可以得到每个用户提交的数据条数，total为用户提交的数据条数。

②假设每组抽取的百分比为50% , 那么sql语句只要做这样的修改就可以了

SELECT `uname`,CEIL(COUNT(`id`) * 50/100) AS `top` FROM `tasklist` GROUP BY `uname`;

执行可以得到

③将查询结果视作一张表，我们就得到了表table_c

$table_c = '(SELECT `uname`,CEIL(COUNT(`id`) * 50/100) AS `top` FROM `tasklist` GROUP BY `uname`) ';

第三步，如果我们执行这样的sql语句，

SELECT `b`.`id` AS `id`,`b`.`uname` AS `uname`,`b`.`no`,`c`.`top` AS `top` 
FROM $table_b AS `b`,$table_c AS `c`
WHERE `b`.`uname`=`c`.`uname`;

可以得到

看到这个查询结果，大家应该都知道我们怎么用一条sql语句来实现从一张mysql表里来按比例随机抽取每个用户提交的数据信息了。

$table_b = '(SELECT `id`,`uname` FROM `tasklist` ORDER BY `uname` ASC,RAND())';
$table_b = '(SELECT `a`.`id` AS `id`,`a`.`uname` AS `uname`,@row:=IF(@u=`uname`,@row+1,1) AS `no`, @u:=`uname` 
			   FROM ' . $table_b . ' AS `a`,(SELECT @row:=0,@u:=0) AS `b`) ';
$table_c = '(SELECT `uname`,CEIL(COUNT(`id`) * 50/100) AS `top` FROM `tasklist` GROUP BY `uname`) ';
$sql = 'UPDATE `tasklist` AS `a`,' .  $table_b . ' AS `b`,' . $table_c . ' AS `c`
		SET `a`.`status`=1
		WHERE `a`.`id`=`b`.`id` AND `b`.`uname`=`c`.`uname` AND `b`.`no`<`c`.`top`';

设置status=1, 表示该条记录被选中。

转载于:https://my.oschina.net/u/1164352/blog/2222600