PostgreSQL WITH ORDINALITY 사용

현재 근무하고 있는 회사에 유저마다 직업가치관을 저장하는 데이터가 있다.

그리고 직업가치관은 총 4개로 이루어져 있다. 그리고 직업가치관 순위는 유저마다 다르다.

'A' ,'B', 'C', 'D'

그리고 이 네개의 데이터를 다른 테이블에 있는 직업가치관의 값과 조인하여 사용해야 했다.

 

문제는 현재 DB구조상 어떤 테이블에는 직업가치관의 id값이 저장되지만, 다른 테이블에는 'A' 라는 string 값이 저장되어 있다.

그래서 나는 id값으로 저장되어 있는 컬럼을 string로 치환하여 조인해야 했다.

{270: 'A', 271: 'B', 272: 'C', 273: 'D'}

 

찾아보니 WITH ORDINALITY 를 이용하면 될 것 같았다.

WITH ORDINALITY는 PostgreSQL의 함수(주로 unnest()) 결과에 각 행의 순번을 나타내는 새 열을 추가하는 절이다.

주로 배열이나 다른 집합 데이터를 순서대로 처리할 때 사용된다고 한다.

 

기본사용은 다음과 같다.

SELECT * FROM unnest(ARRAY['사과', '바나나', '딸기']) WITH ORDINALITY;

 

결과

unnest | ordinality
--------+------------
 사과   |          1
 바나나 |          2
 딸기   |          3

 

 

나의 경우에는 CASE문과 함께 사용했다.

select 
      r."id",
       array_agg(
                 coalesce(
                         case v
                                when 270 then 'A'
                                when 271 then 'B'
                                when 272 then 'C'
                                when 273 then 'D'
                            end, ''
                        )
                        order by ord
                    ) as "jobValue"
#생략
                left join lateral unnest(
                    array[
                        d."jobValue1Id",
                        d."jobValue2Id",
                        d."jobValue3Id",
                        d."jobValue4Id"
                    ]
                ) with ordinality as t(v, ord) 
                    on true

 

• WITH ORDINALITY 를 사용하여 각 원소에 순서 번호(ord) 를 붙여준다.

v	ord
jobValue1Id	1
jobValue2Id	2
jobValue3Id	3
jobValue4Id	4

• CASE WHEN 으로 ID → 문자열 치환: ID 값이 270이면 ‘A’, 271이면 ‘B’ … 이런 식으로 변환.
• array_agg(... ORDER BY ord) 다시 배열로 묶되(array_agg),아까 WITH ORDINALITY로 만든 순서(ord) 기준으로 정렬한다.

그럼 유저가 설정한 직업가치관 순서를 그대로 보존할 수 있다.

jobValue

{A,B,C,D}

{C,D,A,B}

 

쿼리를 작성하면서 WITH ORDINALITY 사용은 해본 적이 없었는데 이번 기회에 새로운 걸 배웠다!