PostgreSQL where all in array

Question

什么是最简单和最快的方法来实现一个子句，其中数组中的所有元素都必须匹配，而不仅仅是使用IN时的一个元素？毕竟它应该像mongodb的$all一样运行。

考虑到对话用户是对话ID和用户ID之间的连接表，我有类似以下的想法:

WHERE (conversations_users.user_id ALL IN (1,2))

---

更新 16.07.12

关于结构和情况，添加更多信息：

1. 联接表非常简单：

                 Table "public.conversations_users"
        Column      |  Type   | Modifiers | Storage | Description 
   -----------------+---------+-----------+---------+-------------
    conversation_id | integer |           | plain   | 
    user_id         | integer |           | plain   | 
   

2. 一次会话中有多个用户，每个用户都可以属于多个不同的会话。为了查找一次会话中的所有用户，我使用了这个联接表。

3. 最终，我想找到一个 Ruby on Rails 的 scope，它可以根据参与者来找到相应的对话 - 例如：

scope :between, ->(*users) {
  joins(:users).where('conversations_users.user_id all in (?)', users.map(&:id))
}

更新 23.07.12

我的问题是关于查找完全匹配的人。因此：

如果查询 (1,2)，那么 (1,2,3) 之间的对话不会匹配。

- pex

---

你能添加一些示例输入和输出数据以使其更清晰吗？ - user330315

感谢您的评论，@a_horse_with_no_name。已添加案例和模式。 - pex

在查找用户之间的对话(1,2)时，您是否也希望在结果中包括(1,2,3)之间的对话，还是只要显示(1,2)之间的对话-无他人参与？ - Erwin Brandstetter

@ErwinBrandstetter 只在 (1,2) 之间。 - pex

在这种情况下，您需要使用我答案中的注释部分。或者您可以使用戈登答案中的第二个查询。到目前为止，所有其他答案都不足以解决这个问题 - 您没有明确声明它。 - Erwin Brandstetter

@ErwinBrandstetter 对不起，你是对的 - 是我的错。 - pex

Answer 1

如果联结表遵循良好的实践并定义了一个唯一复合键，即用于防止重复行的约束条件，则以下类似于简单查询的内容应该可以完成。

select conversation_id from conversations_users where user_id in (1, 2)
group by conversation_id having count(*) = 2

需要注意的是，数字2是用户ID列表的长度。如果用户ID列表的长度发生变化，这个数字也需要相应改变。如果你不能假设你的连接表中不包含重复记录，则将"count(*)"改为"count(distinct user_id)"，这可能会牺牲一些性能。

此查询找到包括所有指定用户的所有对话，即使对话还包括其他用户。

如果您只想要 完全 包含特定用户集合的对话，一个方法是在where子句中使用嵌套子查询，如下所示。请注意，第一行和最后一行与原始查询相同，只有中间两行是新的。

select conversation_id from conversations_users where user_id in (1, 2)
   and conversation_id not in
   (select conversation_id from conversations_users where user_id not in (1,2))
group by conversation_id having count(*) = 2

如果您的数据库支持，等效地，您可以使用集合差运算符。以下是Oracle语法示例（对于Postgres或DB2，请将关键字“minus”更改为“except”）。

select conversation_id from conversations_users where user_id in (1, 2)
  group by conversation_id having count(*) = 2
minus
  select conversation_id from conversations_users where user_id not in (1,2)

一个良好的查询优化器应该将最后两种变化视为相同，但请确认您特定的数据库是否如此。例如，Oracle 11GR2查询计划在应用减号运算符之前对两组会话ID进行排序，但跳过了最后一个查询的排序步骤。因此，根据多个因素（如行数、核心数、缓存、索引等），任何一种查询计划都可能更快。

- Alex Blakemore

---

实际上计算违反行是没有用的。我们一旦找到一个就足够了。在这种情况下，使用EXISTS半连接通常更快。 - Erwin Brandstetter

这不计算违规行。它只是作为 where 子句的一部分将其过滤掉。顶层 where 子句在对 having 子句进行任何计数之前生效。 - Alex Blakemore

没错，我的第一句话不够准确，实际上你并没有在计数。不需要收集所有违规行，这才是正确的说法。不过，EXISTS 更快的部分仍然适用。别误会，我已经为你的答案点赞了，因为它简单而聪明。我的评论只是想挤出更多的性能。 - Erwin Brandstetter

