如何从MySQL中随机选择记录样本? [英] How to select a random sample of records from MySQL?
问题描述
请考虑下表:
tweets
-----------------------
id tweet class
-----------------------
1 Foo bar baz! 2
2 Lorem ipsum 2
3 Foobar lorem 3
4 Activi set 1
5 Baz baz bar? 3
7 Dolor mez foo 3
8 Samet set bar 1
实际上,该表有600,000条记录,但是如示例表中所示,某些id
已被删除(因此最高的id
> 600,000). class
可以是1
,2
或3
.
In reality, the table has 600,000 records, but as indicated in the example table, some id
s have been deleted (so the highest id
> 600,000). class
can be either 1
, 2
or 3
.
我需要随机选择 1200条推文,每个class
400条.这可能与一个查询有关吗?
I need a random selection 1200 tweets, 400 of each class
. Is this possible to do with one query?
推荐答案
如果class
(3)的可能值数量有限,则可以很容易地通过一组UNION
查询来完成,每个查询都有它们的拥有ORDER BY RAND()
和LIMIT 400
:
If you have a finite number of possible values for class
(3), then it is easily done with a set of UNION
queries each having their own ORDER BY RAND()
and LIMIT 400
:
(SELECT id, tweet, class FROM tweets WHERE class = 1 ORDER BY RAND() LIMIT 400)
UNION
(SELECT id, tweet, class FROM tweets WHERE class = 2 ORDER BY RAND() LIMIT 400)
UNION
(SELECT id, tweet, class FROM tweets WHERE class = 3 ORDER BY RAND() LIMIT 400)
要将ORDER BY
应用于每个UNION
组,该组必须包含在()
中.
For the ORDER BY
to be applied to each UNION
group, the group must be enclosed in ()
.
这篇关于如何从MySQL中随机选择记录样本?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!