从查询结果中随机选择一部分结果作为样本

Question

这个问题询问如何在SQL Server中获取随机样本记录，并得到的答案是使用TABLESAMPLE。那么在Oracle 10中是否有相应的解决方法呢？

如果没有，有没有标准的方法从查询结果中得到随机样本呢？例如，如何从通常返回数百万行的查询中获取1,000个随机行？

- Jeremy French

Answer 1

SELECT  *
FROM    (
        SELECT  *
        FROM    mytable
        ORDER BY
                dbms_random.value
        )
WHERE rownum <= 1000

- Quassnoi

---

DBMS_RANDOM是PL/SQL中的函数，在Oracle中没有纯SQL方法生成随机数。需要进行上下文切换操作。 - Quassnoi

在一个有40,000行的表格上，这个查询只需要0.1秒，而基于SAMPLE(n)的查询只需要0.02秒（快了5倍）。因此对于我的数据量来说，速度并不慢... - Julien Kronegg

Answer 2

SAMPLE子句将为您提供表中所有行的随机抽样百分比。

例如，在这里我们获取了25%的行：

SELECT * FROM emp SAMPLE(25)

以下SQL（使用其中一种分析函数）将为您提供表中特定值的每个出现次数的随机样本（类似于GROUP BY）。

这里我们抽取了每个值的10个样本：

SELECT * FROM (
SELECT job, sal, ROW_NUMBER()
OVER (
PARTITION BY job ORDER BY job
) SampleCount FROM emp
)
WHERE SampleCount <= 10

- grokster

---

大多数情况下我们无法获得25%的行。相反，我们会得到一些不确定数量的行。原始数据集中的每一行都有同样的机会（这里是1/4）被选择。 - Mr. Tao

SEED(n)选项允许你始终得到相同的样本，因此具有相同的行数（当需要可重复的结果时可能很有用），例如SELECT * from emp SAMPLE(25) SEED(1)。 - Julien Kronegg

使用一个有40,000行和SAMPLE(25)的表，我看到返回的记录在24.41%到25.36%之间。 - Julien Kronegg

Answer 3

这不是一个完美的答案，但会获得更好的性能。

SELECT  *
FROM    (
    SELECT  *
    FROM    mytable sample (0.01)
    ORDER BY
            dbms_random.value
    )
WHERE rownum <= 1000

如果您真的想要1000行，则示例会给您实际表格的百分比，您需要调整该数字。但通常我只需要任意数量的行，因此我不限制结果。在我的具有200万行的数据库上，我可以获得2秒而非60秒。

select * from mytable sample (0.01)

- Arturo Hernandez

---

如果样本要具有统计无偏性，这并不是一个很好的方法。因为内部查询的结果是按顺序排列的，首个数值被选中的机会比后面的数值更大。试想一下，表格中的最后一行被选中的可能性是多么小！ - Baumann

由于内部查询按未索引的值排序，数据库必须在返回第一行之前计算到最后一行的值。因为它被选中的概率与任何其他记录一样大。 - Arturo Hernandez

@Baumann：dbms_random.value是由数据库生成的随机值，因此行不应被认为是_真正_有序的（这只是为了使每个通过SAMPLE(n)选择的行具有相同的概率出现在返回的1000行中）。 - Julien Kronegg

第一次查询结合了两种最佳方式（使用SAMPLE(n)进行快速随机选取和使用rownum<=1000精确定义返回结果数量）。 - Julien Kronegg

在我的情况下，我想在现有的生产表上绘制一些逼真的测试数据。通过对样本大小进行微调，这正好满足了我的需求。 - Wildcat Matt

Answer 4

我知道这个问题已经被回答了，但是由于这里的访问量如此之高，我想添加一个使用SAMPLE子句的版本，但仍然允许首先过滤行：

with cte1 as (
    select *
    from t_your_table
    where your_column = 'ABC'
)
select * from cte1 sample (5)

需要注意的是，基本选择需要一个ROWID列，这意味着它可能无法在某些视图中使用。

- Thomas Tschernich

Answer 5

样例函数用于在 ORACLE 中的示例数据。因此，您可以尝试以下操作：

SELECT * FROM TABLE_NAME SAMPLE(50);

这里的50是表中所含数据的百分比。因此，如果您想从100000行中获取1000行，则可以执行以下查询：

SELECT * FROM TABLE_NAME SAMPLE(1);

希望这可以帮助你。

- Ankit Bajpai

Answer 6

SELECT * FROM TABLE_NAME SAMPLE(1)

相比于确切的1/100观测数目，你只会得到大约1%的份额。可能的原因是Oracle为每个观测生成一个随机标志，表示是否包含在所生成的样本中。在这样的生成过程中，参数1（1%）扮演了每个观测被选入样本的概率角色。

如果这是真的，实际的样本大小分布将是二项式的。

- Hors2force

Answer 7

应该可以这样做：

SELECT * 
FROM table_name
WHERE primary_key IN (SELECT primary_key 
                      FROM
                      (
                        SELECT primary_key, SYS.DBMS_RANDOM.RANDOM 
                        FROM table_name 
                        ORDER BY 2
                      )
                      WHERE rownum <= 10 );

- BASMA SHAWKY

Answer 8

我们被分配任务，从代理商列表中选择仅两个记录...即每个代理商在一周时间内选择2个随机记录等等....以下是我们得到的，并且它可以工作。

with summary as (
Select Dbms_Random.Random As Ran_Number,
             colmn1,
             colm2,
             colm3
             Row_Number() Over(Partition By col2 Order By Dbms_Random.Random) As Rank
    From table1, table2
 Where Table1.Id = Table2.Id
 Order By Dbms_Random.Random Asc)
Select tab1.col2,
             tab1.col4,
             tab1.col5,
    From Summary s
 Where s.Rank <= 2;

- ChrisNZak

Answer 9

假设您正在尝试从名为my_table的表中选择恰好1,000个随机行。以下是一种方法：

select
    *
from
    (
        select
            row_number() over(order by dbms_random.value) as random_id,
            x.*
        from
            my_table x
    )
where
    random_id <= 1000
;

这与@Quassnoi发布的答案略有不同。它们的成本和执行时间相同，唯一的区别是您可以选择用于获取样本的随机数。

- Arturo Sbr