[SQL] 열을 행으로 바꾸는 방법 정리, CONECT BY LEVEL/UNION ALL/UNPIVOT

이걸로 일주일 고민한 사람의 UNPIVOT 포스팅

계좌번호 (ACCT_NO)	고객아이디 (CUST_NO)	대출종류1 금액 (LOAN_COST1)	대출종류2 금액 (LOAN_COST2)	대출종류3 금액 (LOAN_COST3)	대출종류 4 금액 (LOAN_COST4)	대출종류5 금액 (LOAN_COST5)
12345678	1234	500	0	200	0	100

---

계좌번호 (ACCT_NO)	고객번호 (CUST_NO)	대출종류 (LOAN_DF)	금액 (COST)
12345678	1234	1	500
12345678	1234	3	200
12345678	1234	5	100

위의 테이블 구조에서 아래 테이블 구조로 변경해서 사용할 일

즉 열을 행으로 바꿔야 하는 일이 생겼는데 UNPIVOT을 생각하지 못한 나를 반성하며 써본다..

 

1. CONNECT BY LEVEL  사용하기

처음에 했던 방법은 기존에 있는 소스에서 최대한 변경안 하려고  

CONNECT BY LEVEL 을 사용해서 반복시키면서 열 구조를 행으로 바꾸려고 했다.

📍참고 : CONNECT BY LEVEL은 for문처럼 활용할 수 있는 oracle에서 순번 채번할 때 주로 사용

SELECT
      A.ACCT_NO
     ,A.CUST_NO
     ,B.LOAN_LVL AS LOAN_DF
     ,DECODE(B.LOAN_LVL,1,LOAN_COST1,2,LOAN_COST2,3,LOAN_COST3,4,LOAN_COST4,5,LOAN_COST5) AS COST 
FROM
(
    SELECT A.ACCT_NO
      	 , A.CUST_NO
     	 , B.LOAN_COST1
         , B.LOAN_COST2
         , B.LOAN_COST3
         , B.LOAN_COST4
         , B.LOAN_COST5
     FROM CUST_INFO A
         ,LOAN_INFO B
     WHERE A.ACCT_NO = B.ACCT_NO
)A,
(SELECT to_char(LEVEL) LOAN_LVL
   FROM dual
   CONNECT BY LEVEL <= 5
)B

사실 이렇게 해도 되긴되는데 예제로 짠 위에 쿼리는 5개 정도지만 실제로 업무상으로는 18개의 종류가 있고 하루치 데이터만 조회해도 6만 건이 넘어서 쿼리 한번 호출하는데 50초가 넘게 걸려서 충격받고 이건 아니라 생각했다.

CONNECT BY LEVEL은 대량의 테스트 데이터를 만들거나 동일 데이터를 여러 건 만들 수 있다는걸 알았지만

이미 대용량인 데이터를 복제하니까 너무 느려서 이럴때 사용하는 건 아니라는 걸 깨닫고 다음 방법으로 넘어갔다.

 

2. UNION ALL 사용하기

이제 다시 생각해보니까 이게 더 멍청한 방법 같은데.. 시간을 줄여야만 해서 내 머리로는 이게 다였다..

📍참고 : UNION ALL은 중복제거를 하지않고 각 쿼리의 모든 결과를 포함한 합집합

SELECT
      A.ACCT_NO
     ,B.CUST_NO
     ,A.LOAN_DF
     ,A.COST
