programing

포스트그레스 집약함수에서의 DISIGNITY ON

cafebook 2023. 3. 19. 18:30
반응형

포스트그레스 집약함수에서의 DISIGNITY 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을 사용합니다.

어떻게 할 수 요?DISTINCT ONPostgres post post post post post post post post post post?

제가 발견한 가장 간단한 것은DISTINCT에 걸쳐서jsonb(json이 아닙니다!)(jsonb_build_objectobjects) jsonb ) 、 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

중앙 테이블이 있고 테이블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

심플한 ★★★★★★★★★★★★★★★★★★★★★★★★★★」DISTINCT조작은...애초에 "직접 교차 결합"에서 행을 곱하지 않도록 합니다.먼저 집계한 후 가입합니다.참조:

선택한 몇 개의 행을 반환하는 데 최적

실제로 테이블 전체를 취득하는 것이 아니라, 한 번에 1장 또는 몇 장만 선택한 사진을 집계하여 취득하는 것을 전제로 하고 있습니다.서브쿼리는 빠르고 우아합니다.

SELECT *
FROM   photo p
CROSS  JOIN LATERAL (
   SELECT json_agg(c) AS comments
   FROM   comment c
   WHERE  photo_id = p.photo_id
   ) c1
CROSS  JOIN LATERAL (
   SELECT json_agg(t) AS tags
   FROM   photo_tag pt
   JOIN   tag       t USING (tag_id)
   WHERE  pt.photo_id = p.photo_id
   ) t
WHERE  p.photo_id = 2;  -- arbitrary selection

그러면 다음에서 전체 행이 반환됩니다.comment ★★★★★★★★★★★★★★★★★」tagJSON 어어어이sonsonsonsonsonsonsonsonsonsonsonsonson.행은 시도와 같이 곱셈이 아니지만 기본 테이블에서와 같은 "구분"에 불과합니다.

기본 데이터에서 중복 항목을 추가로 접으려면 아래를 참조하십시오.

주의:

  • LATERAL ★★★★★★★★★★★★★★★★★」json_agg()Postgres 9.3 이후가 필요합니다.

  • json_agg(c) 말하다json_agg(c.*).

  • 우리는 할 필요가 없다LEFT JOIN왜냐하면 집합 함수는json_agg()는 항상 행을 반환합니다.

일반적으로 열의 하위 집합만 필요합니다(적어도 중복은 제외).photo_id:

SELECT *
FROM   photo p
CROSS  JOIN LATERAL (
   SELECT json_agg(json_build_object('comment_id', comment_id
                                   , 'comment', comment)) AS comments
   FROM   comment
   WHERE  photo_id = p.photo_id
   ) c
CROSS  JOIN LATERAL (
   SELECT json_agg(t) AS tags
   FROM   photo_tag pt
   JOIN   tag       t USING (tag_id)
   WHERE  pt.photo_id = p.photo_id
   ) t
WHERE  p.photo_id = 2;

json_build_object() 는 Postgres 9.4에서 도입되었습니다.이전 버전에서는 번거로웠습니다.ROW생성자가 열 이름을 보존하지 않습니다.단, 일반적인 회피책이 있습니다.

또한 JSON 키 이름을 자유롭게 선택할 수 있으므로 열 이름을 고집할 필요가 없습니다.

모든 행 또는 대부분의 행을 반환하는 데 최적

SELECT p.*
     , COALESCE(c1.comments, '[]') AS comments
     , COALESCE(t.tags, '[]') AS tags
FROM   photo p
LEFT   JOIN (
   SELECT photo_id
        , json_agg(json_build_object('comment_id', comment_id
                                   , 'comment', comment)) AS comments
   FROM   comment c
   GROUP  BY 1
   ) c1 USING (photo_id)
LEFT  JOIN LATERAL (
   SELECT photo_id , json_agg(t) AS tags
   FROM   photo_tag pt
   JOIN   tag       t USING (tag_id)
   GROUP  BY 1
   ) t USING (photo_id);

충분한 행을 취득하면, 이 값은,LATERAL서브쿼리Postgres 9.3+에서 동작합니다.

주의:USING절을 지정합니다.이렇게 하면 편리하게 사용할 수 있습니다.SELECT *외부 쿼리에서 중복 열을 가져오지 않고photo_id사용하지 않았다.SELECT *삭제된 답변은 태그 없음/주석 없음에 대해 NULL 대신 빈 JSON 어레이를 원하는 것으로 나타나기 때문입니다.

기본 테이블의 기존 중복도 제거합니다.

그냥 하면 안 돼json_agg(DISTINCT json_build_object(...))데이터 유형에 대한 등식 연산자가 없기 때문입니다.json를 참조해 주세요.

보다 나은 방법은 여러 가지가 있습니다.

SELECT *
FROM   photo p
CROSS  JOIN LATERAL (
   SELECT json_agg(to_json(c1.comment)) AS comments1
        , json_agg(json_build_object('comment', c1.comment)) AS comments2
        , json_agg(to_json(c1)) AS comments3
   FROM  (
      SELECT DISTINCT c.comment  -- folding dupes here
      FROM   comment c
      WHERE  c.photo_id = p.photo_id
   -- ORDER  BY comment --  any particular order?
      ) c1
   ) c2
CROSS  JOIN LATERAL (
   SELECT jsonb_agg(DISTINCT t) AS tags  -- demonstrating jsonb_agg
   FROM   photo_tag pt
   JOIN   tag       t USING (tag_id)
   WHERE  pt.photo_id = p.photo_id
   ) t
WHERE  p.photo_id = 2;

에서의 4가지 기술 시연comments1,comments2,comments3및 (계속)tags.

db <>여기에 추가
구식: Postgres 9.3에 백패치된 sqlfiddle, Postgres 9.6에 백패치된 sqlfiddle

코멘트에 기재되어 있듯이 json_agg는 행을 오브젝트로 시리얼화하지 않고 전달된 값의 JSON 배열을 구축합니다.필요하실 겁니다row_to_json으로 JSON 오브젝트로 변환합니다.json_agg'CHANGE: 'CHANGE: 'CHANGE: 'CHANGE: 'CHANGE:

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

언급URL : https://stackoverflow.com/questions/30077639/distinct-on-in-an-aggregate-function-in-postgres

반응형

'programing' 카테고리의 다른 글

쉼표를 사용하여 NSString을 분할합니다.  (0) 2023.03.29
Base64는 Javascript 객체를 인코딩합니다.  (0) 2023.03.29
사용자 지정 포트에 Wordpress 다중 사이트(네트워크)를 설치할 수 없음  (0) 2023.03.19
체크아웃 후 주문 데이터 가져오기  (0) 2023.03.19
보내기 전에 $.ajax를 중지  (0) 2023.03.19

'programing'의 다른글

  • 현재글포스트그레스 집약함수에서의 DISIGNITY ON

관련글

댓글

티스토리툴바