postgresql 2줄을 한줄로 ..(ARRAY_AGG 함수)

LISTAGG 함수 – 오라클

LISTAGG 집계 함수는 ORDER BY 표현식에 따라 쿼리 내 각 그룹의 행 순서를 지정한 다음, 값을 연결하여 문자열 하나를 만듭니다.

ARRAY_AGG 함수 – POSTGRESQL

ARRAY_AGG 함수는 요소 세트를 배열로 집계합니다.

ARRAY_AGG 집계 함수의 호출은 결과 배열 유형을 기반으로 합니다.

• 

위에것을 아래처럼 바꾸고 싶음

• 

select
      사용자.user_id,
      사용자롤.role_id,
      롤.role_name
      from 사용자
        left join 사용자롤 on 사용자롤.user_id = 사용자.user_id
        left join 롤 on 롤.role_id = 사용자롤.role_id

결과

http://sqlfiddle.com/

여기에 ARRAY_AGG  사용

ARRAY_TO_STRING(ARRAY_AGG(사용자롤.role_id),',') as role_id,
ARRAY_TO_STRING(ARRAY_AGG(롤.role_name),',') as role_name`,

집계함수 이외의 칼럼은 GROUP BY 절에 넣어 줌.

select 
        사용자.user_id,
       ARRAY_TO_STRING(ARRAY_AGG(사용자롤.role_id), ',') as role_id, --   사용자롤.role_id,
       ARRAY_TO_STRING(ARRAY_AGG(롤.role_name), ',')    as role_name --     롤.role_name
from 사용자
           left join 사용자롤 on 사용자롤.user_id = 사용자.user_id
        left join 롤 on 롤.role_id = 사용자롤.role_id
group by tenant.id

결과

컬럼에 배열의 값을 정렬 하고 싶음.

ARRAY_AGG 구문 안에 order by 추가

     ARRAY_TO_STRING(ARRAY_AGG(사용자롤.role_id
            order by 사용자롤.role_id
            ),',')   as role_id,
    ARRAY_TO_STRING(ARRAY_AGG(롤.role_name
            order by 롤.role_name
            ),',')     as role_name`,

최종

select 
        사용자.user_id,
      ARRAY_TO_STRING(ARRAY_AGG(사용자롤.role_id
            order by 사용자롤.role_id
            ),',')   as role_id,
    ARRAY_TO_STRING(ARRAY_AGG(롤.role_name
            order by 롤.role_name
            ),',')     as role_name`,
from 사용자
        left join 사용자롤 on 사용자롤.user_id = 사용자.user_id
        left join 롤 on 롤.role_id = 사용자롤.role_id
group by tenant.id

정렬 전

• 

정렬 후

•