首页 文章

Postgres深入左外连接两个级别导致笛卡尔积

提问于
浏览 次
4

给出以下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 )

我试图获得所有活动的清单，以及任何成员的数量，以及任何捐赠的总和 . 问题是捐款的总和是错误的（似乎是捐赠的笛卡尔结果 event_members） .

这是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）将返回=实际的捐款总额* event_members中的行数 . 如果我注释掉count（distinct event_members.id）和event_members left outer join，则总和是正确的 .

编辑：欧文指出我正确的方向 . 查询重写为：

SELECT events.name, COUNT(DISTINCT event_members.id), 
  select(SUM(donations.amount) from donations,profiles where donations.profile_id = profiles.id and profiles.event_id = events.id) as total_donations
    FROM            events
    LEFT OUTER JOIN event_members ON event_members.event_id = events.id
    GROUP BY events.name
sql postgresql left-join aggregate-functions

4 回答

  • 4

    当然，每次活动都会在捐赠和活动之间获得笛卡尔产品，因为两者都只与事件有关，除了事件ID之外，捐赠和event_members之间没有联系关系，这当然意味着每个成员都匹配每一个捐赠 .

    回复于
  • 0

    正如我详细解释under the referenced question，您需要首先聚合，然后加入表以避免代理 CROSS JOIN . 像这样：

    SELECT e.name, e.sum_donations, m.ct_members
FROM (
    SELECT e.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 event_id, COUNT(DISTINCT id) AS ct_members
    FROM   event_members
    GROUP  BY 1
    ) m ON m.event_id = e.id

    IF event_members.id 是主键（可以假设），您可以简化为

    COUNT(*) AS ct_members

    因为 id 保证是 UNIQUE NOT NULL . 那要快一点 .

    回复于
  • 1

    你似乎有这两个独立的结构（ -[ 表示 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
    回复于
  • 2

    当你进行查询时，你要求所有事件 - 让's say there are two, event Alpha and event Beta - and then JOIN with the members. Let'说有一个成员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的捐款总额 . 如果爱丽丝捐赠100美元，那么您要求：

    Alpha  Alice  100USD
Beta   Alice  100USD

    因此，当要求 sum total 爱丽丝出来捐赠200美元时，这并不奇怪 .

    如果您想要所有捐款的总和，您最好使用两个不同的查询 . 尝试使用单个查询尽可能地执行所有操作，这将是一个经典的SQL Antipattern（实际上是第18章中的一个，"Spaghetti Query"）：

    非预期产品在一个查询中产生所有结果的一个常见后果是笛卡尔积 . 当查询中的两个表没有限制其关系的条件时，就会发生这种情况 . 如果没有这样的限制，两个表的连接将第一个表中的每一行与另一个表中的每一行配对 . 每个这样的配对都会成为结果集的一行，最终会有比预期更多的行 .

    回复于

相关问题