文章/答案/技术大牛

发布

社区首页 >问答首页 >postgres中聚合函数中的DISTINCT ON

问postgres中聚合函数中的DISTINCT ON
EN

Stack Overflow用户

提问于 2015-05-06 21:04:00

回答 3查看 29.6K关注 0票数 41

对于我的问题，我们有一个模式，其中一张照片有许多标签和许多评论。因此，如果我有一个查询，其中我想要所有的注释和标签，它将乘以行在一起。因此，如果一张照片有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 ？

aggregate-functions

sql

json

postgresql

distinct

回答 3

Stack Overflow用户

发布于 2016-03-12 11:37:58

每当您有一个中心表，并且想要将其左连接到表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

EDIT:如果你真的想在没有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

票数 25

Stack Overflow用户

发布于 2019-11-20 20:44:52

我发现的最简单的事情就是在jsonb上使用DISTINCT (而不是json!)。(jsonb_build_object创建jsonb对象)

SELECT 
   JSON_AGG(
       DISTINCT jsonb_build_object('tag_id', photo_tag.tag_id, 
                                  'name', 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

票数 21

Stack Overflow用户

发布于 2015-05-06 21:56:25

正如注释中所述，json_agg不会将行序列化为对象，而是构建一个包含传递给它的值的JSON数组。您需要使用row_to_json将行转换为JSON对象，然后使用json_agg将其聚合到数组中：

SELECT json_agg(DISTINCT row_to_json(comment)) 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

票数 3

页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持

原文链接：

https://stackoverflow.com/questions/30077639

复制

相似问题

问postgres中聚合函数中的DISTINCT ON
EN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问postgres中聚合函数中的DISTINCT ONEN

回答 3

Stack Overflow用户

Stack Overflow用户

Stack Overflow用户

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问postgres中聚合函数中的DISTINCT ON
EN