[TIL 0702] SQL, Pivot Table 에 대한 이해 (5주차 과제를 바탕으로)

✔️ 피벗 테이블이란 ?

 

• 많은 양의 데이터에서 필요한 자료만을 뽑아 새롭게 표를 작성하는 것.
• 2개 이상의 기준으로 데이터를 집계할 때, 보기 쉽게 배열하여 보여주는 것.
• 표의 행과 열을 전환하는 등의 과정을 통하여 통계를 재정렬하고, 그 결과로 표 데이터를 요약하는 방법. 이 과정에 집계함수(합계, 평균 등)가 사용될 수 있다.
• 피벗 테이블의 핵심 기능은 데이터를 집계하고, 행과 열의 구조를 재정렬하며, 다양한 기준에 따라 데이터를 그룹화하여 중요한 통찰력을 얻을 수 있도록 돕는 것. written by Chatgpt4o
• 범주형 변수 2, 수치형 변수 1 자주 사용

출처 : https://m.blog.naver.com/regenesis90/222205833002

---

✔️ 예시 (서브쿼리로 PIVOT 전 단계의 테이블 만들어주기)

• food_orders 와 customers 라고 하는 테이블을 customer_id 를 기준으로 Inner Join 한 테이블 생성
• 조건 : 나이 10~59세
• cuisine_type 과 연령대를 기준으로 그룹화 하여 각 그룹에 해당하는 개수(데이터 수) 집계 (Count)

select
	f.cuisine_type cuisine_type,
	case when c.age < 20 then '10대'
		 when c.age < 30 then '20대'
		 when c.age < 40 then '30대'
		 when c.age < 50 then '40대'
		 else '50대' end 연령대,
	count(*) cnt_order
from 
	food_orders f inner join customers c on f.customer_id = c.customer_id
where 
	c.age between 10 and 59
group by 1, 2

 

위의 코드 실행 시 생성되는 테이블 예시(일부)

 

 

---

✔️ 위의 테이블에서 Pivot Table 만들기

• 위의 코드를 서브쿼리로 사용하여 외부 쿼리의 테이블로 재사용
• cuisine_type 별로 묶어준 뒤, 연령대 컬럼의 값들이 컬럼이 되도록, 그리고 생성된 컬럼들의 값들이 cnt_order 로 채워질 수 있도록 코드 작성 -> cusine_type 한 개 당 하나의 행을 가지도록 피벗팅
• 여기서 핵심은 group by cuisine_type 과 mas(if()) if 문을 통해 one_hot_encoding 을 해준 뒤, max 로 cnt_order 값을 추출!

select 
	cuisine_type,
	max(if(연령대 = '10대', cnt_order, 0)) '10대',
	max(if(연령대 = '20대', cnt_order, 0)) '20대',
	max(if(연령대 = '30대', cnt_order, 0)) '30대',
	max(if(연령대 = '40대', cnt_order, 0)) '40대',
	max(if(연령대 = '50대', cnt_order, 0)) '50대'
from(
select
	f.cuisine_type cuisine_type,
	case when c.age < 20 then '10대'
		 when c.age < 30 then '20대'
		 when c.age < 40 then '30대'
		 when c.age < 50 then '40대'
		 else '50대' end 연령대,
	count(*) cnt_order
from 
	food_orders f inner join customers c on f.customer_id = c.customer_id
where 
	c.age between 10 and 59
group by 1, 2
) sub
group by 
	cuisine_type

---

---

• 궁금증
• Why max? 
• max 집계 함수와 group by를 작성해주지 않으면 어떤 식으로 테이블이 출력될까 ?
• 먼저, 사용된 예시 데이터의 특성은
  • 아래에서 볼 수 있듯이 각 연령대와 음식 종류가 만나서 생성된 교차 셀에 값이 들어가며, 나머지 셀에는 값이 들어있지 않습니다. 이는 일종의 원-핫 인코딩 형태로, 특정 음식 종류와 연령대의 교차점에서만 값이 존재하고 다른 교차점에서는 값이 0인 것을 의미합니다.
• 그룹화(Group By) 및 집계함수
  • cuisine_type으로 데이터를 그룹화하여, 각 음식 종류별로 데이터를 묶어주게 되면
  • 그룹화된 데이터에서 특정 연령대별로 최대값을 선택합니다. 예를 들어, American 그룹에서 10대는 58, 0, 0, 0, 0 중 최대값인 58이 선택되고, 20대는 55, 30대는 54, 40대는 60, 50대는 59가 선택됩니다.
  • 만약 집계 함수를 min으로 설정하면 모든 값이 0이 선택되며, avg를 사용하면 58 / 5와 같은 평균값이 출력될 것입니다. sum을 사용하면 결과는 max와 동일하게 나옵니다.

select 
	cuisine_type,
	if(연령대 = '10대', cnt_order, 0) '10대',
	if(연령대 = '20대', cnt_order, 0) '20대',
	if(연령대 = '30대', cnt_order, 0) '30대',
	if(연령대 = '40대', cnt_order, 0) '40대',
	if(연령대 = '50대', cnt_order, 0) '50대'
from(
select
	f.cuisine_type cuisine_type,
	case when c.age < 20 then '10대'
		 when c.age < 30 then '20대'
		 when c.age < 40 then '30대'
		 when c.age < 50 then '40대'
		 else '50대' end 연령대,
	count(*) cnt_order
from 
	food_orders f inner join customers c on f.customer_id = c.customer_id
where 
	c.age between 10 and 59
group by 1, 2
) sub