[MySQL] HackerRank Pivot Table 문제 (CASE문, SET, 서브쿼리 활용)

문제 출처

https://www.hackerrank.com/challenges/occupations/problem?isFullScreen=true&h_r=next-challenge&h_v=zen 

Occupations | HackerRank

 

문제 설명

OCCUPATIONS 테이블

OCCUPATIONS 테이블은 Name, Occupation 두 column으로 이루어진 테이블이에요.

 

문제는 Occupation 칼럼에 있는 4가지 직업 (Doctor, Professor, Singer, Actor)을 새로운 열로 Pivot 테이블로 만드는 것이 핵심이고, 새로 만든 테이블에는 직업에 맞는 Name 값을 넣어주는 것입니다.

* 추가 조건 - 1. 알파벳 순으로 정렬 2. 다른 직업의 값이 남아 있으면 Null로 채우기

 

최종적으로 아래와 같은 테이블을 만들어야 해요.

Doctor	Professor	Singer	Actor
Jenny	Ashley	Meera	Jane
Samantha	Christeen	Priya	Julia
Null	Ketty	Null	Maria

 

문제 풀이

Q. "Occupation 열에 있는 값들을 새로운 열로 어떻게 만들어줄 것인가?"

A. CASE문을 활용해 볼까?

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;

CASE문을 활용하여 각 직업 별로 필터링하는 과정을 거친 뒤 4개의 칼럼을 생성해 주고,

ORDER BY를 통해 알파벳순으로 정렬하는 조건을 만족시켜 줬습니다.

 

그런데 쿼리의 결괏값을 보면 아래와 같이 출력이 됩니다. 우리가 원하는 결괏값과는 조금 다른 모양을 띄고 있죠? 🥹

결괏값

CASE문으로 생성된 NULL 값들이 없어야 우리가 원하는 결괏값을 얻을 수 있을 텐데 말이죠.

즉, NULL이 없이 하나의 row 값에 Aamina, Ashley, Christeen, Eve 이런 식으로 들어가야 합니다.

---

Q. NULL 값을 어떻게 없앨까...?

A. ChatGPT..?

 

접근 방식은 다음과 같아요. "SET을 활용하여 누적 값을 매긴 뒤, GROUP BY 하고 집계함수를 통해서 출력하면 됩니다."

제가 봐도 무슨 소리인지 모르겠네요. 그럼 천천히 설명해 볼게요 😅

 

! 우선 SET 함수를 통해서 순번을 매기는 방법에 대해서부터 알아봐야 해요.

아래는 SET을 통해서 순번을 매기는 예시입니다.

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;

* CASE WHEN ~ @변수 := @변수 + 1는 @변수가 계속 누적되는 것을 의미해요.

 

그리고 아래와 같이 Name, Occupation, RowNumber 칼럼에 대한 결괏값이 나오게 돼요.

결괏값

 

자 그러면, 아까 CASE문을 통해서 만들었던 쿼리에 순번을 매기는 쿼리를 추가해 볼게요.

-- 변수 설정
SET @D=0, @P=0, @S=0, A=0;

-- CASE문, 순번 쿼리 합치기
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;

 

아래는 쿼리에 대한 결괏값입니다. (Doctor, Professor, Singer, Actor, RowNumber 순) 

결괏값

 

그다음으로 해야 할 것은 위 결괏값을 서브쿼리로 활용하여 RowNumber으로 GROUP BY를 해주는 것이 Point입니다.

 

여기서 이해해야 할 것은 왜 RowNumber 열로 GROUP BY 하는 가입니다.

 

실제 결괏값을 보면 RowNumber은 1 ~ 7까지 있는데 1을 중심으로 설명을 해볼게요.

1을 그룹화시키면 Doctor, Professor, Singer, Actor 값이 다 포함됩니다.

 

즉, 1에는 Doctor (Aamina, Null, Null, Null), Professor (Null, Ashley, Null, Null), Singer (Null, Null, Christeen, Null), Actor (Null, Null, Null, Eve) 이렇게 포함이 되어 있겠죠?

 

여기서 집계함수 MIN을 사용하게 된다면, 1에 포함되어 있는 각 직업을 가진 이름이 출력되게 됩니다.

 

최종 쿼리

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 A
GROUP BY RowNumber;

 

아래는 쿼리에 대한 결괏값입니다.

결괏값