如何在PostgreSQL中随机返回多行？

Question

PostgreSQL版本: 9.6.18

我想知道是否可以从表中随机选择多行(可能基于对表中给定列的值的随机选择，例如主键、序列等)？假设我有一个包含20行的表，我希望随机返回4行。在Googling搜索之后，我发现对于1行，建议同时使用select子句中的偏移、随机()和限制作为解决方案。因此，我尝试修改这个概念，以使其适应于随机返回多行，而不仅仅是一行。下面是我的测试用例：

with testtab as
(
    select 'pkey-01' as primary_key, 'value-01' as colval union all
    select 'pkey-02' as primary_key, 'value-02' as colval union all
    select 'pkey-03' as primary_key, 'value-03' as colval union all
    select 'pkey-04' as primary_key, 'value-04' as colval union all
    select 'pkey-05' as primary_key, 'value-05' as colval union all
    select 'pkey-06' as primary_key, 'value-06' as colval union all
    select 'pkey-07' as primary_key, 'value-07' as colval union all
    select 'pkey-08' as primary_key, 'value-08' as colval union all
    select 'pkey-09' as primary_key, 'value-09' as colval union all
    select 'pkey-10' as primary_key, 'value-10' as colval union all
    select 'pkey-11' as primary_key, 'value-11' as colval union all
    select 'pkey-12' as primary_key, 'value-12' as colval union all
    select 'pkey-13' as primary_key, 'value-13' as colval union all
    select 'pkey-14' as primary_key, 'value-14' as colval union all
    select 'pkey-15' as primary_key, 'value-15' as colval union all
    select 'pkey-16' as primary_key, 'value-16' as colval union all
    select 'pkey-17' as primary_key, 'value-17' as colval union all
    select 'pkey-18' as primary_key, 'value-18' as colval union all
    select 'pkey-19' as primary_key, 'value-19' as colval union all
    select 'pkey-20' as primary_key, 'value-20' as colval
)
select
    t1.primary_key,
    t1.colval
from testtab as t1 offset floor(random() * (select count(*) from testtab as t2)) limit 4;

在上述代码中，我修改了极限1以限制4，实际上在某种程度上随机返回4行，即偏移量是随机的。但问题是，返回的4行都是，始终是连续的。因此，例如，如果偏移量为3，那么查询肯定会按顺序3、4、5和6返回。

primary_key    colval
------------  ---------
pkey-03        value-03
pkey-04        value-04
pkey-05        value-05
pkey-06        value-06

我想知道是否有任何方法可以在返回的行不在连续块的情况下实现这一点？例如，查询将返回四行随机行，比如13、1、8、16等等，而不是3、4、5和6。

因此，我正在寻找类似于R中的Sample()函数或SAS中的PROC SURVEYSELECT，它可以在PostgreSQL中实现同样的功能。有可能吗？

提前谢谢。

Answer 1

您可以通过随机()进行排序，它简化了查询

with testtab as
(
    select 'pkey-01' as primary_key, 'value-01' as colval union all
    select 'pkey-02' as primary_key, 'value-02' as colval union all
    select 'pkey-03' as primary_key, 'value-03' as colval union all
    select 'pkey-04' as primary_key, 'value-04' as colval union all
    select 'pkey-05' as primary_key, 'value-05' as colval union all
    select 'pkey-06' as primary_key, 'value-06' as colval union all
    select 'pkey-07' as primary_key, 'value-07' as colval union all
    select 'pkey-08' as primary_key, 'value-08' as colval union all
    select 'pkey-09' as primary_key, 'value-09' as colval union all
    select 'pkey-10' as primary_key, 'value-10' as colval union all
    select 'pkey-11' as primary_key, 'value-11' as colval union all
    select 'pkey-12' as primary_key, 'value-12' as colval union all
    select 'pkey-13' as primary_key, 'value-13' as colval union all
    select 'pkey-14' as primary_key, 'value-14' as colval union all
    select 'pkey-15' as primary_key, 'value-15' as colval union all
    select 'pkey-16' as primary_key, 'value-16' as colval union all
    select 'pkey-17' as primary_key, 'value-17' as colval union all
    select 'pkey-18' as primary_key, 'value-18' as colval union all
    select 'pkey-19' as primary_key, 'value-19' as colval union all
    select 'pkey-20' as primary_key, 'value-20' as colval
)
select
    t1.primary_key,
    t1.colval
from testtab t1
order by random()
limit 4;