28. (데이터베이스) 피벗 연습 문제

아래의 날짜 표를 달력 형태로 출력하시오.

풀이

select week,
sum(case when day = '일' then num_day else 0 end) 일,
sum(case when day = '월' then num_day else 0 end) 월,
sum(case when day = '화' then num_day else 0 end) 화,
sum(case when day = '수' then num_day else 0 end) 수,
sum(case when day = '목' then num_day else 0 end) 목,
sum(case when day = '금' then num_day else 0 end) 금,
sum(case when day = '토' then num_day else 0 end) 토
from cal
group by week;

정답

select *
from cal;

1단계

• where로 1번째 week만 잡아서 출력해보기

select *
from cal
where week = 1;

2단계

• 첫번째 주만 일, 월, 화, 수, 목, 금, 토 를 칼럼으로 올려보기

select 
case when day='일' then num_day else 0 end as '일',
case when day='월' then num_day else 0 end as '월',
case when day='화' then num_day else 0 end as '화',
case when day='수' then num_day else 0 end as '수',
case when day='목' then num_day else 0 end as '목',
case when day='금' then num_day else 0 end as '금',
case when day='토' then num_day else 0 end as '토'
from cal
where week = 1;

3단계

• sum으로 집계함수 써보기

select 
sum(case when day='일' then num_day else 0 end) as '일',
sum(case when day='월' then num_day else 0 end) as '월',
sum(case when day='화' then num_day else 0 end) as '화',
sum(case when day='수' then num_day else 0 end) as '수',
sum(case when day='목' then num_day else 0 end) as '목',
sum(case when day='금' then num_day else 0 end) as '금',
sum(case when day='토' then num_day else 0 end) as '토'
from cal
where week = 1;

4단계

• where절 삭제하고 group by 써보기

select 
sum(case when day='일' then num_day else 0 end) as '일',
sum(case when day='월' then num_day else 0 end) as '월',
sum(case when day='화' then num_day else 0 end) as '화',
sum(case when day='수' then num_day else 0 end) as '수',
sum(case when day='목' then num_day else 0 end) as '목',
sum(case when day='금' then num_day else 0 end) as '금',
sum(case when day='토' then num_day else 0 end) as '토'
from cal
group by week;

5단계

• week 칼럼 추가하기

select week,
sum(case when day='일' then num_day else 0 end) as '일',
sum(case when day='월' then num_day else 0 end) as '월',
sum(case when day='화' then num_day else 0 end) as '화',
sum(case when day='수' then num_day else 0 end) as '수',
sum(case when day='목' then num_day else 0 end) as '목',
sum(case when day='금' then num_day else 0 end) as '금',
sum(case when day='토' then num_day else 0 end) as '토'
from cal
group by week;

6단계

• 0을 null값으로 대체하기 (그룹함수는 null값이 있어도 연산이 된다)

select week,
sum(case when day='일' then num_day else null end) as '일',
sum(case when day='월' then num_day else null end) as '월',
sum(case when day='화' then num_day else null end) as '화',
sum(case when day='수' then num_day else null end) as '수',
sum(case when day='목' then num_day else null end) as '목',
sum(case when day='금' then num_day else null end) as '금',
sum(case when day='토' then num_day else null end) as '토'
from cal
group by week;