[MySQL] Pivot Table 만들기

Pivot(피벗)이란?

• 기본적으로 RDB(관계형 DB)는 행과 열로 이루어져 있는데, 이 위치를 바꾸는 것

Pivot을 사용하는 경우

• DB를 조회할 때 종종 행과 열을 바꿔서 조회해야 할 때 사용

예시

Pivot 사용 전

	select a.restaurant_name,
		substring(b.time, 1, 2) as hh,
		count(1) as cnt_order
	from food_orders as a 
    	inner join payments as b 
        on a.order_id=b.order_id
	where substring(b.time, 1, 2) between 15 and 20
	group by 1, 2

Pivot 사용 후

• Pivot Table을 만들 때는 Pivot의 Column이 되어 줄 키(Key)가 필요
• max(if()) 문을 통해서 키와 동일할 Column을 세팅

	select restaurant_name,
		max(if(hh='15', cnt_order, 0)) as "15",
		max(if(hh='16', cnt_order, 0)) as "16",
		max(if(hh='17', cnt_order, 0)) as "17",
		max(if(hh='18', cnt_order, 0)) as "18",
		max(if(hh='19', cnt_order, 0)) as "19",
		max(if(hh='20', cnt_order, 0)) as "20"
	from
	(
		select a.restaurant_name,
			substring(b.time, 1, 2) as hh,
			count(1) as cnt_order
		from food_orders as a 
        	inner join payments as b 
        	on a.order_id=b.order_id
		where substring(b.time, 1, 2) between 15 and 20
		group by 1, 2
	) a
	group by 1
	order by 7 desc

Pivot 사용 전(2)

	select b.gender,
		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 age,
		count(1)
	from food_orders as a 
    	inner join customers as b 
        on a.customer_id=b.customer_id
	where b.age between 10 and 59
	group by 1, 2

Pivot 사용 후(2)

	select age,
		max(if(gender='male', order_count, 0)) as male,
		max(if(gender='female', order_count, 0)) as female
	from
	(
		select b.gender,
			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 age,
			count(1) as order_count
		from food_orders as a 
        	inner join customers as b 
            on a.customer_id=b.customer_id
		where b.age between 10 and 59
		group by 1, 2
	) t
	group by 1
	order by age

---