在Postgres中进行两级左外连接会导致笛卡尔积。

Question

给定以下4个表：

CREATE TABLE events ( id, name )
CREATE TABLE profiles ( id, event_id )
CREATE TABLE donations ( amount, profile_id )
CREATE TABLE event_members( id, event_id, user_id )

我正在尝试获取所有事件的列表，以及任何成员的数量和捐款总额。问题在于捐款总额返回错误（似乎是捐款*活动成员数的笛卡尔积结果）。

以下是SQL查询语句（Postgres）

SELECT events.name, COUNT(DISTINCT event_members.id), SUM(donations.amount)
FROM            events
LEFT OUTER JOIN profiles      ON events.id = profiles.event_id
LEFT OUTER JOIN donations     ON donations.profile_id = profiles.id
LEFT OUTER JOIN event_members ON event_members.event_id = events.id
GROUP BY events.name

捐款总额（donations.amount）返回的值等于实际捐款总额乘以活动成员行数。如果我注释掉了count(distinct event_members.id)和event_members left outer join，总和是正确的。

- John P

---

顺便提一下，将sum(donations.amount)更改为count(distinct donations.id)确实会得到正确的捐款数量。 - John P

嗨，我刚刚重新排列了你问题中的“创建表”语句，以反映“连接”的顺序。 - biziclop

紧密相关：https://dev59.com/sWcs5IYBdhLWcg3w8oXK - Erwin Brandstetter

嗨，你可以将自己的解决方案制作成一个新答案（如果你认为它是最好的，甚至可以接受它 :)）。 - biziclop

使用EXPLAIN ANALYZE测试性能。相关子查询（就像你在解决方案中的那样）通常会慢得多。如果结果集中的行数很少，而基表中的行数很多，它仍然可能获胜。 - Erwin Brandstetter

通过我的相对较小的样本结果集（2个事件，每个事件约200个成员、个人资料和捐款），性能相差不到几毫秒。 - John P

Answer 1

如我在回答参考问题中所解释的，您需要在连接之前进行聚合以避免代理CROSS JOIN。像这样：

SELECT e.name, e.sum_donations, m.ct_members
FROM (
    SELECT e.id AS event_id, e.name, SUM(d.amount) AS sum_donations
    FROM   events         e
    LEFT   JOIN profiles  p ON p.event_id = e.id
    LEFT   JOIN donations d ON d.profile_id = p.id
    GROUP  BY 1, 2
    ) e
LEFT   JOIN (
    SELECT m.event_id, count(DISTINCT m.id) AS ct_members
    FROM   event_members m
    GROUP  BY 1
    ) m USING (event_id);

如果 event_members.id 是主键，那么在表中 id 保证为唯一的，因此您可以从计数中删除 DISTINCT：

count(*) AS ct_members

- Erwin Brandstetter

---

为什么在第一个子查询中使用GROUP BY event.id和event.name？event.name可能依赖于event.id。 - biziclop

@biziclop：因为我可能需要这样做。每个SELECT项都必须在GROUP BY列表中或者被用于聚合函数。自PostgreSQL 9.1以来，表的主键覆盖了该表的所有列，但从问题中并不清楚它是否是主键，我们正在运行Postgres 9.1+。 - Erwin Brandstetter

对于我的样本结果集（2个事件，在其他表中每个200个），所有解决方案的执行时间相差不到3毫秒。虽然如此，我确实喜欢这种解决方案的结构。 - John P

Answer 2

您似乎有这两个独立的结构体（-[表示1-N关联）：

events -[ profiles -[ donations
events -[ event members

我将第二个查询包装成了一个子查询：

SELECT events.name,
  member_count.the_member_count
  COUNT(DISTINCT event_members.id),
  SUM(donations.amount)

FROM            events
LEFT OUTER JOIN profiles      ON events.id = profiles.event_id
LEFT OUTER JOIN donations     ON donations.profile_id = profiles.id

LEFT OUTER JOIN (
  SELECT
    event_id,
    COUNT(*) AS the_member_count
  FROM event_members
  GROUP BY event_id
) AS member_count
  ON member_count.event_id = events.id

GROUP BY events.name

- biziclop

---

警告：我的查询可能不是一个语法正确的PostgreSQL查询。 - biziclop

这个方法可行 - 赞一个将选择语句放在连接中的新颖方法。性能几乎与我的<Edit>解决方案完全相同。为了后人，需要将member_count.the_member_count添加到group by中。 - John P

你确定我的答案比Erwin的好吗？如果你觉得不合适，可以取消接受我的答案 :) - biziclop

@JohnP：不确定为什么你接受了这个。基本思路是正确的，但查询无效。 - Erwin Brandstetter

Answer 3

当然，每个事件都会得到捐赠和事件的笛卡尔积，因为两者都只与事件相关，除了事件ID之外，捐赠和事件成员之间没有连接关系，这意味着每个成员都与每个捐赠匹配。

- wich

Answer 4

当你进行查询时，你要求获取所有事件——假设有两个事件，事件Alpha和事件Beta——然后与成员表进行连接。假设有一个名为Alice的成员参加了这两个事件。

SELECT events.name, COUNT(DISTINCT event_members.id), SUM(donations.amount)
FROM            events
LEFT OUTER JOIN profiles      ON events.id = profiles.event_id
LEFT OUTER JOIN donations     ON donations.profile_id = profiles.id
LEFT OUTER JOIN event_members ON event_members.event_id = events.id
GROUP BY events.name

在每一行中，您要求Alice捐赠的总额。如果Alice捐赠了100美元，那么您会这样问：

Alpha  Alice  100USD
Beta   Alice  100USD

因此，当要求总捐款时，阿丽斯捐赠了200美元，这并不奇怪。

如果您想要所有捐款的总和，最好使用两个不同的查询。尝试使用单个查询完成所有操作虽然可能，但会成为经典的SQL反模式（实际上是第18章“意大利面查询”）：

意外产物

在一个查询中生成所有结果的常见后果是笛卡尔积。当查询中的两个表没有限制它们之间的关系时，就会发生这种情况。如果没有这样的限制，则两个表的连接将第一个表中的每一行与另一个表中的每一行配对。每个这样的配对都成为结果集的一行，您最终会得到比预期更多的行。

- LSerni

---

很不幸，我正在使用的报告系统必须从单个查询中获取所有结果。在列列表中嵌入子选择语句解决了这个问题。 - John P