如何从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 ids 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屋！