主页 / dba / 问题 / 235471
Accepted
Eldamir
Asked: 2019-04-24 05:01:13 +0800 CST

跨多对多关系查询“全部”

  • 772

想象一下三个表的设置，用户、组和用户组，其中用户组由每个用户和组表的简单外键组成。

User
----
id
name

Group
-----
id
name

UserGroup
---------
user_id
group_id

现在，我想编写一个查询，选择所有指定组中的所有用户。例如，从用户中选择 *，其中用户属于“group1”、“group2”和“group3”中的每一个。

使用 Django ORM 查询，我会做类似的事情

users = (
    User.objects
    .filter(user_group__group_id=group1.id)
    .filter(user_group__group_id=group2.id)
    .filter(user_group__group_id=group2.id)
)

这将为每次调用产生一个连接.filter，例如

SELECT * FROM users
INNER JOIN user_group g1 ON g1.user_id = id
INNER JOIN user_group g2 ON g2.user_id = id
INNER JOIN user_group g3 ON g3.user_id = id
WHERE g1.group_id = %s
  AND g2.group_id = %s
  AND g3.group_id = %s

如果我要查询一个更大的集合来匹配，这会变得有点麻烦。

那么有什么更好的方法来做到这一点呢？如果我要问“任何”而不是“全部”，那将是一个简单的问题

SELECT * FROM users
INNER JOIN user_group g1 ON g1.user_id = id
WHERE g1.group_id in %s

但这不是我需要的。

一个小提示：我的具体环境是在 Postgres 上，所以这里没有花哨的 MSSql 东西可以帮助我。最好，答案应该足够通用，可以在任何 SQL 风格中使用。

postgresql many-to-many

3 个回答

  1. Best Answer

    您可以使用“精美的 Postgres”功能来做到这一点 - 比“精美的 MS SQL 功能”要容易得多；）

    您可以将所有组 ID 聚合到一个数组中，然后进行比较。

    如果“全部”是指那些完全分配给这些组的用户，则可以使用以下内容：

    SELECT u.id
FROM users u
  JOIN user_group ug on ug.user_id = u.id
group by u.id
having array_agg(ug.group_id order by ug.group_id) = array[1,2,3];

    请注意，=数组的运算符取决于顺序[1,2,3]是一个不同的数组[3,1,2]，这就是为什么array_agg()使用 anorder by并且数组中的值也被排序的原因。

    如果您使用“所有”表示分配给至少这些组的那些用户（但可以分配给更多），那么您可以使用简单的“包含”运算符：

    SELECT u.id
FROM users u
  JOIN user_group ug on ug.user_id = u.id
group by u.id
having array_agg(ug.group_id order) @> array[1,2,3];

    “包含”运算符@不依赖于元素的顺序。

    如果您需要从表中返回完整的行users，您可以在派生表中进行聚合并加入：

    SELECT u.id
FROM users u
  JOIN (
    SELECT user_id
    FROM user_group 
    GROUP BY user_id    
    HAVING array_agg(group_id) @> array[1,2,3]
  ) ug on ug.user_id = u.id

    第二个查询也可以使用标准 SQL 完成：

    SELECT u.id
FROM users u
  JOIN (
    SELECT user_id
    FROM user_group 
    WHERE group_id in (1,2,3)
    GROUP BY user_id    
    HAVING count(distinct group_id) = 3
  ) ug on ug.user_id = u.id;

    此解决方案的缺点是您需要在更改 ID 列表时同步IN列表和count(..) = 3表达式的值

    • 7

  2. 您在稍后的评论中提到：

    我在某些情况下会产生超过 50 个连接

    如此多的加入改变了游戏规则。

    更好的基础查询

    通常，连接的顺序几乎是无关紧要的。Postgres 将在它认为合适的时候重新排序它们以实现最佳性能。但是，许多连接远远超出join_collaps_limit（默认8）。Postgres 不再尝试找到最佳查询计划。太多的可能性。连接的顺序变得更加重要。考虑这个查询：

    SELECT u.*
