如何筛选具有多次通过关系的SQL结果 [英] How to filter SQL results in a has-many-through relation

问题描述

假设我有表student，club和student_club:

student {
    id
    name
}
club {
    id
    name
}
student_club {
    student_id
    club_id
}

我想知道如何找到足球(30)和棒球(50)俱乐部中的所有学生.
虽然此查询无效，但这是我到目前为止最接近的东西:

I want to know how to find all students in both the soccer (30) and baseball (50) club.
While this query doesn't work, it's the closest thing I have so far:

SELECT student.*
FROM   student
INNER  JOIN student_club sc ON student.id = sc.student_id
LEFT   JOIN club c ON c.id = sc.club_id
WHERE  c.id = 30 AND c.id = 50

推荐答案

我很好奇.众所周知，好奇心以杀死猫而闻名.

I was curious. And as we all know, curiosity has a reputation for killing cats.

此测试的精确蒙皮环境:

The precise cat-skinning environment for this test:

• PostgreSQL 9.0 在Debian Squeeze上具有不错的RAM和设置.
• 6.000名学生，24.000个俱乐部会员资格(数据从具有现实生活数据的类似数据库中复制.)
• 从问题的命名模式中稍微转移一下:student.id是student.stud_id，club.id是club.club_id.
• 我在此线程中以查询的作者命名，并在其中有两个索引.
• 我多次运行了所有查询以填充缓存，然后使用EXPLAIN ANALYZE选择了5个中的最好的.

• 相关指数(应该是最佳的-只要我们不了解要查询哪些俱乐部)

• PostgreSQL 9.0 on Debian Squeeze with decent RAM and settings.
• 6.000 students, 24.000 club memberships (data copied from a similar database with real life data.)
• Slight diversion from the naming schema in the question: student.id is student.stud_id and club.id is club.club_id here.
• I named the queries after their author in this thread, with an index where there are two.
• I ran all queries a couple of times to populate the cache, then I picked the best of 5 with EXPLAIN ANALYZE.

• Relevant indexes (should be the optimum - as long as we lack fore-knowledge which clubs will be queried):

ALTER TABLE student ADD CONSTRAINT student_pkey PRIMARY KEY(stud_id );
ALTER TABLE student_club ADD CONSTRAINT sc_pkey PRIMARY KEY(stud_id, club_id);
ALTER TABLE club       ADD CONSTRAINT club_pkey PRIMARY KEY(club_id );
CREATE INDEX sc_club_id_idx ON student_club (club_id);

此处大多数查询都不需要

club_pkey.
主键在PostgreSQL中自动实现唯一索引.
最后一个索引是为了弥补多列索引在PostgreSQL上:

club_pkey is not required by most queries here.
Primary keys implement unique indexes automatically In PostgreSQL.
The last index is to make up for this known shortcoming of multi-column indexes on PostgreSQL:

多列B树索引可与满足以下条件的查询条件一起使用 涉及索引列的任何子集，但索引是最 在前导(最左边)有约束时有效 列.

A multicolumn B-tree index can be used with query conditions that involve any subset of the index's columns, but the index is most efficient when there are constraints on the leading (leftmost) columns.

结果:

EXPLAIN ANALYZE中的总运行时间.

Results:

Total runtimes from EXPLAIN ANALYZE.

SELECT s.stud_id, s.name
FROM   student s
JOIN   student_club sc USING (stud_id)
WHERE  sc.club_id IN (30, 50)
GROUP  BY 1,2
HAVING COUNT(*) > 1;

---

2)欧文1:33.217毫秒

SELECT s.stud_id, s.name
FROM   student s
JOIN   (
   SELECT stud_id
   FROM   student_club
   WHERE  club_id IN (30, 50)
   GROUP  BY 1
   HAVING COUNT(*) > 1
   ) sc USING (stud_id);

---

3)马丁1:31.735毫秒

SELECT s.stud_id, s.name
   FROM   student s
   WHERE  student_id IN (
   SELECT student_id
   FROM   student_club
   WHERE  club_id = 30
   INTERSECT
   SELECT stud_id
   FROM   student_club
   WHERE  club_id = 50);

---

4)德里克:2.287毫秒

SELECT s.stud_id,  s.name
FROM   student s
WHERE  s.stud_id IN (SELECT stud_id FROM student_club WHERE club_id = 30)
AND    s.stud_id IN (SELECT stud_id FROM student_club WHERE club_id = 50);

---

5)Erwin 2:2.181毫秒

SELECT s.stud_id,  s.name
FROM   student s
WHERE  EXISTS (SELECT * FROM student_club
               WHERE  stud_id = s.stud_id AND club_id = 30)
AND    EXISTS (SELECT * FROM student_club
               WHERE  stud_id = s.stud_id AND club_id = 50);

---

6)肖恩:2.043毫秒

SELECT s.stud_id, s.name
FROM   student s
JOIN   student_club x ON s.stud_id = x.stud_id
JOIN   student_club y ON s.stud_id = y.stud_id
WHERE  x.club_id = 30
AND    y.club_id = 50;

最后三个的表现几乎相同. 4)和5)得出相同的查询计划.

The last three perform pretty much the same. 4) and 5) result in the same query plan.

漂亮的SQL，但性能跟不上.

