[HackerRank] Occupations (Medium)

어려워서 결국 혼자 못 풀었다

피봇하는 건데 새로운걸 알게됐다

 

이렇게 생긴 테이블을 Doctor 레코드, Actor 레코드... 로 직업별로 열을 만들고 필드엔 사람이름을 넣으라는 문제다.

직업별로 사람수가 다른데 남는 행엔 NULL을 넣는다. 사람이름은 알파벳순 나열해야 한다.

 

문제풀이 전에 기본개념 정리를 해봤다.

 

📌 Step 0. SELF JOIN에 대해

-- 예시
SELECT t1.name, t2.name
FROM occupations t1 JOIN occupations t2
WHERE t1.name = 'Eve'

 

메인 테이블인 Occupations는 18rows이다.

 

위처럼 같은 테이블 둘을 조인하고 Eve만 불러오면 첫 레코드는 Eve, 두번째 레코드는 18명의 이름이 다 붙는다. (해커랭크는 결과값 창을 최대한 늘려도 18줄이 다 보이지 않아서 캡쳐가 잘렸다)

 

참고로 WHERE절 없이 불러오면 18*18의 324행이 출력된다.

 

이 문제의 최종답안과는 상관없지만 이번에 한번 정리하게 돼서 기록하고 싶었다.

 

 

 

 

 

 

 

📌 Step 0. 문자형에서 연산자에 대해

-- 예시
SELECT t1.name, t2.name
FROM occupations t1 JOIN occupations t2
WHERE t1.name = 'Jane' AND t1.name <t2.name

 

이번에 처음 알았는데 숫자형, 날짜형뿐 아니라 문자형에서도 연산자를 쓸 수 있었다! alphabetic order 기준인 거 같다.

 

위 쿼리를 통해 Jane보다 이름이 후순위인 사람들을 두번째열에서 불러보았다.

 

이제 문제를 풀어보겠다.

 

 

 

 

 

 

---

 

📌 Step 1. 상호연관 서브쿼리

SELECT name 
    , occupation
    , (SELECT COUNT(*)
        FROM occupations t1
        WHERE t1.occupation = t2.occupation AND t1.name < t2.name) AS rank_name
FROM occupations t2

 

correlated subquery는 처음 써본다.

중첩 서브쿼리(=nested subquery, 보통 WHERE절에서 연산자와 함께 씀) 중에서, 보통의 simple subquery는 서브쿼리의 값을 메인쿼리가 이용하는 방식인 반면에 correlated subquery는 서브쿼리가 메인쿼리를 참조한다.

 

상호연관 서브쿼리? 하위 서브쿼리? (공통적으로 약속된 번역이 있는진 모름)는 대개 성능이 좋지 않대서 SELF JOIN으로 바꿔보려 하긴 했는데 아직은 실패했다. 할 방법이 있긴 한건진 모르겠는데 나중에 돌아와서 또 해봐야지...

 

 

 

 

 

아무튼 이 쿼리의 결과로는 이름, 직업, 그 직업군 내에서 본인의 이름 순서(0부터 시작~)를 반환하고 있다. 

서브쿼리의 WHERE절에선 같은 직업을 가진 사람들 중에, 이름이 본인보다 후순위인 행의 수를 COUNT하고 있고

메인쿼리의SELECT절에선 본인의 이름과 COUNT결과를 부르고 있다. RANK함수를 쓰지 않고도 번호를 매길 수 있었다. 

 

 

📌 Step 2. NULL을 포함해 직업 골라내기

SELECT 
    (CASE WHEN occupation = 'Doctor' THEN name ELSE null END) AS Doctor
    , (CASE WHEN occupation = 'Professor' THEN name ELSE null END) AS Professor
    , (CASE WHEN occupation = 'Singer' THEN name ELSE null END) AS Singer
    , (CASE WHEN occupation = 'Actor' THEN name ELSE null END) AS Actor
FROM occupations

 

직업마다 속한 사람수가 다른데, 각자 길이(length)가 다른걸 동시에 불러올 수는 없다. (자동으로 남는칸은 NULL로 해주질 않는다)

 

CASE문으로 해당안되면 null을 넣어주면 이렇게 골라낼 수 있다.

 

이제 이걸 FROM에서 occupations가 아니라 위에서 만든 쿼리를 인라인뷰 서브쿼리로 써주면 끝난다.

 

 

 

 

 

 

 

