postgresql - 从 PostgreSQL 中的每个组中抽取 N 个样本

Question

我有一个包含数据的表，该表有一个名为 id 的列，如下所示:

<表类="s-表"><头>id值 1值 2值 3<正文>1244550100012515517001540601200............219744200021090310024423160021209101100............

我想为每个存在的 id 取 50 个样本行，但如果存在的样本行少于 50 个，则该组只需取整组数据点。

例如，我想从 id = 1、id = 2 等中随机选择最多 50 个数据点...

我找不到任何与此类似的以前的问题，但我尝试至少在逻辑上尝试通过解决方案，我可以通过 id 迭代和联合所有查询并限制为 50:

SELECT * FROM (SELECT * FROM schema.table AS tbl WHERE tbl.id = X LIMIT 50) UNION ALL;

但很明显，您不能使用这种类型的解决方案，因为 UNION ALL 需要聚合从一个 id 到下一个 id 的输出，而我没有 id 值的列表用于代替 tbl.id = X 中的 X。

有没有一种方法可以通过收集唯一 id 值列表并合并所有结果来实现这一点，或者是否有更优化的方法可以做到这一点？

Answer 1

如果您想为每个 id 选择一个随机样本，那么您需要以某种方式随机化这些行。这是一种方法:

select * from (
  select *, row_number() over (partition by id order by random()) as u
  from schema.table
) as a
where u <= 50;

示例(限制为 3，每个 id 都有一些行号，以便您可以看到选择的随机性):

1. 设置

DROP TABLE IF EXISTS foo;

CREATE TABLE foo
(
  id int,
  value1 int,
  idrow int
  );

INSERT INTO foo
select 1 as id, (1000*random())::int as value1, generate_series(1, 100) as idrow
union all
select 2 as id, (1000*random())::int as value1, generate_series(1, 100) as idrow
union all
select 3 as id, (1000*random())::int as value1, generate_series(1, 100) as idrow;

1. 选择

select * from (
  select *, row_number() over (partition by id order by random()) as u
  from foo
) as a
where u <= 3;

输出:

<表类="s-表"><头>id值1idrow你<正文>1542611248621155743250595121004622422333396688137478923664193