Fancy SQL, but the performance can't keep up.

SELECT s.stud_id,  s.name
FROM   student AS s
WHERE  NOT EXISTS (
   SELECT *
   FROM   club AS c 
   WHERE  c.club_id IN (30, 50)
   AND    NOT EXISTS (
      SELECT *
      FROM   student_club AS sc 
      WHERE  sc.stud_id = s.stud_id
      AND    sc.club_id = c.club_id  
      )
   );

---

8)超级立方体2:147.497毫秒

SELECT s.stud_id,  s.name
FROM   student AS s
WHERE  NOT EXISTS (
   SELECT *
   FROM  (
      SELECT 30 AS club_id  
      UNION  ALL
      SELECT 50
      ) AS c
   WHERE NOT EXISTS (
      SELECT *
      FROM   student_club AS sc 
      WHERE  sc.stud_id = s.stud_id
      AND    sc.club_id = c.club_id  
      )
   );

正如预期的那样，这两个的性能几乎相同.查询计划会导致表扫描，计划者在这里找不到使用索引的方法.

As expected, those two perform almost the same. Query plan results in table scans, the planner doesn't find a way to use the indexes here.

WITH RECURSIVE two AS (
   SELECT 1::int AS level
        , stud_id
   FROM   student_club sc1
   WHERE  sc1.club_id = 30
   UNION
   SELECT two.level + 1 AS level
        , sc2.stud_id
   FROM   student_club sc2
   JOIN   two USING (stud_id)
   WHERE  sc2.club_id = 50
   AND    two.level = 1
   )
SELECT s.stud_id, s.student
FROM   student s
JOIN   two USING (studid)
WHERE  two.level > 1;

精美的SQL，CTE的性能不错.非常奇特的查询计划.
同样，有趣的是9.1如何处理这个问题.我将很快将此处使用的数据库集群升级到9.1.也许我会重新运行整个shebang ...

Fancy SQL, decent performance for a CTE. Very exotic query plan.
Again, would be interesting how 9.1 handles this. I am going to upgrade the db cluster used here to 9.1 soon. Maybe I'll rerun the whole shebang ...

WITH sc AS (
   SELECT stud_id
   FROM   student_club
   WHERE  club_id IN (30,50)
   GROUP  BY stud_id
   HAVING COUNT(*) > 1
   )
SELECT s.*
FROM   student s
JOIN   sc USING (stud_id);

查询2的

CTE变体).出乎意料的是，它可能会导致使用完全相同的数据的查询计划略有不同.我在student上发现了顺序扫描，其中子查询变量使用了索引.

CTE variant of query 2). Surprisingly, it can result in a slightly different query plan with the exact same data. I found a sequential scan on student, where the subquery-variant used the index.

另一个后期添加@ypercube.到底有多少种方式，真是令人惊讶.

Another late addition @ypercube. It is positively amazing, how many ways there are.

SELECT s.stud_id, s.student
FROM   student s
JOIN   student_club sc USING (stud_id)
WHERE  sc.club_id = 10                 -- member in 1st club ...
AND    NOT EXISTS (
   SELECT *
   FROM  (SELECT 14 AS club_id) AS c  -- can't be excluded for missing the 2nd
   WHERE  NOT EXISTS (
      SELECT *
      FROM   student_club AS d
      WHERE  d.stud_id = sc.stud_id
      AND    d.club_id = c.club_id
      )
   )

---

12)欧文3:2.377毫秒

@ypercube的11)实际上只是这个更简单变体的令人费解的反向方法，该方法也仍然缺少.表现几乎和顶级猫一样快.

---

12) erwin 3: 2.377 ms

@ypercube's 11) is actually just the mind-twisting reverse approach of this simpler variant, that was also still missing. Performs almost as fast as the top cats.

SELECT s.*
FROM   student s
JOIN   student_club x USING (stud_id)
WHERE  sc.club_id = 10                 -- member in 1st club ...
AND    EXISTS (                        -- ... and membership in 2nd exists
   SELECT *
   FROM   student_club AS y
   WHERE  y.stud_id = s.stud_id
   AND    y.club_id = 14
   )

13)欧文4:2.375毫秒

很难相信，但这是另一个全新的变种.我认为有超过两个成员的潜力，但它也仅以两个而跻身顶级猫之列.

13) erwin 4: 2.375 ms

Hard to believe, but here's another, genuinely new variant. I see potential for more than two memberships, but it also ranks among the top cats with just two.

SELECT s.*
FROM   student AS s
WHERE  EXISTS (
   SELECT *
   FROM   student_club AS x
   JOIN   student_club AS y USING (stud_id)
   WHERE  x.stud_id = s.stud_id
   AND    x.club_id = 14
   AND    y.club_id = 10
   )

俱乐部会员资格的动态数量

换句话说:过滤器数量不等.这个问题要求准确的 两个 俱乐部会员资格.但是许多用例必须准备数量不定的

Dynamic number of club memberships

In other words: varying number of filters. This question asked for exactly two club memberships. But many use cases have to prepare for a varying number.

此稍后的相关答案中的详细讨论:

Detailed discussion in this related later answer:

• 在WHERE子句中多次使用同一列

这篇关于如何筛选具有多次通过关系的SQL结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！