sql - 多个左连接从每个返回单行，未完成

Question

这是建立在另一个问题上，

Left join and only last row from right

我尝试使用相同的策略编写第二个连接。我在我的 dev macbook pro 上运行 postgres 9.1.4。请在此处查看此简化示例:

SELECT * FROM (
    SELECT 
      post.*,
      comment.*,
      edit.*,
      ROW_NUMBER() OVER (PARTITION BY post.id ORDER BY edit.date_applied DESC) AS rna,
      ROW_NUMBER() OVER (PARTITION BY post.id ORDER BY comment.date_posted DESC) AS rnb
    FROM 
      post
    LEFT JOIN edit
      ON     post.id = edit.post_id
    LEFT JOIN comment
      ON     post.id = comment.post_id
    ORDER BY
      post.id DESC
) AS q
WHERE rna = 1 AND rnb = 1;

所以我想用它做的是提取所有帖子，包括最近的编辑和最近的评论。在我的数据库中，大约有 6000 个帖子，每个帖子大约有 100 条评论，每个帖子可能有 10 次编辑。

现在，如果我使用其中一个连接运行查询，而不是同时使用两个连接，查询将运行得非常快(不到一分钟，没有我想要的那么快)。但是，如果我按照上面介绍的方式运行查询，postgres 会耗尽我 SSD 上剩余的 14 GB，并在大约 5 分钟后放弃。

谁能解释为什么会发生这种情况？我希望这是我对 PARTITION BY 子句缺乏理解。从 SELECT 子句中删除连接表并在子查询和外部查询中添加 LIMIT 没有任何改变。

感谢阅读。

Answer 1

问题可能是您在一个帖子 ID 中得到了笛卡尔积。例如，如果您有 100 次编辑和 100 条评论，那么由于加入，您最终会得到 10,000 行。

解决方案是在子查询中执行row_number():

SELECT post.*, comment.*, edit.*
FROM 
  post
LEFT JOIN (select e.*,
                  ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY e.date_applied DESC) AS rna
           from edit e
           ) edit
  ON     post.id = edit.post_id and rna = 1
LEFT JOIN (select c.*,
                  ROW_NUMBER() OVER (PARTITION BY post_id ORDER BY c.date_posted DESC) AS rnb
           from comment c
          ) comment
  ON     post.id = comment.post_id and rnb = 1
ORDER BY
  post.id DESC