将表自连接n次

Question

将表自连接n次

3

我正在尝试解决一个生物学中的“查找给定一组生物的核心基因组”的问题。基本上，给定一个生物列表，找到它们共同拥有的所有基因。为了抽象掉生物学，你可以想象在一组人中查找所有最喜欢的颜色（一个人可以有多个最喜欢的颜色）。

数据库表格应该长这样：

name | fav_colour
john | red
john | blue
john | green
jason | red
jason | blue
matt | red
matt | teal

用户可以指定一组名称，如[john，jason]以得到[red，blue]，或[john]以获得[red，blue，green]，或[john，jason，matt]以获得[red]。

我试图通过进行 n 次自连接来解决此问题，其中 n 是提供的名称数量。

有什么办法可以让我对表进行 n 次自连接以解决任何数量的名称的问题吗？ 我尝试通过 Postgres 函数来实现这个目标，但无法弄清楚 n 次自连接部分... 任何帮助或指向正确方向的指针都将不胜感激。

不幸的是，我不能更改架构以使这些类型的查询更容易。

- player87

1

连接(join)方法并不是我想要的... 我的第一个想法是使用聚合(aggregation)，使用group by和count(*)计算每个基因有多少个生物体，然后使用having count = 生物体数量。另一个想法是为每个生物体串联查询，并使用INTERSECT获取共同元素。 - joshp

用户如何“指定一组名称”？我们可以假设它们在一个只有一个名字列的表中吗？ - philipxy

@philipxy 我希望有一个存储过程，可以接受一个值数组并对其进行解析和计数。数组中的项目数将为n，这将是所需的自连接数量。 - player87

请将其编辑到您的问题中。否则它怎么能被回答呢？此外，更多关于您尝试捕获的“自连接”的内容。例如另一个评论中的“即名称列在n个表中是唯一的”。在关系代数中，如果表行相同，则使用重命名进行自连接，除非它们是常量因此微不足道*。（自连接只是不相关的。）（请参见我的编辑答案。）尝试编写您所考虑的表达式和/或示例。顺便说一句，由于所有的数组、循环和计数，您显然没有考虑关系。 - philipxy

你想要进行“关系除法”，通过一个名称表将名称-颜色表进行划分。 - philipxy

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Gordon Linoff · Accepted Answer

我认为你不需要进行自连接。你可以使用聚合和HAVING子句：

with t(name, fav_colour) as (
      values ('john', 'red'),
             ('john', 'blue'),
             ('john', 'green'),
             ('jason', 'red'),
             ('jason', 'blue'),
             ('matt', 'red'),
             ('matt', 'teal')
     )
select fav_colour
from t
where name in ('john', 'jason')
group by fav_colour
having count(*) = 2;

"2"的值是列表中名称的数量。更改IN列表和计数是您需要进行的唯一更改。