[SQL][HackerRank] Occupations

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

Occupations | HackerRank

 

Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output column headers should be Doctor, Professor, Singer, and Actor, respectively.
Note: Print NULL when there are no more names corresponding to an occupation.

문제에서는 Name과 Occupation이 필드로 담긴 OCCUPATIONS 테이블이 주어진다. 형태는 아래와 같다.

 

이를 이용해서 테이블의 형태를 변환(Pivot)시키는것이 문제의 목적이다. 원하는 형태는, 각 직업(Occupation)이 열로 변환된 형태이다. 즉, Doctor, Proffesor, Singer, Actor이 각각 열로 구성되길 원한다. 그렇게 열을 구성했으면, 각 행은 사람들의 이름이 된다. 이때, 사람들의 이름은 알파벳순서대로 행을 채워나가야 한다. 행을 채워나가다가, 대응되는 이름이 없으면 NULL값으로 행을 채우면 된다. 말이 장황하니, 예시를 들어보자.

 

OCCUPATIONS 테이블이 아래와 같은 형태라면,

Name	Occupation
Samantha	Doctor
Julia	Actor
Maria	Actor
Meera	Singer
Ashely	Professor

 

아래와 같은 테이블로 변환되길 원하는 것이다. 각 4개의 직업이 테이블의 열을 이루고 있고, 각 행은 알파벳 순서대로 이름을 채운다. Actor의 직업을 가진 사람은 2명인 반면, 나머지는 직업에는 모두 1명이 속한다. 따라서, Actor을 제외한 나머지 직업은 두번째 행에 대응되는 Name이 없으므로, NULL값으로 행을 채운다.

Doctor	Professor	Singer	Actor
Samantha	Ashely	Meera	Juila
NULL	NULL	NULL	Maria

 

---

생각보다 까다로운 문제이며, 문제를 풀기 위해서는 ROW_NUMBER, CASE WHEN, GROUP BY에 대해 정확하게 알고 있어야 한다. 위 세가지 개념을 차례대로 살펴보자.

 

 

1) ROW_NUMBER()

문제에서 각 직업에 속하는 사람들을 알파벳 순서대로 행에 기입하길 원했다. 때문에 우리는 각 사람들이 몇번째 행에 속할 것인지 분류해주는 작업이 필요하다. 그 작업은 row_number를 이용하면 가능하다. 아래와 같이 쿼리를 작성하면, 각 직업 내에서, 알파벳 기준 Name 순위를 메겨준다.

SELECT *,
    ROW_NUMBER() OVER(PARTITION BY occupation ORDER BY name) as num
FROM occupations;

 

출력결과는 아래와 같다. 각 객체마다 몇번째 행에 속해야할지 태그가 붙은 형태이다.

---

2) CASE WHEN 

이제 4개의 열(Occupations)를 생성하고자 한다. 각 직업(Doctor, Proffesor, Singer, Actor)을 열로 생성하고, 열에 해당하는 사람들을 값으로 집어넣어야하기 때문이다. 이같은 경우는 CASE WHEN 구문을 4번 작성해서, 각 해당하는 직업인 경우 이름을 출력하는 방식을 채택하면된다.

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;

출력결과는 아래와 같다. 열은 차례대로, Doctor, Proffesor, Singer, Actor 순이다. 4개의 열을 만드는것은 완료했지만, 각 행 사이사이에 NULL값이 존재한다는 문제가 있다. 

---

3) GROUP BY & AGGREGATE

위 결과에서 NULL값을 정리해주기위해서는, 1) 에서 생성한 테이블을 이용해 그룹화를 진행해야한다. 

 

FROM 절에서는 1)에서 생성한, 각 객체가 포함될 행이 표기되어있는 필드(num)을 가진 테이블을 이용한다. 그리고 num 필드를 이용해서 그룹화를 진행한다. 그러면 1번째 행에 올 Doctor, Proffesor, Singer, Actor, 2번째 행에 올 Doctor, Proffesor, Singer, Actor, ... , n번째 행에 올 Doctor, Proffesor, Singer, Actor가 각각 그룹을 이룬다.

 

이제 각 그룹마다 직업이 의사인사람, 교수인사람, 가수인사람, 그리고 배우인 사람을 출력해주면 문제가 마무리된다.

SELECT
    MIN(CASE WHEN occupation = 'Doctor' THEN name END) as Doctor,
    MIN(CASE WHEN occupation = 'Professor' THEN name END) as Professor,
    MIN(CASE WHEN occupation = 'Singer' THEN name END) as Singer,
    MIN(CASE WHEN occupation = 'Actor' THEN name END) as Actor   
FROM(
    SELECT *,
        ROW_NUMBER() OVER(PARTITION BY occupation ORDER BY name) as num
    FROM occupations
) tmp
GROUP BY num;