对于您的第一个查询，您可以使用 ... having count(distinct user_id) = 2，那么您就不需要唯一约束。 - maniek

@ErwinBrandstetter 我尝试了您建议的 Oracle 存在半连接的变化形式，得到了与上一个查询相同的查询计划。我也看到过存在半连接有助于提高性能的情况，但并不总是有所帮助。我认为上面最后一个查询更易读。 - Alex Blakemore

@AlexBlakemore：这个问题涉及到PostgreSQL，这里有所不同。我认为“NOT EXISTS”更清晰易懂，当然这是个人口味的问题。 - Erwin Brandstetter

Answer 2

我将这些用户折叠到一个数组中。同时，我使用了一个CTE（在WITH子句中的内容）使其更易读。

=> select * from conversations_users ;
 conversation_id | user_id
-----------------+---------
               1 |       1
               1 |       2
               2 |       1
               2 |       3
               3 |       1
               3 |       2
(6 rows)       

=> WITH users_on_conversation AS (
  SELECT conversation_id, array_agg(user_id) as users
  FROM conversations_users
  WHERE user_id in (1, 2) --filter here for performance                                                                                      
  GROUP BY conversation_id
)
SELECT * FROM users_on_conversation
WHERE users @> array[1, 2];
 conversation_id | users
-----------------+-------
               1 | {1,2}
               3 | {1,2}
(2 rows) 

编辑（一些资源）

• 数组函数： http://www.postgresql.org/docs/9.1/static/functions-array.html
• CTEs： http://www.postgresql.org/docs/9.1/static/queries-with.html

- hgmnz

Answer 3

这样可以保留ActiveRecord对象。

在下面的例子中，我想知道与数组中所有代码相关联的时间表。

codes = [8,9]

Timesheet.joins(:codes).select('count(*) as count, timesheets.*').
           where('codes.id': codes).
           group('timesheets.id').
           having('count(*) = ?', codes.length)

您需要具备完整的ActiveRecord对象才能进行相关操作。如果您希望它成为一个真正的作用域，您可以使用上述示例并使用.pluck(:id)传递结果。

- Don Pflaster

---

我认为这不会按预期工作。 这将返回所有时间表，其中至少有一个这些代码，且正好两个代码，但不一定同时出现。 - jprince

在我的表格中适配后，它完美地运作了，谢谢！@jprince GROUP 子句确保时间表行的匹配代码出现多少次，因此如果时间表只有其中 1 个代码，则 HAVING 子句将排除它。据我所知，这仅会失败（如果 codes.id 或您的替换不是唯一的）。 - Henry Blyth

@HenryBlyth 我不记得是什么引起了我最初的评论，但我认为你是正确的。 - jprince

Answer 4

虽然 @Alex 的答案使用 IN 和 count() 是最简单的解决方案，但我认为这个 PL/pgSQL 函数会更快：

CREATE OR REPLACE FUNCTION f_conversations_among_users(_user_arr int[])
  RETURNS SETOF conversations AS
$BODY$
DECLARE
    _sql text := '
    SELECT c.*
    FROM   conversations c';
    i int;
BEGIN

FOREACH i IN ARRAY _user_arr LOOP
    _sql  := _sql  || '
    JOIN   conversations_users x' || i || ' USING (conversation_id)';
END LOOP;

_sql  := _sql  || '
    WHERE  TRUE';

FOREACH i IN ARRAY _user_arr LOOP
    _sql  := _sql  || '
    AND    x' || i || '.user_id = ' || i;
END LOOP;

/* uncomment for conversations with exact list of users and no more
_sql  := _sql  || '
    AND    NOT EXISTS (
        SELECT 1
        FROM   conversations_users u
        WHERE  u.conversation_id = c.conversation_id
        AND    u.user_id <> ALL (_user_arr)
        )
*/

-- RAISE NOTICE '%', _sql;
RETURN QUERY EXECUTE _sql;

END;
$BODY$ LANGUAGE plpgsql VOLATILE;

电话：

SELECT * FROM f_conversations_among_users('{1,2}')

该函数动态构建并执行一个形如以下的查询语句：

SELECT c.*
FROM   conversations c
JOIN   conversations_users x1 USING (conversation_id)
JOIN   conversations_users x2 USING (conversation_id)
...
WHERE  TRUE
AND    x1.user_id = 1
AND    x2.user_id = 2
...

这个表单在关系除法查询的广泛测试中表现最佳。

