TIL_250502 (금) SQL 피벗테이블 복습 및 문제풀이

카테고리 없음

TIL_250502 (금) SQL 피벗테이블 복습 및 문제풀이

elya0919 2025. 5. 2. 17:57

어제 복습을 하다 피벗테이블 부분이 막혀서 오늘 다시 개념부터 천천히 이해해보려고 한다.

피벗테이블(Pivot table) ?

원래 "행(세로)"으로 된 데이터를 "열(가로)"로 변환해 시각적으로 보기 쉽게 "그룹화"해서 정리한 것

📦 예시) 음식 주문 데이터가 이렇게 있을 때: 컬럼과 그 안에 데이터가 세로로 나열되어 있는데.

연령대성별주문수

연령대	성별	주문수
20대	female	8
30대	male	5
30대	female	12

피벗테이블을 사용하면 성별이라는 컬럼 자체를 male과 female로 다시 그룹화해서 연령대별 + 성별별 주문 수를 확인할 수 있다 .

연령대남성 주문 수여성 주문 수

연령대	male	female
20대	10	8
30대	5	12

다시 정리하자면 피벗테이블은 비교 분석을 위한 시각적 재구성
2개의 기준이 하나 값으로 구성
“기준 열은 고정하고, 분류 기준은 가로로 펼치며, 숫자 값을 그 안에 채워넣는 것”
쉽게 말해, 분류 기준을 가로로 펼치기 위해 컬럼으로 만들고
그 분류 기준에 들어가던 데이터 값을 아래로 쭉 나열한다

집계함수(COUNT, SUM, MAX 등)를 함께 사용하는 이유는,

분류 기준이 컬럼으로 바뀌었을 때, 그 안에 어떤 값을 넣을지 조건에 따라 계산해야 하기 때문이다.
즉, 단순히 컬럼을 만든다고 값이 자동으로 채워지지 않기 때문에,

조건을 걸기 위한 IF() + 요약하기 위한 집계함수
가 함께 쓰인다.

피벗테이블 사용 x 결과

select 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대'
	else '* 10세 미만'
end 'age_segment', gender, COUNT(customer_id)
from customers
group by 1,2
order by 1

피벗테이블 사용 결과
max( if(...) ) 사용
=> max라는 집계 함수안에 count 집계 함수 중복사용이 불가능, 서브쿼리에서 먼저 연산하고 바깥 쿼리에서 대입

SELECT t.age_segment,
MAX(if (gender = 'male', count_customer, null)) male,
MAX(if (gender = 'female', count_customer, null)) female
from 
(
select 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대'
	else '* 10세 미만'
end age_segment, gender, COUNT(customer_id) count_customer
from customers
group by 1,2
) t
group by 1
order by 1

피벗테이블 사용 시 데이터가 훨씬 잘 정리되어 시각적으로 보기 쉬워진다.

피벗테이블 사용 2
= max( if(...) ) 말고 count( if(...) ) 사용해서 서브쿼리 없이 더 짧고 간결한 쿼리 작성하기

select 
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대'
	else '* 10세 미만'end age_segment,
	count(if (gender = 'male', customer_id,0)) male,
	count(if (gender = 'male', customer_id,0)) female
from customers
group by 1
order by 1

혼자 문제를 만들어봤다.

경기 지역의 아메리칸 타입 식당의 연령대별 방문객 수를 구하시오.

처음에는 피벗테이블을 사용하지 않고 문제를 풀어봤다.

select 
CASE 
	when c.age between 10 and 19 then '10대'
	when c.age between 20 and 29 then '20대'
	when c.age between 30 and 39 then '30대'
	when c.age between 40 and 49 then '40대'
	when c.age between 50 and 59 then '50대'
END age_segment,
SUBSTR(fo.addr,1,2) '시도', fo.cuisine_type, COUNT(c.customer_id) '방문 고객'
from customers c 
join food_orders fo on fo.customer_id = c.customer_id 
where addr like '%경기%' and cuisine_type = 'American' and c.age between 10 and 59
group by 1, 2, 3

그리고 경기, american이 너무 많이 나와 거슬려서 피벗x쿼리를 한 번 더 다듬어봤다.

어처피 where절에 조건이 경기지역의 아메리칸 식당이니까 select절에 굳이 있을 필요가 없으니

customer_id만 세면 된다. -> Count(customer_id) 사용

select 
CASE 
	when c.age between 10 and 19 then '10대'
	when c.age between 20 and 29 then '20대'
	when c.age between 30 and 39 then '30대'
	when c.age between 40 and 49 then '40대'
	when c.age between 50 and 59 then '50대'
END age_segment,
COUNT(c.customer_id) as '경기 지역 아메리칸 식당 연령대별 방문객 수'
from customers c 
join food_orders fo on fo.customer_id = c.customer_id 
where addr like '%경기%' and cuisine_type = 'American' and c.age between 10 and 59
group by 1

이렇게 정리도 되는데 이제 피벗테이블을 사용한다고 하면

일단 여기서 가로로 나와야 하는 데이터가 뭔지 생각해본다

-> 연령대별 방문객 수, 그렇다면 연령대가 가로로 나열되고 그 안에 값이 들어가야한다.

시도	음식타입	10대	20대	30대	40대	50대
경기	American	8	25	14	11	7

그래서 일단 종이에 어떤 형식으로 데이터가 나와야 하는지 손으로 그려봤고

쿼리를 작성했다.

select SUBSTR(addr,1,2) '지역',cuisine_type '음식타입',
COUNT(if(c.age between 10 and 19, c.customer_id, null)) as '10대',
COUNT(if(c.age between 20 and 29, c.customer_id, null)) as '20대',
COUNT(if(c.age between 30 and 39, c.customer_id, null)) as '30대',
COUNT(if(c.age between 40 and 49, c.customer_id, null)) as '40대',
COUNT(if(c.age between 50 and 59, c.customer_id, null)) as '50대'
from customers c 
join food_orders fo on fo.customer_id = c.customer_id 
where addr like '%경기%' and cuisine_type = 'American' and c.age between 10 and 59
group by 1