主页 / dba / 问题 / 190752
Accepted
jaap3
Asked: 2017-11-14 02:22:22 +0800 CST

PostgreSQL 查找具有相同成员的组

  • 772

给定以下架构：

User
  id

Group
  id

Membership
  group_id
  user_id

(With a unique index on group_id, user_id)

我将如何选择相同的两个用户所属的组？

我现在拥有的：

SELECT   "Membership"."group_id" 
FROM     "Membership" 
WHERE    "Membership"."user_id" IN (1, 2) 
GROUP BY "Membership"."group_id" 
HAVING   COUNT("Membership"."group_id") >= 2

这似乎有效（它只返回两个用户都是其成员的组 ID），但看起来相当混乱。

有没有更好的方式来表达这个查询？

编辑

所以给出以下数据：

Users
-----
id
1
2
3

Group
-----
id
1
2
3

Membership
----------
group_id user_id
1        1
1        2
2        1
2        3
3        2
3        3

我想找到用户 1 和 2 都是其成员的组。

所以 user_id 1 和 2 的查询结果应该是组 1。对 user_id 1 和 3 的相同查询应该返回组 2。而对 user_id 2 和 3 的查询应该返回组 3。

postgresql relational-division

2 个回答

  1. Best Answer

    这称为关系划分，您的查询几乎就是您可以使用标准 SQL 执行的操作。

    在 Postgres 中写这个的另一种方法是使用数组：

    select group_id
from membership
group by group_id
having array_agg(user_id order by user_id) = array[1,3];

    array[1,3]对常量内的 ID 进行排序也很重要，因为array[1,3]它是一个与 ; 不同的数组array[3,1]。

    上面返回那些恰好由这两个成员组成的组。您的查询返回至少包含这两个成员的查询。这也可以用数组来写：

    select group_id
from membership
group by group_id
having array_agg(user_id order by user_id) @> array[1,3];

    这对数组使用“包含”运算符，测试左侧数组是否包含右侧数组的所有元素。但是左边的数组允许有更多的元素。

    使用数组检查精确匹配可能更快（因为它需要使用标准 SQL 进行额外的子查询），但我认为“至少”查询不会对性能产生很大影响。

    • 2

  2. 您的查询是一种标准的表达方式：

    FORALL x:p(x)

    在 SQL 中。另一种常见的方法是将 De Morgan 定律用于谓词逻辑，并将此谓词重写为：

    NOT EXISTS x:NOT p(x)

    通常，人们发现比重写更容易理解您已有的查询。如果您在代码中添加注释您的查询是做什么的，我认为这根本不是一个糟糕的查询。

    如果您只有几个用户要检查，则效率较低（通常）但可能更容易掌握的解决方案是为交叉路口的每个用户使用一条腿：

    select m1.group_id from membership m1 where m1.user_id = 1 
intersect 
select m1.group_id from membership m1 where m1.user_id = 2

    对于 3 个用户，这将是：

    select m1.group_id from membership m1 where m1.user_id = 1 
intersect 
select m1.group_id from membership m1 where m1.user_id = 2 
intersect 
select m1.group_id from membership m1 where m1.user_id = 3

    也就是说，我会瞄准你已经拥有的或@a_horse_with_no_name 提供的解决方案

    • 1

相关问题