postgresql 为什么添加额外的列和另一个表中的另一个LEFT OUTER JOIN会导致原始查询中的值发生更改？

问题

我正在创建一个reddit克隆，它允许用户创建帖子，对其他人的帖子投票，并在其他人的帖子上留言。当我查询数据库以获得所有帖子的列表时，我希望我的查询返回如下表所示的数据。

预期表

| 帖子标识|赞成票数|否决票数|评论数量|
| - -|- -|- -|- -|
| 一个|2个|第0页|五个|
| 2个|一个|第0页|第0页|
| 三个|第0页|一个|第0页|
| 四个|第0页|一个|四个|
不幸的是，我对如何设置查询有一些误解。当我查询数据库时，查询的结果如下所示。

意外的表

| 帖子标识|赞成票数|否决票数|评论数量|
| - -|- -|- -|- -|
| 一个|10个|第0页|五个|
| 2个|一个|第0页|第0页|
| 三个|第0页|一个|第0页|
| 四个|第0页|四个|四个|
由于某种原因，一个帖子的赞成票数量等于评论数乘以赞成票或反对票的数量。

SQL Alchemy查询和SQL版本

第一个

SQL炼金术模型

class Post(Base):
  __tablename__ = "posts"

  id = Column(Integer, primary_key=True, nullable=False)
  title = Column(String, nullable=False)
  content = Column(String, nullable=False)
  published = Column(Boolean, server_default="True", nullable=False)
  created_at = Column(TIMESTAMP(timezone=True), 
    server_default=text('now()'), nullable=False)
  owner_id = Column(Integer, ForeignKey("users.id", 
    ondelete="CASCADE"), nullable=False)
  owner = relationship("User", backref="posts")

class User(Base): 
  __tablename__ = "users"

  id = Column(Integer, primary_key=True, nullable=False)
  username = Column(String, nullable=False, unique=True)
  email = Column(String, nullable=False, unique=True)
  password = Column(String, nullable=False)
  created_at = Column(TIMESTAMP(timezone=True), 
    server_default=text('now()'), nullable=False)

class Vote(Base):
  __tablename__ = "votes"

  user_id = Column(Integer, ForeignKey("users.id", 
    ondelete="CASCADE"), primary_key=True)
  user = relationship("User", backref="votes")
  post_id = Column(Integer, ForeignKey("posts.id", 
    ondelete="CASCADE"), primary_key=True)
  post = relationship("Post", backref="votes")
  upvote = Column(Boolean, nullable=False)

class Comment(Base):
  __tablename__ = "comments"

  id = Column(Integer, primary_key=True, nullable=False)
  owner_id = Column(Integer, ForeignKey("users.id", 
    ondelete="CASCADE"))
  owner = relationship("User", backref="comments")
  post_id = Column(Integer, ForeignKey("posts.id", 
    ondelete="CASCADE"))
  post = relationship("Post", backref="comments")
  content = Column(String, nullable=False)
  created_at = Column(TIMESTAMP(timezone=True), 
    server_default=text('now()'), nullable=False)

我尝试过的

第一个查询

我试着删除评论表上的第二个左连接和试图计算评论数的查询。下面的查询可以正确计算赞成票和反对票的数量。
第一个
| 帖子标识|赞成票数|否决票数|
| - -|- -|- -|
| 一个|2个|第0页|
| 2个|一个|第0页|
| 三个|第0页|一个|
| 四个|第0页|一个|

第二个查询

我也尝试只移除计算注解数目的函数，而保留第二个LEFT OUTER JOIN。当我实作下列查询时，我的数据表如下所示：
一个
| 帖子标识|赞成票数|否决票数|
| - -|- -|- -|
| 一个|10个|第0页|
| 2个|一个|第0页|
| 三个|第0页|一个|
| 四个|第0页|四个|
这使我相信问题出在第二个左外连接上，而不是其他计数函数。
当我试图将第二个LEFTOUTERJOIN添加到查询中时，跟踪upvotes和downvotes数量的计数返回了一个不正确的值。有人能解释一下为什么我的查询返回了上面的Unexpected表，以及我需要做什么修改才能返回预期的表吗？提前感谢您花时间阅读这篇文章。请让我知道，如果我可以在任何地方更清楚

我不知道Alchemy，所以下面是我对SQL的回答：
您的错误在于交叉联接了帖子的投票和评论。如果您在帖子上有两个投票和三个评论，您的联接将创建所有六个组合（2 x 3 = 6）。然后您进行计数，但当您在交叉联接的结果中计数时，您将多次计算投票和评论。
你想做的是加入投票计数和评论计数的职位：

SELECT 
  p.id AS posts_id,
  COALESCE(v.cnt_up, 0) AS num_upvotes,
  COALESCE(v.cnt_down, 0) AS num_downvotes,
  COALESCE(c.cnt, 0) AS num_comments 
FROM posts p
LEFT OUTER JOIN
(
  SELECT
    post_id,
    COUNT(*) FILTER (WHERE upvote = true) AS cnt_up,
    COUNT(*) FILTER (WHERE upvote = false) AS cnt_down
  FROM votes
  GROUP BY post_id
) v ON v.post_id = p.id
LEFT OUTER JOIN
(
  SELECT post_id, COUNT(*) AS cnt
  FROM comments
  GROUP BY post_id
) c ON c.post_id = p.id
ORDER BY p.id;

好吧，我可能不应该提到这一点，因为这被认为是不好的做法，因为它创建了一个不必要的大中间结果，并易于出错，当试图添加其他聚合结果时，但好吧，这里是：只要使用的唯一聚合函数是COUNT，就可以使用具有不同ID计数的查询：

SELECT
  p.id AS posts_id,
  COUNT(DISTINCT v.id) FILTER (WHERE v.upvote = true) AS num_upvotes,
  COUNT(DISTINCT v.id) FILTER (WHERE v.upvote = false) AS num_downvotes,
  COUNT(DISTINCT c.id) AS num_comments 
FROM posts p 
LEFT OUTER JOIN votes v ON voves.post_id = p.id
LEFT OUTER JOIN comments c ON c.post_id = p.id
GROUP BY p.id
ORDER BY p.id;

postgresql 为什么添加额外的列和另一个表中的另一个LEFT OUTER JOIN会导致原始查询中的值发生更改？

问题

预期表

意外的表

SQL Alchemy查询和SQL版本

SQL炼金术模型

第一个查询

第二个查询

1条答案

相关问题

热门标签

最新问答