[sql] data transpose (case when 절 활용)

들어가기 앞서 . .

- SQL을 활용해서 데이터를 집계할 때 row(열)로 구성된 데이터를 column(행)으로 구성하여 보고싶을 때가 종종있다.

- case when절을 활용해서 데이터를 집계할 때 column으로 늘어 뜨리는 부분에 대해 간단하게 정리하려 한다.

 

 

Case when 절

- case when절은 프로그래밍에서 if ~ else then 구문과 유사하다고 볼 수 있다. 즉 SQL의 If구문으로 select절에서 주로 사용된다.

CASE
WHEN '조건1' THEN '조건1이 만족할 때 가져올 값'
WHEN '조건2' THEN '조건2가 만족할 때 가져올 값'
...
ELSE 모든 조건이 만족하지 않았을 때 가져올 값
END 

 

Data 예제

create table student_grade(`student_id` int, `student_name` varchar(10), `subject` varchar(20), `score` decimal(3))ENGINE=InnoDB DEFAULT CHARSET=utf8;
insert into student_grade(`student_id`, `student_name`, `subject`, `score`) 
values 
(1, '김철수', '국어', 81), 
(1, '김철수', '영어', 77), 
(1, '김철수', '수학', 93), 
(2, '김영희', '국어', 74),
(2, '김영희', '영어', 82),
(2, '김영희', '수학', 61),
(3, '박민수', '국어', 95),
(3, '박민수', '영어', 81),
(3, '박민수', '수학', 76),
(4, '최민희', '국어', 79),
(4, '최민희', '영어', 81),
(4, '최민희', '수학', 92),
(5, '이영숙', '국어', 64),
(5, '이영숙', '영어', 71),
(5, '이영숙', '수학', 53),
(6, '홍사랑', '국어', 64),
(6, '홍사랑', '영어', 72),
(6, '홍사랑', '수학', 58),
(7, '박지성', '국어', 81),
(7, '박지성', '영어', 82),
(7, '박지성', '수학', 75),
(8, '손흥만', '국어', 59),
(8, '손흥만', '영어', 97),
(8, '손흥만', '수학', 69),
(9, '이강수', '국어', 100),
(9, '이강수', '영어', 72),
(9, '이강수', '수학', 61),
(10, '김광수', '국어', 82),
(10, '김광수', '영어', 81),
(10, '김광수', '수학', 78),
(11, '박석희', '국어', 93),
(11, '박석희', '영어', 88),
(11, '박석희', '수학', 86),
(12, '최수석', '국어', 91),
(12, '최수석', '영어', 100),
(12, '최수석', '수학', 100),
(13, '이상준', '국어', 62),
(13, '이상준', '영어', 67),
(13, '이상준', '수학', 71),
(14, '김상민', '국어', 53),
(14, '김상민', '영어', 62),
(14, '김상민', '수학', 67),
(15, '황희춘', '국어', 71),
(15, '황희춘', '영어', 84),
(15, '황희춘', '수학', 86);

위의 쿼리로 테이블을 만들면 아래와 같은 모습의 테이블을 확인할 수 있다.

 

15명의 학생에 대해 국어, 영어, 수학 과목의 점수 테이블

 

집계

- 만들고 싶은 테이블은 위의 'student_grade' 테이블을 활용해서 학생의 이름을 유일하게 나오도록 하며, 각 과목의 점수를 column으로 펼쳐서 보고 싶다. 이런 경우에 case when절을 활용하면 된다.

 

<쿼리 1>

select 
student_id, student_name,
	case when `subject` = '국어' then score end as subject_of_korean,
    case when `subject` = '영어' then score end as subject_of_english,
    case when `subject` = '수학' then score end as subject_of_math
from student_grade
order by student_id
;

 

위의 쿼리는 case when 절을 여러번 사용하여 subject_of_korean, subject_of_english, subject_of_math 컬럼을 생성하여 아래와 같은 표 형식이 나오도록 만드려고 했다.

이름	수학점수	영어점수	국어점수
손흥민
박지성
이강인

 

쿼리1 실행 결과

하지만 위와 같이 이름이 중복으로 나오는 것을 확인할 수 있다. 즉, student_grade의 row를 하나 하나 스캔하면서 case when절에 만족하는 부분에만 점수를 가져오고 나머지는 null을 가지고오게 된다.

 

<쿼리 2>

select 
student_id, student_name,
	sum(case when `subject` = '국어' then score end) as subject_of_korean,
    sum(case when `subject` = '영어' then score end) as subject_of_english,
    sum(case when `subject` = '수학' then score end) as subject_of_math
from student_grade
group by student_id, student_name
order by student_id
;

 

쿼리2 실행 결과

'쿼리2'를 실행했을 때 원하는 형태의 테이블이 생성됐다. '쿼리2'의 경우 '쿼리1'의 결과에 group by를 함께써서 이름에 대해서 국어, 영어, 수학 과목에 대해 column으로 집계를 했다.

 

 

마무리

- SQL활용 시에 case when절을 활용할 일이 많기 때문에 집계 및 데이터를 transpose하는 예시를 통해 활용법을 정리해 보았다.

- [Github 링크]