sql - 检查聚合数据中是否存在相关数据

Question

我正在尝试编写一个能够检查聚合数据范围内是否存在某个数据的 SQL 查询，我ARRAY_AGG在查询中使用了该函数。

架构如下所示：

Schema Browser
----------------------------
  + post (TABLE)
      postid int4(10)
      name varchar(5)

  + posttag (TABLE)
      posttagid int4(10)
      postid int4(10)
      tagid int4(10)

  + tag (TABLE)
      tagid int4(10)
      name varchar(20)

这是我使用以下命令获取所有带有标签的帖子的查询ARRAY_AGG：

SELECT * FROM post,
LATERAL (
  SELECT
    ARRAY_AGG( DISTINCT tag.name ) AS tags 
    FROM tag, posttag 
    WHERE post.postid = posttag.postid
    AND posttag.tagid = tag.tagid
) posts

它将输出这个结果集，这正是我正在寻找的：

问题是当我尝试使用标签过滤器时！例如，我想获取所有包含“node”和“test”标签的帖子。假设“test”标签的 id 为 1，“node”标签的 id 为 2。所以我正在尝试IN这样的条件tag.tagid IN (1, 2)，这就是我将如何在查询中添加 where 条件：

SELECT * FROM post,
LATERAL (
  SELECT
    ARRAY_AGG( DISTINCT tag.name ) AS tags 
    FROM tag, posttag 
    WHERE post.postid = posttag.postid
    AND posttag.tagid = tag.tagid
    AND tag.tagid IN (1, 2)
) posts

但是现在它失去了所有其他标签，好像所有帖子都只有两个标签，这是不正确的。还有一些空标签！

这是SQLFiddle游乐场。

我如何简单地检查 ID 1 和 2 是否存在于 ARRAY_AGG 中，这样它就不会改变结果？

标签： sqlpostgresqldatabase-design

Answer 1

首先以您想要的方式将所有内容连接在一起。而不是在子查询中创建标签列表并将其加入post，加入post，posttag和tag一起并使用 aGROUP BY创建标签列表。如果 a没有，我使用LEFT JOIN而不是：INNER JOINpostposttag

SELECT p.postid,
  p.name,
  ARRAY_AGG(DISTINCT t.name) AS tags
FROM post p
 LEFT JOIN posttag pt
   ON p.postid = pt.postid
 LEFT JOIN tag t
   ON pt.tagid = t.tagid
GROUP BY p.postid,
  p.name

以上将为您提供与未过滤查询相同的结果，并且它具有更好的查询计划（4.2k 与 82.5k）。使用INNER JOIN可以进一步改善（3.9k）。

至于过滤，您遇到的问题是您只过滤posttag并tag加入，而不是实际的帖子。为此，请使用子查询从具有所需标签的 中posttag进行选择。在这样的语句postid中使用它：IN

SELECT p.postid,
  p.name,
  ARRAY_AGG(DISTINCT t.name) AS tags
FROM post p
 LEFT JOIN posttag pt
   ON p.postid = pt.postid
 LEFT JOIN tag t
   ON pt.tagid = t.tagid
WHERE p.postid IN (SELECT postid FROM posttag WHERE tagid IN (1,2))
GROUP BY p.postid,
  p.name

SQLFiddle