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

정렬 전

정렬 후