가로 데이터를 세로로 바꾸기 SQL_피벗
- 책 'SQL 레시피' 를 참고하여 작성한 글 입니다.
가로 데이터를 세로로 만들려면 먼저 어떤 것을 봐야 할까?
우선 '가로 데이터를 세로로 만들려면' 이러한 행위를 우리는 피벗 테이블 이라고 부릅니다.
그럼, 피벗(PIVOT)이란 무엇일까요?
이런거라고 할 수 있습니다.
사실 이런 겁니다. 스타트업에서 BM을 바꿀 때, 피보팅 한다라고도 많이하죠.
피벗 테이블(pivot table)
표의 행과 열을 전환하는 등의 과정을 통하여 통계를 재정렬하고, 그 결과로 표 데이터를 요약하는 방법. 이에는
집계함수(합계, 평균 등)가 사용될 수 있습니다.
Wikipedia
퀘스트, 우리는 아래 테이블을 피봇! 하고 싶습니다.
아래는 4분기 매출 테이블인데 저는 이 테이블을 year, quarter, sales 값으로 만들고 싶습니다.
일단 저기 q1,q2,q3,q4 친구들을 왼쪽에 있는 행으로 오게 하고 싶습니다. 그러려면 인덱스 값을 4개 만들어 줘서 이를 붙여야 합니다. 우선 가로로 된 테이블의 열이 예를 들어 4개로 고정되어있다면 -> 행으로 전개할 수가 4개로 정해져있다는 말입니다.
이 순서대로 우리는 피벗 테이블을 만들 수 있습니다.
1. 일련 변호를 가진 테이블 만들기
2. 피벗 테이블을 만들고 이를 CROSS JOIN 하기
3. 피벗 테이블을 결합하기
1. 일련 변호를 가진 테이블 만들기
SELECT 1 AS idx
UNION ALL SELECT 2 AS idx
UNION ALL SELECT 3 AS idx
UNION ALL SELECT 4 AS idx
네! 일단 q1~q4가 열에서 행으로 와야하니까 인덱스 값을 4개로 만들어 주었습니다.
2. 피벗 테이블을 만들고 이를 CROSS JOIN 하기
왜 CROSS JOIN이고 , 이건 뭘까요?
- CROSS JOIN(상호 조인)은 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인하는 기능이다.
처음에 이해하기 힘드니까 일단 차근 차근 코드를 쳐보면서 이해해 보겠습니다.
SELECT *
FROM quarterly_sales AS q
CROSS JOIN ( SELECT 1 AS idx
UNION ALL SELECT 2 AS idx
UNION ALL SELECT 3 AS idx
UNION ALL SELECT 4 AS idx
)AS p
이렇게 원래 테이블에 CROSS JOIN을 해주면 모든 값에 idx 값이 붙는 것을 알 수 있습니다.
이렇게 하면 year별로 각 열에 해당하는 값이 생기니까, 테이블을 재정렬하기 쉬워집니다.
그래서 year 별로 나눴고, quarter 테이블을 만들어 줍니다.
SELECT q.year
-- q1에서 q4까지의 레이블 이름 출력하기
,CASE
WHEN p.idx = 1 THEN 'q1'
WHEN p.idx = 2 THEN 'q2'
WHEN p.idx = 3 THEN 'q3'
WHEN p.idx = 4 THEN 'q4'
END AS quarter
FROM quarterly_sales AS q
CROSS JOIN ( SELECT 1 AS idx
UNION ALL SELECT 2 AS idx
UNION ALL SELECT 3 AS idx
UNION ALL SELECT 4 AS idx
)AS p
이렇게 각 year, quarter 별로 나눠진 걸 볼 수 있습니다.
3. 피벗 테이블을 결합하기
이제 이를 매출액 데이터와 결합해 줍니다.
SELECT q.year
,CASE
WHEN p.idx = 1 THEN 'q1'
WHEN p.idx = 2 THEN 'q2'
WHEN p.idx = 3 THEN 'q3'
WHEN p.idx = 4 THEN 'q4'
END AS quarter
,CASE
WHEN p.idx = 1 THEN q.q1
WHEN p.idx = 2 THEN q.q2
WHEN p.idx = 3 THEN q.q3
WHEN p.idx = 4 THEN q.q4
END AS sales
FROM quarterly_sales AS q
CROSS JOIN ( SELECT 1 AS idx
UNION ALL SELECT 2 AS idx
UNION ALL SELECT 3 AS idx
UNION ALL SELECT 4 AS idx
)AS p
결과
그럼 이렇게 피벗 테이블이 완성됩니다.