[HackerRank] Occupations 직업별 피벗 테이블 만들기

안녕하세요!

오늘은 해커랭크에 Occupations 문제를 정리해보려 합니다.

피봇테이블 형식을 만들 수 있는지에 대한 문제인데, 한번에 해결하진 못하고 검색을 하면서 해결했네요.

배운 점들도 많아서 과정을 한번 적어보겠습니다. 문제는 아래와 같습니다.

 

Q. Occupations 테이블에서 Occupation 컬럼을 4개의 컬럼(Doctor, Professor, Singer, Actor) 피봇형식으로 출력하시오. (단, 알파벳 순으로 정렬)

Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output should consist of four columns (Doctor, Professor, Singer, and Actor) in that specific order, with their respective names listed alphabetically under each column.

Note: Print NULL when there are no more names corresponding to an occupation.

Input Format

The OCCUPATIONS table is described as follows:

 

---

첫 시도

단순하게 이름들을 세로로 모은다고 생각해서 아래와 같이 쿼리를 만들었는데요.

한 행에 이름 하나, 나머지 칼럼들이 Null값들로 채워지는 결과값이 나왔습니다. 

# 틀린 답
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

 

---

Solution

결국 모든 데이터들을 첫행부터 차례대로 출력해야 하는데 이 부분이 어려웠습니다. 

Null값을 없애야 하나..? 정렬을 시켜야 하나..?  여러가지 생각해 봤는데 떠오르지 않더라고요.

 

결국 인터넷 검색을 해보았고 아래와 같이 쿼리를 만드는 구나 알게 되었습니다.

 

(1) 행 번호를 적은 임시테이블을 만들고,

(2) 행 번호를 group by 해서 차곡차곡 데이터가 출력될 수 있게 함.

 

위 과정들을 눈으로 볼 수 있게 포스팅 해보겠습니다.

 

(1) 행번호 CTE 만들기

# 행번호 CTE 만들기
WITH RANKED AS (
    SELECT
        NAME, OCCUPATION,
        ROW_NUMBER() OVER (PARTITION BY OCCUPATION ORDER BY NAME) AS ROW_NUM
    FROM OCCUPATIONS
    )
SELECT *
FROM RANKED

 

윈도우 함수를 사용해 각 행번호를 만들어주는 임시테이블 쿼리이고 결과는 아래와 같이 나옵니다. .

3번째 컬럼에 행 번호가 추가된 것을 볼 수 있습니다.

 

(2) group by를 사용해 pivot 하기

위 상태에서 피봇 쿼리를 사용해줍니다. 

Min() 함수를 써주는 이유는 문자열이기 때문입니다. group by를 사용해야 하는데 문자열끼리 연산을 하거나 합칠일이 없기에 단일값을 출력하기 위해 써주었습니다. 

# 정답 쿼리
WITH RANKED AS (
    SELECT
        NAME, OCCUPATION,
        ROW_NUMBER() OVER (PARTITION BY OCCUPATION ORDER BY NAME) AS ROW_NUM
    FROM OCCUPATIONS
    )

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 Ranked
GROUP BY ROW_NUM

 

 

이번에 단순 피봇이 아닌 윈도우 함수와 결합된 피봇 문제를 풀어봤는데요.

풀고 나니 해볼만 한거 같은데 처음 문제를 풀땐 어떻게 접근해야 할지 어려웠네요.

덕분에 윈도우 함수 활용법에 대해 더 이해할 수 있는 문제였던 것 같습니다.

 

도움이 되셨으면 좋겠습니다! 감사합니다.