FROM
(
     SELECT A.ACCT_NO
      	  , A.CUST_NO
          , '1' AS LOAN_DF
          , B.LOAN_COST1 AS COST
     FROM CUST_INFO A
         ,LOAN_INFO B
     WHERE A.ACCT_NO = B.ACCT_NO
     UNION ALL
     SELECT A.ACCT_NO
      	  , A.CUST_NO
          , '2' AS LOAN_DF
          , B.LOAN_COST2 AS COST
     FROM CUST_INFO A
         ,LOAN_INFO B
     WHERE A.ACCT_NO = B.ACCT_NO
     UNION ALL
     SELECT A.ACCT_NO
      	  , A.CUST_NO
          , '3' AS LOAN_DF
          , B.LOAN_COST3 AS COST
     FROM CUST_INFO A
         ,LOAN_INFO B
     WHERE A.ACCT_NO = B.ACCT_NO
     UNION ALL
     SELECT A.ACCT_NO
      	  , A.CUST_NO
          , '4' AS LOAN_DF
          , B.LOAN_COST4 AS COST
     FROM CUST_INFO A
         ,LOAN_INFO B
     WHERE A.ACCT_NO = B.ACCT_NO
     SELECT A.ACCT_NO
      	  , A.CUST_NO
          , '5' AS LOAN_DF
          , B.LOAN_COST5 AS COST
     FROM CUST_INFO A
         ,LOAN_INFO B
     WHERE A.ACCT_NO = B.ACCT_NO
  )A

 

동일한 테이블에 18번 접근해서 처리하지만 LEVEL처럼 같은 조회를 18번 실행하는 게 아니니까 당연히 시간이 줄었다.

하지만 줄어도 15초이상이 걸렸고 UNION ALL 18개는 무모했고.. 멍청했고...

당연히 이상하다고 생각했으나 더 좋은 방법이 떠오르지 않았고 눈물 닦아야 해서 하루는 더 걸리겠다고 생각했다.

근데 오늘 다른 분한테 보여줬더니 그렇게 짜면 다른 사람들이 보면 기절한다고 하면서 UNPIVOT으로 바꾸는 게 어떠냐고 했다

 

3. UNPIVOT 사용하기

내 사전에 야근은 없는데.. 야근하면서 알아낸 UNPIVOT

📍참고 : UNPIVOT은 PIVOT이 행을 열로 변경해 준다면 반대로 열을 행으로 변경해 줄 때 사용

SELECT A.ACCT_NO
     , A.CUST_NO
     , LOAN_DF
     , COST
FROM
(
	SELECT A.ACCT_NO
      	  , A.CUST_NO
     	  , B.LOAN_COST1 AS COST1 
          , B.LOAN_COST2 AS COST2
          , B.LOAN_COST3 AS COST3
          , B.LOAN_COST4 AS COST4
          , B.LOAN_COST5 AS COST5
     FROM CUST_INFO A
         ,LOAN_INFO B
     WHERE A.ACCT_NO = B.ACCT_NO
  )A
  UNPIVOT (COST FOR LOAN_DF IN (COST1 AS '1', COST2 AS '2', COST3 AS '3',COST4 AS '4',COST5 AS '5'))

이걸로 바꾸고 5초로 줄었다... 5초.... 5초!!!!!!!!!!!!!!!!!

알고 보니 대용량데이터를 처리할 때는 오히려 LEVEL이나 UNION으로 중복데이터를 계속 생성하는 것보다 UNPIVOT을 사용하는 게 더 빨랐다..

이런 함수 사용하면 더 느릴 거라고 생각했는데 생각해 보면 느린데 왜 만들어줬겠어? 더 똑똑한 사람들이 저런 거 만들었을 텐데!!!!!!!

 

4. 결론

👉 CONNECT BY LEVEL

    대량의 테스트 데이터를 만들거나 동일 데이터를 여러 건 만들 때 유용
    주의점은 복제하는 데이터의 량이 많은 경우 성능장애를 발생
    보통 몇천 건 이하로만 사용하는 것을 권장

👉 UNION ALL

  동일 테이블에 여러 번 접근하여 처리하는 방법
  데이터양이 많은 경우에 권장하지 않음

👉 UNPIVOT

 오라클 11g 이상 버전에서 사용 가능
 가장 간단한 방법 중 하나로 속도도 좋은 장점

⭐️  데이터가 적을 때는 굳이 UNPIVOT을 사용할 필요 없이 CONNECT BY LEVEL이나 UNION ALL로 합쳐도 충분할 것 같아서

테이블을 변경하려는 목적에 따라서 올바른 방법을 선택하면 될 것 같다.