sql - PostgreSQL:选择具有特定成员的组

Question

给出下表:

CREATE TABLE users (
    id bigserial PRIMARY KEY,
    name text NOT NULL
);

CREATE TABLE groups (
    id bigserial PRIMARY KEY
);

CREATE TABLE group_members (
    group_id bigint REFERENCES groups ON DELETE CASCADE,
    user_id bigint REFERENCES users ON DELETE CASCADE,
    PRIMARY KEY (group_id, user_id)
);

我们如何选择具有一组特定用户的组？

我们需要一个 SQL 函数，它接受一组用户 ID 并返回具有完全相同的一组用户 ID 的组 ID(来自 group_members 表)。

此外，如果索引能让您的解决方案更快，请添加索引。

Answer 1

首先，我们需要从 group_members 关系中获取“候选”行，然后通过额外的运行确保组大小与 user_ids 数组大小相同(这里我使用 CTE https://www.postgresql.org/docs/current/static/queries-with.html ):

with target(id) as (
  select * from unnest(array[2, 3])          -- here is your input
), candidates as (
  select group_id
  from group_members
  where user_id in (select id from target)   -- find all groups which include input
)
select group_id
from group_members
where group_id in (select group_id from candidates)
group by group_id
having array_length(array_agg(user_id), 1) 
  = array_length(array(select id from target), 1) -- filter out all "bigger" groups
;

使用一些示例数据进行演示:http://dbfiddle.uk/?rdbms=postgres_9.6&fiddle=a98c09f20e837dc430ac66e01c7f0dd0

此查询将利用您已有的索引，但可能值得在 group_members (user_id) 上添加一个单独的索引，以避免在 CTE 查询的第一阶段进行中间散列。

SQL 函数很简单:

create or replace function find_groups(int8[]) returns int8 as $$
  with candidates as (
    select group_id
    from group_members
    where user_id in (select * from unnest($1))
  )
  select group_id
  from group_members
  where group_id in (select group_id from candidates)
  group by group_id
  having array_length(array_agg(user_id), 1) = array_length($1, 1)
  ;
$$ language sql;

请参阅相同的 DBfiddle 进行演示。