主页 / dba / 问题 / 96610
Accepted
Rana
Asked: 2015-03-31 08:53:49 +0800 CST

在 PostgreSQL 中采样

  • 772

我正在寻找在 PostgreSQL 中随机抽样的可能方法。我发现了几种具有不同优点和缺点的方法。天真的方法是：

select * from Table_Name
order by random()
limit 10;

另一种更快的方法是：

select * from Table_Name
WHERE random() <= 0.01
order by random()
limit 10;

（虽然 0.01 取决于表格大小和样本大小；这只是一个示例。）

在这两个查询中，都会为每一行生成一个随机数，并根据这些随机生成的数字进行排序。然后在排序后的数字中选择前10个作为最终结果，所以我认为这些应该是抽样而不是替换。

现在我想做的是以某种方式将这种采样方法变成带替换的采样。这怎么可能？或者在 PostgreSQL 中有没有其他可以替换的随机抽样方法？

我不得不说我确实知道这怎么可能，但我不知道如何在 Postgres 中实现它。这是我的想法：

如果我们不是生成一个随机值，而是生成样本大小为 的随机S值S，然后对所有随机生成的值进行排序，它将进行带放回抽样。（我不知道我是否正确。）
此时我不介意查询的性能。

postgresql random

2 个回答

  1. 矫正

    如果我们不是生成一个随机值，而是生成 S 个随机值，其中 S 是样本大小，然后对所有随机生成的值进行排序，它将是带放回抽样。（我不知道我是否正确。）

    你错了两点。

    1. 您的原始查询不会生成“一个随机值”。random()是对每一行调用的易失性函数。然后所有行都按结果排序，这就是为什么这对于大表来说效率非常低。
    2. 因此，“如果我们生成S随机值……”的方法将一事无成。您必须将随机行“细化”以匹配“行号” - 现有 ID 或代理编号。这就是我要演示的。

    我知道你说：

    在这一点上，我不介意查询的性能。

    所以@ypercube 的回答和往常一样是正确的。但我的心在流血。有很多更快的方法。

    假设最坏的情况是您对桌子一无所知。

    以任意顺序将序号附加到每一行，然后您可以可靠地选择随机行，如果需要，可以多次选择。表扫描一次，对于大表和小样本来说仍然很昂贵，但比扫描n次要好得多：

    WITH t AS (SELECT *, row_number() OVER () AS rn FROM tbl)
SELECT * FROM (
    SELECT trunc(random() * (SELECT max(rn) FROM t))::int + 1 AS rn
    FROM   generate_series(1, 10) g
    ) r
JOIN   t USING (rn);

    每行有相同的机会被选择任意次数。

    如果您的 ID 列很少或没有间隙，那么对于大表有更快的选择：

    • 3
  2. Best Answer

    在 Postgres 9.3+ 中，您可以使用以下内容：

    select t.*
from 
  generate_series(1, 10) as x(i)
    cross join lateral
  ( select *, x.i
    from Table_Name
    -- where random() < 0.01
    order by random()
    limit 1 
  ) ;

    基本上选择1个随机行，10次。

    在旧版本中，您可以使用简单的cross join（否lateral）：

    select t.*
from 
    generate_series(1, 1000) as x(i)
  cross join 
    Table_Name as t
    -- where random() < 0.01
    order by random()
    limit 10
  ) t ;

    它创建了表的 1000 倍副本（因此每行存在 1000 次），然后使用与查询相同的方法选择 10 行。如果副本数 (1000) 与所需行数 (10) 相比足够大，则概率几乎等于替换后的概率。

    这第二个查询的性能当然会很糟糕，即使是小表也是如此。

    • 2

相关问题