[SQL] CASE WHEN으로 Pivot Table 만들기(HackerRank - Occupations 문제)

🔊 본 포스팅에서 사용되는 테이블의 자료와 출처는 HackerRank 임을 밝힙니다. 더 다양한 SQL 문제를 풀어보시려면HackerRank 사이트를 방문해 보세요!

 

HackerRank 사이트

 

이번 포스팅에서는 MySQL의 CASE WHEN 구문으로 Pivot Table을 만들어 문제를 해결하는 방법에 대해 소개하려 한다. 활용하려는 문제는 HackerRank 사이트의 Occupations 문제이다. 원본은 여기를 참조하자.

 

우선 Pivot table이란 무엇일까? 직관적으로 말하면 Row에 있는 값들을 Column으로 변형한 테이블을 말하는데, Pandas의 pivot(), pivot_table() 메소드를 활용해본 사람들에겐 익숙할 것이다. Pivot table의 형태는 다음과 같다.

 

출처 : Pandas documentation

 

그렇다면 이것을 CASE WHEN 구문으로 어떻게 만들어줄 수 있을까? 문제를 보면서 같이 살펴보자. 문제의 요구사항은 다음과 같다.

 

PivottheOccupationcolumn inOCCUPATIONSso that eachNameis sorted alphabetically and displayed underneath its correspondingOccupation. The output column headers should beDoctor,Professor,Singer, andActor, respectively. Note:PrintNULLwhen there are no more names corresponding to an occupation.
(각각의 이름을 알파벳순으로 정렬하고 그에 상응하는 직업(Occupation)을 보여주는데, 직업값을 기준으로 Pivot 테이블로 변환해라. 이 때 변환한 Pivot 테이블의 칼럼명은 Doctor, Professor, Singer, Actor 순이다. 참고로, 각 직업에 대응하는 이름이 없다면 NULL을 반환해라.)

위 글의 설명만 보면 이해가 되지 않는다. 그러므로 Sample Input/Output을 살펴보자.

 

Occupations 문제의 샘플 I/O

 

Input 테이블을 살펴보았을 때, Occupation 종류에 따라 각각에 속하는 Name들을 정리해보면 다음과 같다.(Name은 알파벳 순으로 정렬했다.)

 

• Doctor : Jenny, Samantha
• Professor : Ashely, Christeen, Ketty
• Singer : Meera, Priya
• Actor : Jane, Julia, Maria

이 때, 각 직업에 속하는 첫 번째 이름들끼리 한 행에 출력하는 것이다. 그런데 만약 그 index에 해당하는 직업의 이름이 없다면 Null을 출력하라는 것이다. 그래서 Sample Output을 Pivot 형태로 정리하면 다음과 같다.

 

Doctor	Professor	Singer	Actor
Jenny	Ashely	Meera	Jane
Samantha	Christeen	Priya	Julia
NULL	Ketty	NULL	Maria

 

만약 이해가 되지 않는다면 여기 Discussion에서 닉네임 raiyanger24의 Step별 풀이방법과 이 이미지를 참조해보자.

SQL 풀이는 다음과 같다.

 

-- 각 직업별 Index를 세기 위한 변수 설정
SET @D=0, @P=0, @S=0, @A=0;

-- 문자열의 알파벳순서에서 최솟값(MIN)은 A(a)로 시작하는 것을 추출해줌!
SELECT MIN(Doctor), MIN(Professor), MIN(Singer), MIN(Actor)
FROM (SELECT CASE WHEN Occupation = 'Doctor' THEN Name END AS Doctor,
             CASE WHEN Occupation = 'Professor' THEN Name END AS Professor,
             CASE WHEN Occupation = 'Singer' THEN Name END AS Singer,
             CASE WHEN Occupation = 'Actor' THEN Name END AS Actor,
             CASE
             WHEN Occupation = 'Doctor' THEN (@D:=@D+1)
             WHEN Occupation = 'Professor' THEN (@P:=@P+1)
             WHEN Occupation = 'Singer' THEN (@S:=@S+1)
             WHEN Occupation = 'Actor' THEN (@A:=@A+1)
             END AS RowNumber
       FROM Occupations
       ORDER BY Name) sub
GROUP BY RowNumber

 

위 풀이의 핵심 포인트는 2가지이다.

 

1. (Name을 알파벳순으로 정렬했을 때)각 직업별로 첫 번째로 오는 Name들, 두 번째로 오는 Name들, 세 번째로 오는 Name들...(생략)을 알아내기 위해 SET 을 이용해 각 직업별의 Row를 세어준다.
2. CASE WHEN을 사용해 Row의 값을 Column으로 Pivot table을 만들어주기

