如何在PostgreSQL中随机返回多行

Question

PostgreSQL版本：9.6.18

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

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;

上面提到的代码中，我将limit 1修改为limit 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

您可以通过random((进行订购，它简化了查询

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;