[HackerRank] Occupations

Question

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.

Input Format
The OCCUPATIONS table is descrived as follows:

Column	Type
Name	String
Occupation	String

Occupation will only contain one of the following values: Doctor, Professor, Singer or Actor.

Sample Input

Name	Occupation
Samantha	Doctor
Julia	Actor
Maria	Actor
Meera	Singer
Ashely	Professor
Ketty	Professor
Christeen	Professor
Jane	Actor
Jenny	Doctor
Priya	Singer

Sample Output

Explanation
The first column is an alphabetically ordered list of Doctor names.
The second column is an alphabetically ordered list of Professor names.
The third column is an alphabetically ordered list of Singer names.
The fourth column is an alphabetically ordered list of Actor names.
The empty cell data for columns with less than the maximum number of names per occupation (in this case, the Professor and Actor columns) are filled with NULL values.

느낀점

• 이번 문제는 테이블을 새롭게 만들어야 한다는것은 머리속으로 그려졌다, 하지만 SQL에서 어떻게 테이블을 만들어야할지 전혀 감이 오지 않았다...결국 구글링을 통해 CASE절을 이용하여 피벗테이블을 만드는 방법을 알게되었고, 방법을 봐도 바로 따라하지는 못하였다... 아직 연습이 많이 필요해보인다.
• 하지만, 답을 보고 따라해가며 이해를 하여 다음번에 틀리지 않기 위해 풀이를 적어보려고한다.

풀이

1단계

피벗테이블 형태로 각 직업별로 만들어줍니다.

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

먼저 각 열에 Doctor, Professor, Singer, Actor에 해당하는 Name을 불러올 수 있게 SELECT로 불러준다. 그리고 미리 ORDER BY로 정렬해 주었고 이렇게 하면 다음과 같은 모습이 나온다.

이렇게 했을때 문제는 NULL값으로 인해 각각 다른 행에 Name이 있는것을 알 수 있다.
우리는 한 행에 이름들이 배치되어 있기를 원한다.

따라서 각 직업별 Name의 갯수를 계산해서 갯수별로 Group by를 지으면 각 직어별 갯수는 다르지만 1행부터 차례로 나열이 될것이다.

2단계

갯수를 계산하기 위해 변수를 설정하고 계산한다.

SET @D = 0, @P = 0, @S = 0, @A = 0; --변수세팅--

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 -- 각 직업에 해당하면 갯수를 1개씩 올리기--
       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 --RowNumber로 저장
FROM Occupations
ORDER BY Name

여기까지 보면 맨 마지막줄에 각직업에 해당하는 갯수를 작성해주었으면 내려가면서 같은 직업이 나오면 +1을 해준다.

해당 갯수를 Group by 로 묶어준다면 만약 순서대로 3개2개3개4개 였을경우 인덱스 1,2,3,4가 생길것이고 이에 해당하는 값이 순서대로 출력이 될것이다.

Group by 를 어떻게 하느냐!!? 우선 해당데이터 전체에서 Groupby 를 해야하므로 지금까지 작성한 코드를 하나의 테이블로 만들어줍니다 -> FROM절로 귀속시킨다.

마지막으로 select절에 Max()를 붙여준 이유는 맨 아래 group by 절에서 RowNumber를 걸어주었기때문에 집계함수를 같이 사용해야해서 Max를 사용해 주었다.

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

SELECT Max(Doctor), Max(Professor), Max(Singer), Max(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) hackerrank
Group BY RowNumber

이렇게 정답을 확인할 수 있다!! 부트캠프에서 파이썬 알고리즘 섹션을 배울때 복잡한 문제로 보일때는 문제를 작게 짤라서 생각해보라고 했었는데 SQL에서도 이러한 방식으로 접근하면 좋을것같다!!