[Hackerrank] Occupations

문제

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

Occupations | HackerRank

아래 데이터를 피벗 형태로 반환하기

Name	Occupation
Aamina	Doctor
Ashley	Professor
Christeen	Singer
Eve	Actor

↓

 

결과

Doctor	Professor	Singer	Actor
Aamina	Ashley	Christeen	Eve
Julia	Bevelt	Jane	Jennifer
NULL	Maria	Kristeen	Samantha

 

해결 방법

1. 변수와 case 문을 사용하기

 

1-1) 

set @r1=0, @r2=0, @r3=0, @r4=0; 

select case when occupation = 'Doctor' then (@r1:=@r1+1) 
when occupation = 'Professor' then (@r2:=@r2+1) 
when occupation = 'Singer' then (@r3:=@r3+1)
when occupation = 'Actor' then (@r4:=@r4+1) end as rowNum, 
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, c
ase when occupation = 'Actor' then Name end as Actor 
from OCCUPATIONS 
order by Name;

 각 직업을 대상으로 RowNum을 변수를 이용해서 달아준다.

 

1-2)

set @r1=0, @r2=0, @r3=0, @r4=0; 
select min(Doctor), min(Professor), min(Singer), min(Actor) 
from ( select case when occupation = 'Doctor' then (@r1:=@r1+1) 
      when occupation = 'Professor' then (@r2:=@r2+1) 
      when occupation = 'Singer' then (@r3:=@r3+1) 
      when occupation = 'Actor' then (@r4:=@r4+1) end as rowNum, 
      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) as temp 
group by rowNum;

RowNum을 group by하면 결국 가장 사람이 많은 직업군의 개수로 테이블이 정리한다.

그러나 여기서 group by를 썼기 때문에, 집계 함수만을 select 할 수 있기에 min을 붙여 각 직업 칼럼들을 셀렉한다.

각 칼럼은 밸류값이 다 STRING이기에 MAX가 들어간다 해서 출력값이 변하지 않는다. 

 

 

 

2. Window문 ROW_NUM 사용하기

SELECT
    MAX(CASE WHEN OCCUPATION = 'Doctor' THEN NAME END) AS 'Doctor',
    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) RN
      FROM OCCUPATIONS) TEMP
GROUP BY RN

큰 그림은 비슷하나 여기선 WINDOW의 ROW_NUM 을 사용하여 편리하게 작성할 수 있다. 사실 MySQL에서 ROW_NUMBER()를 지원하지 않는데 여기선 돌아간다. (ROW_NUMBER는 ORACLE에서 지원)