[TIL] MySQL TIL Day 6

🟡 [HackerRank] Occupations

https://www.hackerrank.com/challenges/occupations/problem?isFullScreen=true

Solution

이 문제는 mySQL의 CASE WHEN을 이용하여 pivot table로 변경하는 문제이다. Pivot table은 간단히 말하면 테이블의 Row를 Column으로 변형한 테이블이다.

(1) Step 1: CASE WHEN 으로 Name과 Null 부여

첫번째 단계는 CASE WHEN을 이용해 Occupation이 특정 직종(Doctor 등등) 이라면 Name을, 그렇지 않으면 Null를 출력하도록 한다. 그리고 문제에서 제시한대로 Name을 기준으로 정렬한다.

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

위 코드를 실행하면 결과는 아래와 같다. 본 문제는 각 Occupation을 칼럼으로 두고 1행부터 해당하는 Name이 각각 차례대로 칼럼에 입력되어야 하기 때문에 아래와 같은 결과가 나오면 안된다.

Aamina NULL NULL NULL
NULL Ashley NULL NULL
NULL Belvet NULL NULL
NULL Britney NULL NULL
NULL NULL Christeen NULL
NULL NULL NULL Eve
NULL NULL Jane NULL
NULL NULL NULL Jennifer
NULL NULL Jenny NULL

(2) Step 2: Row number 부여

Step 1에서 제시한 문제를 해결하기 위해 각 row에 row number을 부여한다. 이 때 각 occupation에 대해 row number을 부여해야한다. 그래야 각 occupation의 첫번째 Name을 첫 번째 row에서 동시에 시작하게 둘 수 있다. TIL Day 5에서 다룬 SET을 이용한 파생변수 생성 방법을 이용하면 된다. 각 Occupation의 row number을 정의하는 파생변수를 생성한다.

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

결과는 아래와 같다. Professor 중에서 Ashley, Britney, Maria, Meera 순으로 row number가 오름차순으로 부여된 것을 확인할 수 있다.

Ashley Professor 1
Samantha Actor 1
Julia Doctor 1
Britney Professor 2
Maria Professor 3
Meera Professor 4
Priya Doctor 2

(3) Step 3: 합치기 및 Group by

이제 Step 1과 Step 2를 합쳐 FROM 절의 서브쿼리로 넣어준다. 그 다음 Group by를 이용해 row number 기준으로 grouping 하면 원하는 결과를 얻을 수 있다. 이 때 Group by는 무조건 집계함수와 함께 사용되어야 하는데, 본 문제에서는 집계할 건 없으므로 아무 의미 없는 MIN이나 MAX를 사용한다.

SET @D = 0, @P = 0, @S = 0, @A = 0;

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) AS t
GROUP BY rownumber