我想在PostgreSQL中随机选择行,我尝试了这个:

select * from table where random() < 0.01;

但也有人建议:

select * from table order by random() limit 1000;

我有一个非常大的表,有5亿行,我希望它是快速的。

哪种方法更好?有什么不同?选择随机行最好的方法是什么?


当前回答

我的经验告诉我:

offset floor(random() * N) limit 1并不比order by random() limit 1快。

我认为偏移量方法会更快,因为它可以节省在Postgres中排序的时间。事实证明并非如此。

其他回答

我知道我有点晚了,但我刚刚找到了这个叫做pg_sample的很棒的工具:

pg_sample -从较大的PostgreSQL数据库中提取一个小的样本数据集,同时保持引用完整性。

我尝试了一个350M行的数据库,它真的很快,不知道随机性。

./pg_sample --limit="small_table = *" --limit="large_table = 100000" -U postgres source_db | psql -U postgres target_db

从PostgreSQL 9.5开始,有一个新的语法专门用于从表中获取随机元素:

SELECT * FROM mytable TABLESAMPLE SYSTEM (5);

这个例子将给出mytable中5%的元素。

有关文档的更多说明:http://www.postgresql.org/docs/current/static/sql-select.html

如果只需要一行,可以使用从count派生的计算偏移量。

select * from table_name limit 1
offset floor(random() * (select count(*) from table_name));

这是一个对我有用的决定。我想这很容易理解和执行。

SELECT 
  field_1, 
  field_2, 
  field_2, 
  random() as ordering
FROM 
  big_table
WHERE 
  some_conditions
ORDER BY
  ordering 
LIMIT 1000;

添加一个名为r的列,类型为serial。指数r。

假设我们有20万行,我们将生成一个随机数n,其中0 < n <= 200000。

选择r > n的行,按ASC排序,选择最小的行。

代码:

select * from YOUR_TABLE 
where r > (
    select (
        select reltuples::bigint AS estimate
        from   pg_class
        where  oid = 'public.YOUR_TABLE'::regclass) * random()
    )
order by r asc limit(1);

代码是自解释的。中间的子查询用于快速估计来自https://stackoverflow.com/a/7945274/1271094的表行数。

在应用程序级别,如果n >为行数或需要选择多行,则需要再次执行该语句。