[MySQL] Pivot Table 만들기

 

Pivot table 이란  2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것을 의미한다.

요런식으로 행,열 모두를 기준으로 한 데이터의 배열

 

 

 

 

만드는 방법
1. 피벗 테이블에 필요한 데이터를 가공하여 만든다.
2. 1의 데이터를 하위 쿼리로 사용하여, Pivot Table 로 만든다.

 

 

 

 

예시) 스파르타 [엑셀보다 쉽고 빠른 SQL] 5주차 숙제
: 음식 타입별, 연령별 주문건수 피벗테이블을 만들자. (연령은 10~59세 사이)

 

 

 

 

1. 필요한 데이터를 만들기 -> 음식타입과 필요한 연령별로 묶어서 개수를 카운트

select cuisine_type,
	case when age between 10 and 19 then "10대"
		when age between 20 and 29 then "20대"
		when age between 30 and 39 then "30대"
		when age between 40 and 49 then "40대"
		when age between 50 and 59 then "50대" end "연령",
		count(1) "count"
from food_orders f inner join customers c on f.customer_id= c.customer_id
where age between 10 and 59
group by 1,2

쿼리 결과

 

 

2. 이를 피벗테이블로 만들자

SELECT cuisine_type,
		max(if(연령='10대',count,0)) "10대",
		max(if(연령='20대',count,0)) "20대",
		max(if(연령='30대',count,0)) "30대",
		max(if(연령='40대',count,0)) "40대",
		max(if(연령='50대',count,0)) "50대"
FROM
(
select cuisine_type,
	case when age between 10 and 19 then "10대"
		when age between 20 and 29 then "20대"
		when age between 30 and 39 then "30대"
		when age between 40 and 49 then "40대"
		when age between 50 and 59 then "50대" end "연령",
		count(1) "count"
from food_orders f inner join customers c on f.customer_id= c.customer_id
where age between 10 and 59
group by 1,2
) a
group by 1

 

1의 데이터를 서브쿼리로 넣어줬다.

연령별로 열의 기준을 설정하고, group by로 행별로 집계하도록 설정하여 피벗 테이블 완성

 

---

 

SQL에서는 GROUP BY가 사용되면, 결과 집합은 그룹 단위로 요약된다.
이때 사용되는 SUM(), AVG(), COUNT(), GROUP_CONCAT() 같은 집계 함수는 각 그룹 내부의 데이터에 대해서만 동작한다.

 

 

GROUP BY로 인해 각 cuisine_type에 해당하는 모든 행들이 하나의 그룹으로 묶인다.
그 그룹에 대해 MAX(...) 함수가 동작하는데, 이 안에서 IF(연령 = '10대', count, 0)은 그룹 내의 모든 행에 대해 실행된다.

SQL은 "10대가 없음을 감지한다"는 식으로 스스로 판단하지 않는다.
MAX(IF(...)) 구조가 그룹 안의 모든 행을 검사하면서, 10대가 없으면 전부 0이 되니까 자연스럽게 0이 반환되는 것이다.
따라서 이 구조는 "존재 유무를 묻는 조건"이 아니라, "존재하지 않으면 자연스럽게 결과가 0으로만 구성"되는 것이다.

 

 

 

 

 

 

+)이때 피벗 테이블을 만들기 위해서는 max()를 일단 사용하라고 배웠는데, 이유는 나중에 알게되면 추가.. 

 

완성된 피벗 테이블