📌 Step 3. 완성

SELECT 
    sub.rank_name
    , MIN(CASE WHEN occupation = 'Doctor' THEN name ELSE null END) AS Doctor
    , MIN(CASE WHEN occupation = 'Professor' THEN name ELSE null END) AS Professor
    , MIN(CASE WHEN occupation = 'Singer' THEN name ELSE null END) AS Singer
    , MIN(CASE WHEN occupation = 'Actor' THEN name ELSE null END) AS Actor
FROM (SELECT name
        , occupation
        , (SELECT COUNT(*) 
        FROM occupations t1
        WHERE t1.occupation = t2.occupation AND t1.name < t2.name) AS rank_name
    FROM occupations t2)
    AS sub
GROUP BY sub.rank_name
ORDER BY sub.rank_name

 

2에서 만든 쿼리에 FROM절을 1에서 만든 쿼리로 넣어준다. 

이름순대로 나열해야 하기에 GROUP BY와 ORDER BY를 해준다.

 

MIN()을 한 이유는 별거 없는데, 한번에 한값씩만 나와야 하기에 아무 집계함수나 쓴거다. MIN()을 하든 MAX()를 하든 똑같이 나온다. 같은 occupation이면서 같은 sub.rank_name인 경우는 한명밖에 없기 때문에 함수를 뭘 쓰든 같은 사람이 나온다.

 

피봇이 이렇게 어렵구나...! 쿼리의 depth가 3이나 된다.

SET과 @+1 을 사용하는 답안도 보긴 했는데 그렇게 하지 않고 푸는 MySQL쿼리는 이게 최선인 거 같았다.

특히 MySQL은 지원안되는 함수를 써서 다른 DB는 더 간단하게 생긴 쿼리를 쓰는 거 같긴 했다. PIVOT이나 FULL OUTER JOIN 같은...

 

추가) 상호연관 서브쿼리 대신 RANK함수 사용

SELECT 
    sub.rank_name
    , MIN(CASE WHEN occupation = 'Doctor' THEN name ELSE null END) AS Doctor
    , MIN(CASE WHEN occupation = 'Professor' THEN name ELSE null END) AS Professor
    , MIN(CASE WHEN occupation = 'Singer' THEN name ELSE null END) AS Singer
    , MIN(CASE WHEN occupation = 'Actor' THEN name ELSE null END) AS Actor
FROM (SELECT name
        , occupation
        , RANK() OVER (PARTITION BY occupation ORDER BY name) AS rank_name
    FROM occupations) AS sub
GROUP BY sub.rank_name
ORDER BY sub.rank_name

 

 

참고1) MS SQL Server의 깔끔한 답안

SELECT d.name, p.name, s.name, a.name
FROM 
    (SELECT name, ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) id 
     FROM occupations 
     WHERE occupation = 'Doctor') D
     
FULL OUTER JOIN
    
    (SELECT name, ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) id 
     FROM occupations 
     WHERE occupation = 'Professor') P ON D.id = P.id

FULL OUTER JOIN

    (SELECT name, ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) id 
     FROM occupations 
     WHERE occupation = 'Singer') S ON P.id = S.id

FULL OUTER JOIN

    (SELECT name, ROW_NUMBER() OVER (PARTITION BY occupation ORDER BY name) id 
     FROM occupations 
     WHERE occupation = 'Actor') A ON S.id = A.id;

 

참고2) MySQL에서  GROUP_CONCAT() 사용한 답안

SELECT 
    GROUP_CONCAT(IF(occupation='doctor',name,NULL)) AS 'doctor'
    , GROUP_CONCAT(IF(occupation='professor',name,NULL)) AS 'professor'
    , GROUP_CONCAT(IF(occupation='singer',name,NULL)) AS 'singer'
    , GROUP_CONCAT(IF(occupation='actor',name,NULL)) AS 'actor' 
FROM (SELECT *, row_number() OVER(PARTITION BY occupation ORDER BY name) AS num 
      FROM OCCUPATIONS ) AS sub
GROUP BY num

프로그래머스의 우유 요거트 문제에서 알게된 함수다

GROUP_CONCAT과 IF로 직업을 가려내고 row_number 윈도우로 순서를 가려냈다.