[HackerRank] Occupations (Pivot)

 

Advanced Select / Medium Level

 

문제

✨ 문제링크 : https://www.hackerrank.com/challenges/occupations/problem?isFullScreen=true

• 다음과 같은 테이블을 피봇 하여,  Doctor, Professor, Singer, and Actor 컬럼 순으로 해당 직업에 해당하는 사람 이름이 알파벳 순으로 정렬되게 만들어야 한다.

---

풀이

1. 테이블 확인

• 먼저 테이블은 위 예시와 동일하게 이름/직업 순으로 되어 있다.

SELECT *
FROM OCCUPATIONS

 

2. 직업별로 이름을 알파벳 순으로 정렬하고 번호 매기기

• 문제 조건 중 직업별로 피봇 하되, 알파벳 순으로 이름을 정렬하는 점이 있다.
• 그리고 피봇 결과는 1, 2, 3행 별로 위에서부터 이름이 채워지고, 이름이 더 이상 채워지지 않는 부분은 NULL 값을 반환해야 한다.

👉 ROW_NUMBER() 윈도우 함수를 사용하는데, 직업별로 Partition을 나누고, 이름을 알파벳 순으로 Order by 한다.

👉 ROW_NUMBER() 를 사용함으로써 피봇 후 행 번호를 생성하는 작업을 미리 해주는 것! (나중에 ROW_NUMBER 를 기준으로 GROUP BY 함)

 

다음과 같이 쿼리하면, 직업별로 해당되는 사람이름이 알파벳 순으로 나타나며 번호도 매겨진다

ORDER BY 해서 살펴보면 쉽게 확인할 수 있다.

SELECT *, 
       ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) as rn
FROM Occupations
ORDER BY occupation, rn

 

3. 직업별로 이름을 알파벳 순으로 정렬하고 번호 매기기

• 위의 쿼리를 FROM 절에 서브쿼리로 넣고, rn으로 GROUP BY 한다. → rn(행 번호)를 기준으로 각 직업에 해당하는 사람들의 이름을 MAX()함수로 반환하여 하나의 값이 한 행에 나타나게 한다.
• 이때 각 직업에 해당하는 사람 이름만 필터링 하기 위해 CASE WHEN 을 사용한다.
• 쿼리를 종합하면 다음과 같다.

SELECT MAX(CASE WHEN Occupation = 'Doctor' THEN Name END) AS Docter,
       MAX(CASE WHEN Occupation = 'Professor' THEN Name END) AS Professor,
       MAX(CASE WHEN Occupation = 'Singer' THEN Name END) AS Singer,
       MAX(CASE WHEN Occupation = 'Actor' THEN Name END) AS Actor
FROM(
    SELECT *, 
           ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) as rn
    FROM Occupations
    ORDER BY occupation, rn) A
GROUP BY rn