在postgres中的聚合函数中的sql – DISTINCT ON

对于我的问题,我们有一个模式,一张照片有很多标签和许多评论.所以如果我有一个查询,我想要所有的注释和标签,它会将行乘以一起.所以如果一张照片有2个标签和13个评论,我得到26行的那张照片：

SELECT
        tag.name,comment.comment_id
FROM
        photo
        LEFT OUTER JOIN comment ON comment.photo_id = photo.photo_id
        LEFT OUTER JOIN photo_tag ON photo_tag.photo_id = photo.photo_id
        LEFT OUTER JOIN tag ON photo_tag.tag_id = tag.tag_id

这对大多数事情都很好,但这意味着如果我的GROUP BY然后是json_agg(tag.*),我得到第一个标签的13个副本,第二个标签的13个副本.

SELECT json_agg(tag.name) as tags
FROM
        photo
        LEFT OUTER JOIN comment ON comment.photo_id = photo.photo_id
        LEFT OUTER JOIN photo_tag ON photo_tag.photo_id = photo.photo_id
        LEFT OUTER JOIN tag ON photo_tag.tag_id = tag.tag_id
GROUP BY photo.photo_id

相反,我想要一个仅“郊区”和“城市”的数组,如下所示：

[
      {"tag_id":1,"name":"suburban"},{"tag_id":2,"name":"city"}
 ]

我可以使用json_agg(DISTINCT tag.name),但是当我想将整行作为json时,这只会产生一个标签名称数组.我想要json_agg(DISTINCT ON(tag.name)tag.*),但这显然不是有效的sql.

如何在Postgres中的聚合函数中模拟DISTINCT ON？

每当你有一个中央表,并希望左连接到表A中的许多行,并且还将其连接到表B中的许多行时,您会收到这些复制行的问题.如果你不小心,它可以特别地抛弃聚合功能,如COUNT和SUM所以我认为你需要为每个照片和每个照片分别构建你的标签,然后将它们加在一起：

WITH tags AS (
  SELECT  photo.photo_id,json_agg(row_to_json(tag.*)) AS tags
  FROM    photo
  LEFT OUTER JOIN photo_tag on photo_tag.photo_id = photo.photo_id
  LEFT OUTER JOIN tag ON photo_tag.tag_id = tag.tag_id
  GROUP BY photo.photo_id
),comments AS (
  SELECT  photo.photo_id,json_agg(row_to_json(comment.*)) AS comments
  FROM    photo
  LEFT OUTER JOIN comment ON comment.photo_id = photo.photo_id
  GROUP BY photo.photo_id
)
SELECT  COALESCE(tags.photo_id,comments.photo_id) AS photo_id,tags.tags,comments.comments
FROM    tags
FULL OUTER JOIN comments
ON      tags.photo_id = comments.photo_id

编辑：如果你真的想加入一起,没有CTE,这看起来像是给出正确的结果：

SELECT  photo.photo_id,to_json(array_agg(DISTINCT tag.*)) AS tags,to_json(array_agg(DISTINCT comment.*)) AS comments
FROM    photo
LEFT OUTER JOIN comment ON comment.photo_id = photo.photo_id
LEFT OUTER JOIN photo_tag on photo_tag.photo_id = photo.photo_id
LEFT OUTER JOIN tag ON photo_tag.tag_id = tag.tag_id
GROUP BY photo.photo_id

原文链接：https://www.f2er.com/javaschema/281886.html

在postgres中的聚合函数中的sql – DISTINCT ON

猜你在找的设计模式相关文章