[SQL] PIVOT (행을 열로 돌려버렷)

해당 포스팅은 Oracle기반으로 작성되었습니다.

 

Pivot에 대해서 약간 사전적으로 이야기를 하면

데이터를 재구성하여 특정 기준에 따라 행 데이터를 열 데이터로 변환하는 기능입니다.

즉 하나의 행의 값들을 여러개의 열로 변환시켜주는 것입니다.

 

 

Pivot의 구조는 다음과 같습니다.

-- <> 생략가능
Select 열
  FROM 테이블명 <테이블별칭>
 Pivot (
    집계함수(값)
    FOR 피벗기준열 in( 값1 < as 별칭 >, 값2 ...) 

 ) <피벗테이블별칭>;

Pivot은 집계함수 사용하는데 집계 함수는 데이터를 요약할때 사용되는 것으로

SUM,COUNT,AVG등을 사용하는데 즉, Pivot에서는 집계함수가 필요하다고 볼수있습니다.

 

이론만 보면 세밀한 경우를 설명하기 힘드니까 실전으로 알아가보도록 하겠습니다.
WITH절을 이용해서 해당 테이블이 없어도 실습할 수 있도록 SQL문을 작성해 봤습니다.

 

실전 1 - 기본사용

with TEST_TB AS (
SELECT '철수' as 학생
     ,  1   as 상벌점
     , '수학' as 부여자
  FROM Dual
 UNION ALL
SELECT '도히', 5, '체육' FROM Dual
 UNION ALL
SELECT '영수', -1,'국어' FROM Dual
 UNION ALL
SELECT '철수', 2,'체육' FROM Dual
)

select aaa.*    -- a.부여자는 안된다.
 from TEST_TB a
 PIVOT ( count(a.상벌점) for 학생 IN ( '철수' as 김철수
                                     , '도히')) aaa;

 

부여자마다 각 학생들에 대한 상벌점을 COUNT를 출력을 해줍니다.

그러고보니 나머지 학생들은 출력이 안되었는데 

PIVOT의 단점은 반환할 열의 이름을 명시적으로 지정해야합니다.

즉, 지정하지 않은 열은 반환을 안하기 때문에 가변적인 현장에서는 사용을 하기 힘들 수 있습니다.

 

만약 부여자를 제거하고 싶으면 서브쿼리를 이용하면 된다.

 

실전2 - 여러 열의 pivot사용

COUNT랑 SUM를 같이 보고 싶을 수 도 있다. 

한번 부여자도 제거할겸 같이 진행해보겠습니다

with TEST_TB AS (
SELECT '철수' as 학생
     ,  1     as 상벌점
     , '수학' as 부여자
  FROM Dual
 UNION ALL
SELECT '도히', 5, '체육' FROM Dual
 UNION ALL
SELECT '영수', -1,'국어' FROM Dual
 UNION ALL
SELECT '철수', 2,'체육' FROM Dual
)

select pvtTB.*
 from (SELECT  학생,상벌점 from  TEST_TB) a
 PIVOT( COUNT(a.상벌점) as CNT
      , SUM(a.상벌점)   as 총점  for 학생 IN ( '철수' as 김철수
                                             , '도히')) pvtTB;

 

여기서 중요한건 여러개의 열을 할때는 열에 대한 별칭을 줘야한다.
 별칭을 줘야하는 이유는 열이 겹쳐서 명확하지 않기 떄문이다. 

즉 겹치지 않게만 하면 되므로 2개일때는 한개만 줘도 괜찮다. 

 

select pvtTB.*
 from (SELECT  학생,상벌점 from  TEST_TB) a
 PIVOT( COUNT(a.상벌점) as CNT
      , SUM(a.상벌점)   for 학생 IN ( '철수' as 김철수
                                       , '도히')) pvtTB;

     겹치지 않기때문에 총점을 주지 않았음에도 정상으로 실행이 된다.

 

만약 별칭을 2개이상을 안준곳이 있으면 해당 메세지가 나올 것이다.

열의 정의가 애매합니다!

 

실전 3 - Null 관련 

 

with TEST_TB AS (
SELECT '철수' as 학생
     ,  1   as 상벌점
     , '수학' as 부여자
  FROM Dual
 UNION ALL
SELECT '도히', 5, '체육' FROM Dual
 UNION ALL
SELECT '영수', -1,'국어' FROM Dual
 UNION ALL
SELECT '철수', 2,'체육' FROM Dual
)

select pvtTB.*
 from TEST_TB
 PIVOT( COUNT(상벌점) as CNT
      , SUM(상벌점)   as 총점  for 학생 IN ( '철수' as 김철수
                                           , '도히')) pvtTB;

 

보면 총점 부분에는 null이 존재한다. 그럼 NVL을 사용하여 하면 될것같지만..!

-- 에러발생!
 PIVOT( COUNT(상벌점) as CNT
      , NVL(SUM(상벌점),0)   as 총점  for 학생 IN ( '철수' as 김철수
                                                   , '도히')) pvtTB;

 

엄멈머.. 에러가 발생한다.

피벗 작업 내에서는 합계함수가 있어야한다

 

그럼 안에 NVL을 넣어볼까

select pvtTB.*
 from TEST_TB
 PIVOT( COUNT(상벌점) as CNT
      , SUM(NVL(상벌점,0))   as 총점  for 학생 IN ( '철수' as 김철수
                                       , '도히')) pvtTB;

그래도 결과는 NULL이 있음을 알 수 있다.

 

이유는  실제테이블에서 확인해보면 정말 결과값이 없음을 알 수 있다.

select 부여자,학생,SUM(NVL(상벌점,0))
 from TEST_TB
 group by 부여자, 학생

 

따라서 NULL처리를 고려해야한다면 피벗이후에 SELECT문에 고려해야한다는 점도 알아야한다.

 

결과물

with TEST_TB AS (
SELECT '철수' as 학생
     ,  1   as 상벌점
     , '수학' as 부여자
  FROM Dual
 UNION ALL
SELECT '도히', 5, '체육' FROM Dual
 UNION ALL
SELECT '영수', -1,'국어' FROM Dual
 UNION ALL
SELECT '철수', 2,'체육'  	FROM Dual
)

select pvtTB.부여자, 김철수_cnt, nvl(김철수_총점,0),도히_cnt, nvl(도히_총점,0)
 from TEST_TB
 PIVOT( COUNT(상벌점) as CNT
      , SUM(상벌점)   as 총점  for 학생 IN ( '철수' as 김철수
                                     , '도히' AS 도히)) pvtTB;

 

 

즉 PIVOT은 행을 열로 바꾸기에는 좋지만

단점도 명확하게 존재한다.

NULL에대해서 잘 생각해야하고

사용하려는 열을 확실하게 명시해야한다.