[SQL] data.world tutorial SQL Pivot 문 예제 풀이

PIVOT enables you to see rows as columns in a query result.

피벗테이블은 row 여러 개를 column 으로 옮겨서 요약 정리한다. 

PIVOT (집계값 FOR 대상컬럼 IN ("컬럼1", "컬럼2", ... "컬럼N"))

 

https://docs.data.world/documentation/sql/concepts/advanced/PIVOT.html

PIVOT

엑셀에서 원하는 결과값을 값에 넣고,  요약기준을 row 와 column 에 추가하는 것과 같다.
(이 때 column 에 추가하지 않은 것들은 무조건 모두 row 로 제공된다.)

 

---

1. 요구사항 분석

we wanted to see a column
- for each of the offices ( East, West, Center )
- with the sales
- for each product listed in it

office 와 product 단위로 sales 정보 (아마도 SUM ) 를 보고 싶다.

 

2. 데이터셋 확인

sales_pipeline 에 office, sales_value, product 모두 있다. 

 

3. 커리구조 검토

SELECT office, product, sales_value
FROM sales_pipeline
GROUP BY office, product 

 

4. 쿼리 작성

이 요구사항을 제공하는 방식은 2가지 이다.

 

a. 1차원 행으로 나열: GROUP BY office, product

pivot 없이 

 

b. 2차원 피벗테이블: office 는 컬럼에, product 는 행에, sales 를 요약정보로! 

office 가 West, Central, East 3개밖에 안되므로 컬럼으로 배치해본다. 

 

PIVOT 은 기존 테이블에 PIVOT 문만 추가해서 처리할 수는 없는 경우가 많으으므로 WITH subquery 로 재구조화해야한다. 

마치 엑셀에서 데이터셋을 지정한 상태에서 피벗테이블을 돌리는 것과 동일한 원리이다!

 

WITH 문으로 재구조화하지 않으면 이런 에러를 뱉어낸다. 

Query can not include both a 'PIVOT' and an 'WHERE','GROUP BY' or 'HAVING' clause: (line 6, column 1) 'PIVOT' query cannot contain a calculated column. Restructure your query as a 'WITH' subquery: (line 3, column 1)

 

 

5. 응용

만약 집계 기준이 한 개 더 있다면? 

we wanted to see a column
- for each of the offices( East, West, Center )
- with the sales 
- for each product listed in it
- for each quarter in 2017 

피벗테이블의 대상이 되는 t1 에 close_date 거래성사일자를 기준으로 분기 quarter 정보를 추가하였다. 

 

이 때에도 동일하게 PIVOT 문을 작성해주면 된다. 피벗테이블의 대상이 되는 데이터셋의 변화일 뿐 PIVOT 문을 사용하는 방식은 변하지 않는다. 대상컬럼 외의 값들은 모두 남아있다.

 

---

 

사실 UNPIVOT 도 있지만, 그닥 사용하지 않을것 같아서 개념만 확인했다. 

 

UNPIVOT enables you to see columns as rows in a query result.

UNPIVOT 컬럼을 통해 컬럼 한개를 여러 row 로 쪼갤 수 있다.

 

UNPIVOT (값 FOR 추가컬럼 IN ("컬럼명1" AS "추가컬럼값1", "컬럼명2" AS "추가컬럼값2", ... "컬럼명N" AS "추가컬럼값N"))

주로 카테고라이징을 컬럼으로 한 걸 row 로 풀어버릴 때 사용할 것 같다.

 

추가컬럼 대상인 컬럼명의 데이터타입이 같을 때에만 처리할 수 있다.

다른 데이터타입의 컬럼을 넣으면 에러가 뜬다: 'UNPIVOT' columns must all be of the same type: (line 9, column 1)

 

https://docs.data.world/documentation/sql/concepts/advanced/UNPIVOT.html

UNPIVOT