如何使用 RAND(seed) 对 MySQL 中的行进行采样? [英] How to sample rows in MySQL using RAND(seed)?

问题描述

我需要使用 MySQL 从表中获取一组可重复的随机行.我使用 MySQL RAND 函数实现了这一点，使用行的 bigint 主键作为种子.有趣的是，这会产生看起来根本不随机的数字.谁能告诉我这里发生了什么以及如何让它正常工作?

I need to fetch a repeatable random set of rows from a table using MySQL. I implemented this using the MySQL RAND function using the bigint primary key of the row as the seed. Interestingly this produces numbers that don't look random at all. Can anyone tell me whats going on here and how to get it to work properly?

select id from foo where rand(id) < 0.05 order by id desc limit 100

在 600 行中的一个示例中，没有返回任何行.我将选择更改为包含id, rand(id)"，并在我得到的地方去掉了 rand 子句:

In one example out of 600 rows not a single one was returned. I change the select to include "id, rand(id)" and get rid of the rand clause in the where this is what I got:

| 163345 |  0.315191733944408 |
| 163343 |  0.814825518815616 |
| 163337 |  0.313726862253367 |
| 163334 |  0.563177533972242 |
| 163333 |  0.312994424545201 |
| 163329 |  0.312261986837035 |
| 163327 |  0.811895771708242 |
| 163322 |  0.560980224573035 |
| 163321 |  0.310797115145994 |
| 163319 |  0.810430896291911 |
| 163318 |  0.560247786864869 |
| 163317 |  0.310064677437828 |

看看有多少 0.31xxx 行.一点也不随机.

Look how many 0.31xxx lines there are. Not at all random.

PS:我知道这很慢，但在我的应用程序中，where 子句将行数限制为 1000.

PS: I know this is slow but in my app the where clause limits the number of rows to a few 1000.

推荐答案

对所有行使用相同的种子来做到这一点，例如:

Use the same seed for all the rows to do that, like:

select id from foo where rand(42) < 0.05 order by id desc limit 100

请参阅 rand() 文档为什么它会这样工作.如果您想要另一组值，请更改种子.

See the rand() docs for why it works that way. Change the seed if you want another set of values.

这篇关于如何使用 RAND(seed) 对 MySQL 中的行进行采样?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！