[코딩테스트] 해커랭크 Hackerrank SQL occupations 문제풀이

SQL 코딩테스트 준비를 위해 해커랭크 문제풀이를 해보려고 합니다. 서버는 MySQL을 기준으로 설정하였으며, 문제의 핵심은 sql로 테이블을 pivot하는 방법, 순위함수 row_number() 함수를 이해하는 것입니다. 문제 분석을 통해 정답 SQL 쿼리와 오답을 포함한 자세한 풀이 과정을 작성했습니다. SQL 코딩 능력을 키우고자 하는 분들에게 도움이 될 수 있도록 문제 해결에 필요한 사고 과정과 코드 설명을 상세히 다루었습니다.

 

 

문제 : occupations (hackkerrank 해커랭크 SQL, MySQL)  

난이도 : Medium

서버 : MySQL
테이블 이름 : OCCUPATIONS

이 문제의 핵심  :  SQL로 pivot 하는 방법을 아는지,모르는지에 대한 문제 출제 의도인 것 같습니다. 

 

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

Occupations | HackerRank

 

 

문제  요약 : occupations(직업)별로 이름이 있는 데이터표를 pivot한 형태로 만들어주세요. 

 

input sample

output sample

 

 

 

정답 SQL 코드 : 

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) as rn
    FROM OCCUPATIONS
) AS subquery
GROUP BY rn;

 

 

풀이과정 (나의 풀이 사고과정 정리) : 

 

 

pivot하는 예제를 생각했을떄, 이런 예제에서 subject = 수학, 영어, 국어인 score를 만들려면 

<기존 테이블> 

student	subject	score
a	수학	80
a	영어	90
a	국어	100
b	수학	82
b	영어	93
b	국어	100

 

<pivot한 테이블> 

student	subject	score	수학	영어	국어
a	수학	80	80
a	영어	90		90	100
a	국어	100
b	수학	82	82
b	영어	93		93
b	국어	100			100

 

 

pivot하게 만들려면 조건문이 필요하고, 집계를 하면 되겠다고 생각했다. 

 

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 OCCUPATIONS

 

그런데, 여기서 max..집계를 해야 하는데 집계기준을 어떻게 만들지? 에 대한 고민이 있었고, 

고민끝에 서치를 통해 ROW_NUMBER() 함수를 이용하면 되는 힌트를 얻었다. 

---

SQL 순위함수 종류

1) ROW_NUMBER() OVER (ORDER BY val) AS row_number #중복되어도 고유한 번호부여

2) RANK() OVER (ORDER BY val) AS rank #중복값이 있으면 동일한 순위, 다음 순위는 그만큼 건너뜀

3) DENSE_RANK() OVER (ORDER BY val) AS dense_rank #중복값이 있으면 동일한 순위, 건너뛰지 않고 연속적으로 부여

 

 

 

ROW_NUMBER 함수를 통해, occupations 별로 name 알파벳 순서로 순위값을 부여해주면 된다. 

 

 

예시 테이블 (임의로 데이터를 만들어서 데이터가 다를 수 있습니다) 

row_number	name	occupation	doctor	actor	singer	professor
1	s	doctor	s
1	j	actor		j
1	d	singer			d
1	h	professor				h
2	q	doctor	q
2	w	actor		w
2	e	singer			e
2	r	professor				r

 

 

따라서, 다음과 같이 sql 코드를 실행하면 정답! 

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) as rn
    FROM OCCUPATIONS
) AS subquery
GROUP BY rn;

 

 

SQL로 pivot한 형태 테이블 만들기 완성~