우선 SQL 구문 하나하나씩 뜯어보고 결과를 살펴보자. 하단의 SQL 구문을 수행하면 다음과 같은 결과 포맷이 나온다.

 

SELECT CASE WHEN Occupation = 'Doctor' THEN Name END AS Doctor,
       CASE WHEN Occupation = 'Professor' THEN Name END AS Professor,
       CASE WHEN Occupation = 'Singer' THEN Name END AS Singer,
       CASE WHEN Occupation = 'Actor' THEN Name END AS Actor
FROM Occupations
ORDER BY Name

 

결과 화면

 

위 결과화면을 보면 직업별로 Pivot 테이블 형태는 만들어졌지만 각 직업별로 동일한 index 순서로 오는 Name들을 하나의 행에 위치시키지 못하고 있다. 예를 들어, 직업이 Doctor이면서 알파벳순으로 가장 첫 번째오는 Name은 Aamina 이고 Professor은 Ashley, Singer은 Christeen, Actor은 Eve이다. 따라서 우리가 원하는 형태는 한 Row에 이 이름들이 모두 들어 있는 다음과 같은 형태이다.

 

Doctor	Professor	Singer	Actor
Aamina	Ashley	Christeen	Eve

 

그렇기 때문에 하나의 Row에 직업별 동일한 index인 Name들끼리 모으기 위해 SET을 이용해 각 직업별 행 index를 세면서 설정해주자. 다음 SQL 구문의 결과화면은 다음과 같다.

 

-- 각 직업별 Index를 세기 위한 변수 설정
SET @D=0, @P=0, @S=0, @A=0;

SELECT Name,
       Occupation,
       CASE 
       WHEN Occupation = 'Doctor' THEN (@D:=@D+1)
       WHEN Occupation = 'Professor' THEN (@P:=@P+1)
       WHEN Occupation = 'Singer' THEN (@S:=@S+1)
       WHEN Occupation = 'Actor' THEN (@A:=@A+1)
       END AS RowNumber
FROM Occupations

 

결과화면

 

위 결과화면을 살펴보면 직업별로 Row index가 매겨져 있음을 볼 수 있다. 이제 위 2개의 SQL 구문을 합쳐 출력한 결과는 다음과 같다.

 

-- 각 직업별 Index를 세기 위한 변수 설정
SET @D=0, @P=0, @S=0, @A=0;

-- 문자열의 알파벳순서에서 최솟값(MIN)은 A(a)로 시작하는 것을 추출해줌!
SELECT CASE WHEN Occupation = 'Doctor' THEN Name END AS Doctor,
       CASE WHEN Occupation = 'Professor' THEN Name END AS Professor,
       CASE WHEN Occupation = 'Singer' THEN Name END AS Singer,
       CASE WHEN Occupation = 'Actor' THEN Name END AS Actor,
       CASE
       WHEN Occupation = 'Doctor' THEN (@D:=@D+1)
       WHEN Occupation = 'Professor' THEN (@P:=@P+1)
       WHEN Occupation = 'Singer' THEN (@S:=@S+1)
       WHEN Occupation = 'Actor' THEN (@A:=@A+1)
       END AS RowNumber
FROM Occupations
ORDER BY Name

 

결과화면

 

위 결과 상태에서 이제 RowNumber라는 새로운 파생 칼럼을 기준으로 그룹핑한 후 각 직업별 Name들의 최솟값을 출력하게 된다면 우리가 원하는 형태로 출력된다.

 

-- 각 직업별 Index를 세기 위한 변수 설정
SET @D=0, @P=0, @S=0, @A=0;

-- 문자열의 알파벳순서에서 최솟값(MIN)은 A(a)로 시작하는 것을 추출해줌!
SELECT MIN(Doctor), MIN(Professor), MIN(Singer), MIN(Actor)
FROM (SELECT CASE WHEN Occupation = 'Doctor' THEN Name END AS Doctor,
             CASE WHEN Occupation = 'Professor' THEN Name END AS Professor,
             CASE WHEN Occupation = 'Singer' THEN Name END AS Singer,
             CASE WHEN Occupation = 'Actor' THEN Name END AS Actor,
             CASE
             WHEN Occupation = 'Doctor' THEN (@D:=@D+1)
             WHEN Occupation = 'Professor' THEN (@P:=@P+1)
             WHEN Occupation = 'Singer' THEN (@S:=@S+1)
             WHEN Occupation = 'Actor' THEN (@A:=@A+1)
             END AS RowNumber
       FROM Occupations
       ORDER BY Name) sub
GROUP BY RowNumber

 

결과화면