FROM   user_group g1
JOIN   user_group g2 USING (user_id)
JOIN   user_group g3 USING (user_id)
-- ...
JOIN   users u ON u.id = g1.user_id
WHERE  g1.group_id = %s               -- most selective first !
AND    g2.group_id = %s
AND    g3.group_id = %s
... 
;

    join 子句中的USING关键字至少避免了重复user_id列。但SELECT *仍会包括所有 50 多个表中的所有其他列，使SELECT列表（和数据传输）庞大且昂贵，而您可能只需要users. （你没有澄清。）

    首先放置最具选择性的谓词以从一开始就减少行数。在你的情况下，这是最稀有的群体。可以使查询便宜很多。

    为了使其更加“动态”，您可以将其包装在递归 CTE中（以及在函数或准备好的语句中），如下所示：

    具有数组索引的物化视图

    对于具有许多谓词的情况，物化视图似乎是一个诱人的选择-如果您的写入负载和要求允许：

    CREATE MATERIALIZED VIEW user_groups AS
SELECT user_id, array_agg(group_id) AS groups
FROM   user_group
GROUP  BY 1;

    看：

    然后，您可以在派生数组上添加 GIN 索引，并使用数组运算符进行查询以达到完全不同的性能水平。

    CREATE INDEX ON user_groups USING gin (groups);

SELECT user_id FROM user_groups WHERE groups @> '{1,2,3}';

    intarray

    如果user_id是数据类型integer（可能应该是），您可以使用附加模块intarray进一步优化。看：

    那么排序数组可能是有利的：

    CREATE MATERIALIZED VIEW user_groups AS
SELECT user_id, array_agg(group_id) AS groups
FROM   (SELECT user_id, group_id FROM user_group ORDER BY 1,2) ug
GROUP  BY 1;

    intarray提供额外的运算符类和索引选项。

    CREATE INDEX ON user_groups USING gin (groups gin__int_ops);

    如果您的结果数组真的很大，请考虑：

    CREATE INDEX ON user_groups USING gist (groups gist__intbig_ops);

    与之配套的查询看起来没有变化：

    SELECT user_id FROM user_groups WHERE groups @> '{1,2,3}';

    但是，在内部，它现在将使用更快的 intarray 运算符@>而不是通用数组运算符@>和匹配的索引。应该要快得多。

    • 2

  3. 这看起来像是一个经典AND场景——它适用于任何支持 SQL 的数据库服务器。而且，不需要使用数组或任何“ fancy MS SQL features”，或者实际上是 PostgreSQL 或任何服务器！

    你想要的（在标准 SQL 中）是（在这里小提琴）：

    SELECT u.id, u.name FROM the_user u
INNER JOIN user_group g1 ON g1.user_id = u.id
INNER JOIN user_group g2 ON g2.user_id = u.id
INNER JOIN user_group g3 ON g3.user_id = u.id
WHERE g1.group_id = 5
  AND g2.group_id = 6
  AND g3.group_id = 7;

    结果：

    id   name
 3  user3

    这是想要的结果——没有数组，没有花哨的东西，只是简单、诚实的 SQL :-)

    您还可以使用INTERSECT集合运算符：

    SELECT ug.user_id, u.name
  FROM user_group ug
  JOIN the_user u ON ug.user_id = u.id
  WHERE ug.group_id = 5

INTERSECT

SELECT ug.user_id, u.name
  FROM user_group ug
  JOIN the_user u ON ug.user_id = u.id
  WHERE ug.group_id = 6

INTERSECT

SELECT ug.user_id, u.name
  FROM user_group ug
  JOIN the_user u ON ug.user_id = u.id
  WHERE ug.group_id = 7;

    结果：

    user_id      name
      3     user3

    同上！可能是更好的性能明智？

    ========= DML 和 DDL =========

    Tables (DDL):

    CREATE TABLE the_user -- not "user" - can be an SQL keywowrd
(
  id INT,
  name VARCHAR (10)
);

CREATE TABLE groupe -- use the French - GROUP being a keyword!
(
  id INT,
  name VARCHAR (10)
);

CREATE TABLE user_group
(
  user_id INT,
  group_id INT
);

    Data (DML):

    INSERT INTO the_user VALUES (1, 'user1'), (2, 'user2'), (3, 'user3');

INSERT INTO groupe VALUES (5, 'group1'), (6, 'group2'), (7, 'group3');

INSERT INTO user_group VALUES (1, 6), (1, 7), (2, 5), (2, 7), (3, 5), (3, 6), (3, 7);
    • 0

相关问题