在PostgreSQL中，我如何迭代表A，并使用表A中的列作为连接到表B的限制来填充表C

unhi4e5o 于 2023-04-20 发布在 PostgreSQL

关注(0)|答案(1)|浏览(89)

使用Greenplum 6（基于PostgreSQL 9.4.）时，我有以下示例表：
表SAMPLE_A：

| uniqueId | SampleSz |
| -------- | -------- |
| 1        | 25       |
| 2        | 50450    |
| 3        | 9        |

表SAMPLE_B：

| IP       | uniqueId |
| -------- | -------- |
| 1.4.4.5  | (1,2,3)  |
| 2.5.6.7  | (2)      |
| 3.4.7.8  | (1,3)    |

我正在尝试从上面创建一个新表：
对于uniqueId=1，随机抽取一组25（SAMPLE_A.SampleSz）个IP（SAMPLE_B.IP），其中SAMPLE_A.uniqueId在SAMPLE_B.uniqueId的数组中。然后迭代到下一个SAMPLE_A.uniqueId并随机抽取50450...等。
实际的表是有点复杂，但我被困在这里。
我试着写一个奇异的记录（和失败）：

select i.ip, s.uniqueId
from SAMPLE_A s
join lateral (
   select distinct ip
   from SAMPLE_B i
   where s.uniqueId = any(i.uniqueId)
-- ORDER BY random()
   LIMIT s.SampleSz
   ) i on true

这抛出了一个解组错误。即使它起作用了，也不能解决我的全部问题，但我认为这是第一步。

***更新1：所需的结果集***我打算把上面的表当作整个数据集来处理结果集，但这实际上行不通（因为它基本上会显示为我只是取消了第二个表的嵌套）。因此，让我们假设我们正在（单独）处理www.example.com和www.example.com之间的每个IP3.4.7.03.4.7.255;每个IP在SAMPLE_B中都有一条记录，其中包含所有3个uniqueId（1，2，3）.对于UniqueId 2，我会得到所有256个IP（因为它小于相关的样本大小（50，450）.我会得到25个“随机”记录1.和3，我会得到如下（9个随机记录）.显然，2将完全重叠（与1&3）;1和3可以在0到9个记录上彼此重叠：

| IP       | uniqueId |
| -------- | -------- |
| 3.4.7.25 | 3        |
| 3.4.7.5  | 3        |
| 3.4.7.7  | 3        |
| 3.4.7.8  | 3        |
| 3.4.7.84 | 3        |
| 3.4.7.61 | 3        |
| 3.4.7.112| 3        |
| 3.4.7.125| 3        |
| 3.4.7.194| 3        |
| 3.4.7.207| 3        |
| 3.4.7.11 | 3        |
| 3.4.7.8  | 1        |
| 3.4.7.1  | 1        |

postgresql

来源：https://stackoverflow.com/questions/76046804/how-in-postgresql-can-i-iterate-through-table-a-and-use-a-column-from-table-a

1条答案

按热度按时间

brqmpdu11#

您的查询在Postgres 9.4上可用。
唯一真正的问题是ORDER BY random()，它不能与SELECT DISTINCT组合，其中ORDER BY只接受来自相同SELECT列表的项。

SELECT b.ip, a.uniqueid
FROM   sample_a a
CROSS  JOIN LATERAL (
   SELECT DISTINCT b.ip
   FROM   sample_b b
   WHERE  b.uniqueid @> a.uniqueid  -- array operator
   -- ORDER BY random()  -- not possible like this!!
   LIMIT  s.samplesz
   ) b;

我切换到数组运算符，因为索引可以支持数组运算符。至少在Postgres中是这样。不确定Greenplum在GIN索引中的位置。请参阅：

PostgreSQL可以索引数组列吗？

如果你需要一个随机样本，你需要做更多的事情。一个简单的解决方案是一个子查询，比如：

SELECT b.ip, a.uniqueid
FROM   sample_a a
CROSS  JOIN LATERAL (
   SELECT *
   FROM (
      SELECT DISTINCT b.ip
      FROM   sample_b b
      WHERE  b.uniqueid @> a.uniqueid  -- array operator
      ) b1
   ORDER  BY random()
   LIMIT  s.samplesz
   ) b;

但是如果sample_b中有 * 许多 * 匹配，则性能很差。最好的技术取决于基数和您对“随机”的精确定义。
相关：

选择随机行的最佳方法PostgreSQL

赞(0）回复(0）举报 2023-04-20

我来回答

在PostgreSQL中，我如何迭代表A，并使用表A中的列作为连接到表B的限制来填充表C

1条答案

相关问题

热门标签

最新问答