结合使用UNNEST和JOIN

Question

我希望能够unnest()在具有许多JOINs的复杂SQL查询中使用PostgreSQL中的函数。这是查询示例：

SELECT 9 as keyword_id, COUNT(DISTINCT mentions.id) as total, tags.parent_id as tag_id
FROM mentions
INNER JOIN taggings ON taggings.mention_id = mentions.id
INNER JOIN tags ON tags.id = taggings.tag_id
WHERE mentions.taglist && ARRAY[9] AND mentions.search_id = 3
GROUP BY tags.parent_id

我想在taggings这里删除该表，因为我的mentions表具有一个名为 taglist* 的 整数数组
字段，该字段由所有链接的tag id组成。
*mentions

我尝试了以下操作：

SELECT 9 as keyword_id, COUNT(DISTINCT mentions.id) as total, tags.parent_id as tag_id 
FROM mentions 
INNER JOIN tags ON tags.id IN (SELECT unnest(taglist))
WHERE mentions.taglist && ARRAY[9] AND mentions.search_id = 3 
GROUP BY tags.parent_id

此方法有效，但带来的结果与第一个查询不同。

所以我要做的是SELECT unnest(taglist)在JOIN查询中使用的结果来补偿taggings表。

我怎样才能做到这一点？

UPDATE： taglist与提及的标记ID的列表相同。

Answer 1

从技术上讲，您的查询可能会这样工作（不能完全确定此查询的目标）：

SELECT 9 AS keyword_id, count(DISTINCT m.id) AS total, t.parent_id AS tag_id
FROM  (
    SELECT  unnest(m.taglist) AS tag_id
    FROM    mentions m
    WHERE   m.search_id = 3
    AND     9 = ANY (m.taglist)
    ) m 
JOIN   tags t  USING (tag_id) -- assumes tag.tag_id!
GROUP  BY t.parent_id;

但是，在我看来，您在这里走错了方向。通常，将删除冗余阵列taglist并保留规范化的数据库架构。然后，您的原始查询应该可以很好地服务，仅使用别名缩短语法：

SELECT 9 AS keyword_id, count(DISTINCT m.id) AS total, t.parent_id AS tag_id
FROM   mentions m
JOIN   taggings mt ON mt.mention_id = m.id
JOIN   tags     t  ON t.id = mt.tag_id
WHERE  9 = ANY (m.taglist)
AND    m.search_id = 3
GROUP  BY t.parent_id;

解开谜团

<rant> 您“不同结果”的根本原因是不幸的命名约定，这是一些 知识上有挑战性的ORM 强加给人的。
我说的** ~~id~~** 是列名。切勿在具有多个表的数据库中使用此反模式。是的，这基本上意味着 任何
数据库。一旦加入一堆表（这就是您在数据库中 所做 的），您最终会得到一堆名为的列id。毫无意义。
名为表的ID列tag应为 tag_id （除非有另一个描述性名称）。永远不要id。 </rant>

您的查询无意中计数，tags而不是mentions：

SELECT 25 AS keyword_id, count(m.id) AS total, t.parent_id AS tag_id
FROM  (
    SELECT unnest(m.taglist) AS id
    FROM   mentions m
    WHERE  m.search_id = 4
    AND    25 = ANY (m.taglist)
    ) m
JOIN   tags t USING (id)
GROUP  BY t.parent_id;

它应该这样工作：

SELECT 25 AS keyword_id, count( **DISTINCT** m.id) AS total, t.parent_id
FROM  (
    SELECT **m.id** , unnest(m.taglist) **AS tag_id**
    FROM   mentions m
    WHERE  m.search_id = 4
    AND    25 = ANY (m.taglist)
    ) m
JOIN   tags t **ON t.id =  m.tag_id**
GROUP  BY t.parent_id;

我还DISTINCT向您添加count()了在查询过程中迷路的。