HackerRank(해커랭크) MySQL Occupations 문제 답

 

 

Occupations

 

 

*MySQL 버전, advanced select 문제입니다.

 

 

 

해커랭크 Occupations 문제, 샘플, 설명

 

• 직업별(D, P, S, A) 컬럼 분류
• 직업별 컬럼에 해당되는 사람 이름 알파벳 순 정렬
• 컬럼에 더 이상 사람 이름이 없다면 NULL 처리

 

Occupations 문제

 

 

대충 첫 번째 컬럼은 의사들, 두 번째 컬럼은 교수들, 세 번째 컬럼은 가수들, 네 번째 컬럼은 배우들 이름이어야 하고 빈 셀은 NULL 처리하라는 말.

 

Occupations 문제 샘플, 설명

 

 

 

해커랭크 Occupations 답

 

• WITH

가독성을 위해 사용

• CASE WHEN

직업별로 이름을 가져오고자 사용(WHERE occupation = 'doctor'처럼 쓴 것임)

• ROW_NUMBER, PARTITION BY

NULL처리 때문에 엄청 까다롭다고 생각한 문제다.

그저 단순하게 CASE WHEN이나 다중 WITH를 사용했는데, NULL과 이름 정렬이 해결되지 않았다.

이처럼 ORDER BY로만 처리 안될 때, RANK함수를 이용해볼 것을 가르침 받았다.

• MAX

집계함수를 사용한 이유는 GROUP BY 때문이다.

 

WITH aaa AS(
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
, ROW_NUMBER() OVER(PARTITION BY occupation ORDER BY name) AS rk
FROM OCCUPATIONS
)

SELECT max(doctor) , max(professor) , max(singer) , max(actor)
FROM aaa
GROUP BY rk;

 

---

 

이는 쉽게 말해, 순위를 그룹화하는 방식이다.

답을 하나하나 뜯어보자. WITH 가상 테이블 aaa부터 살펴보자.

# 테이블 확인을 위해 * 사용

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
, ROW_NUMBER() OVER(PARTITION BY occupation ORDER BY name) AS rk
FROM OCCUPATIONS;

 

ROW_NUMBER() OVER(PARTITION BY occupation ORDER BY name)

>> 직업별 그룹화 후, 각 그룹 내 이름을 정렬하고 순위를 매겼다.

이때 CASE WHEN에 NULL이 난무하는 이유는 직업별로 그룹화되어서 해당 직업이 아니면 NULL인 것.

Occupations NULL

 

 

*보기에 헷갈릴 수 있으니, 다시 표로 일부 정리

aaa
name	occupation	doctor	professor	actor	rk
Eve	Actor	NULL	NULL	Eve	1
Jennifer	Actor	NULL	NULL	Jennifer	2
Ketty	Actor	NULL	NULL	Ketty	3
Samantha	Actor	NULL	NULL	Samantha	4
Aamina	Doctor	Aamina	NULL	NULL	1
...

 

 

이제 NULL과 이름 정렬을 위해, 가상 테이블 aaa의 rk컬럼을 그룹화한다.

이때 GROUP BY하려는 컬럼은 SELECT에 있어야 한다. 혹은 SELECT에 집계함수가 쓰여야 한다.

하지만 컬럼 rk는 SELECT에 없으므로, SELECT 할 컬럼에 모두 집계함수를 사용한다.

*이때 집계함수는 max말고 min을 써도 결과에 영향 없다. 

SELECT max(doctor) , max(professor) , max(singer) , max(actor)
FROM aaa
GROUP BY rk;

 

직업별 이름마다 순위를 매겼었기에, 이를 다시 그룹화하면 1, 2, 3위... 에 해당되는 이름들이 정렬된다.

*즉, 1행의 Aamina, Ashely, Christeen, Eve는 각 직업군에서 1위인 사람들이다.

 

professor는 사람이 많아, 5위를 넘어서지만 나머지 직업군은 그에 못미친다.

그렇다고 professor의 순위를 안 보여 줄 순 없는 노릇. 따라서 나머지 직업군은 NULL 처리가 된 것이다.  

*즉, 컬럼별로 데이터 수가 안 맞으면 NULL 처리됨.  

max(doctor)	max(professor)	max(singer)	max(actor)
Aamina	Ashley	Christeen	Eve
Julia	Belvet	Jane	Jennifer
Priya	Britney	Jenny	Ketty
NULL	Maria	Kristenn	Samantha
NULL	Meera	NULL	NULL
...

 

 

 

추가 정리

 

인라인뷰 버전

SELECT MAX(CASE WHEN aaa.occupation  = 'doctor' THEN aaa.name END) AS doctor
, MAX(CASE WHEN aaa.occupation = 'professor' THEN aaa.name END) AS professor
, MAX(CASE WHEN aaa.occupation = 'singer' THEN aaa.name END) AS singer
, MAX(CASE WHEN aaa.occupation = 'actor' THEN aaa.name END) AS actor
FROM (
SELECT *
, ROW_NUMBER() OVER(PARTITION BY occupation ORDER BY name) AS nm_rk
FROM OCCUPATIONS
) aaa
GROUP BY aaa.nm_rk;