你也可以在你的应用程序中构建查询，但我假设你想使用一个数组参数。此外，这可能是最快的方法。

任何一种查询都需要像下面这样的索引才能快速：

CREATE INDEX conversations_users_user_id_idx ON conversations_users (user_id);

一个基于多列的主键（或唯一键）(user_id, conversation_id)同样可以，但是一个基于(conversation_id, user_id)的主键（就像你可能已经有的那样！）会比较差。您可以在上面的链接中找到简短的理由，或者在dba.SE上与此相关的问题下找到综合评估。

我还假设您在conversations.conversation_id上有一个主键。

您能否使用EXPLAIN ANALYZE运行@Alex的查询和这个函数，并报告您的发现？

请注意，这两个解决方案都可以找到至少包含数组中用户的对话 - 包括其他用户的对话。
如果您想排除这些，请取消我的函数中的附加子句（或将其添加到任何其他查询中）。

告诉我如果您需要更多关于该函数特性的解释。

- Erwin Brandstetter

Answer 5

根据@Alex Blakemore的回答，Rails 4版本上Conversation类的等效作用域应该是：

# Conversations exactly with users array
scope :by_users, -> (users) { 
                           self.by_any_of_users(users)
                             .group("conversations.id")
                             .having("COUNT(*) = ?", users.length) -
                           joins(:conversations_users)
                             .where("conversations_users.user_id NOT IN (?)", users)
}
# generates an IN clause
scope :by_any_of_users, -> (users) { joins(:conversations_users).where(conversations_users: { user_id: users }).distinct }

请注意，您可以对其进行优化，而不是使用Rails的 - （减号），您可以使用 .where（“NOT IN”），但这样会使阅读变得非常复杂。

- Bruno Peres

---

我不理解这个查询。你能解释一下吗？ - Vishal

Answer 6

创建一个包含所有可能值的映射表，并使用它。

select 
    t1.col from conversations_users as t1 
    inner join mapping_table as map on t1.user_id=map.user_id
group by 
    t1.col  
having  
    count(distinct conversations_users.user_id)=
    (select count(distinct user_id) from mapping)

- Madhivanan

Answer 7

select id from conversations where not exists(
    select * from conversations_users cu 
    where cu.conversation_id=conversations.id 
    and cu.user_id not in(1,2,3)        
)

这可以很容易地转换为Rails作用域。

- maniek

---

实际上，现在我不清楚是什么意思：查找完全给定的人之间的对话，还是在完全给定的人加上其他人之间的对话，或者是来自给定集合的人（可能不是所有人）之间的对话而不包括其他人？我的答案是针对最后一种情况。 - maniek

这也会选择仅与某些用户或根本没有用户交谈的对话。 - Erwin Brandstetter

Answer 8

我猜你并不想开始搞临时表。

你的问题并不清楚，是想要与确切一组用户进行对话，还是想要与超集进行对话。以下是针对超集的回答：

with users as (select user_id from users where user_id in (<list>)
              ),
     conv  as (select conversation_id, user_id
               from conversations_users
               where user_id in (<list>)
              )
select distinct conversation_id
from users u left outer join
     conv c
     on u.user_id = c.user_id
where c.conversation_id is not null

为了使此查询正常工作，它假定您在用户和对话用户中都有 user_id 索引。

对于确切的集合...

with users as (select user_id from users where user_id in (<list>)
              ),
     conv  as (select conversation_id, user_id
               from conversations_users
               where user_id in (<list>)
              )
select distinct conversation_id
from users u full outer join
     conv c
     on u.user_id = c.user_id
where c.conversation_id is not null and u.user_id is not null

- Gordon Linoff

Answer 9

基于Alex Blakemore的回答

select conversation_id
from conversations_users cu
where user_id in (1, 2)
group by conversation_id 
having count(distinct user_id) = 2

我已经找到了一个替代查询，其目的相同，即查找包含user_1和user_2（忽略其他用户）的对话的conversation_id。

select *
from conversations_users cu1
where 2 = (
    select count(distinct user_id)
    from conversations_users cu2
    where user_id in (1, 2) and cu1.conversation_id = cu2.conversation_id
)

根据Postgres通过解释查询语句执行的分析，它会变慢，我猜这是真的，因为有更多的条件需要评估，至少对于conversations_users的每一行，子查询将作为相关子查询被执行。这个查询的优点是你不需要分组，因此你可以选择conversations_users表的其他字段。在某些情况下（比如我的情况），这可能很方便。

- Victor