[HackerRank]Occupation - MySQL로 테이블 피벗하기

 

안녕하세요 둔:둔 입니다.

매주, 백준 문제 2개(파이썬) / Hacker Rank SQL 문제 1개씩 푸는 스터디를 진행하고 있는데요.

SQL 문제 중에 재밌는 문제가 있어 제 접근 방식과 풀이를 공유하고 싶어 글을 쓰게 되었습니다.

 

Occupations | HackerRank

문제

• 테이블 OCCUPATIONS 의 Occupation(직업) 기준으로 피벗 작업(Doctor, Professor, Singer, Actor)
  • 각 Occupation 별 Name(이름) 값 알파벳 순서로 정렬
  • Occupation 에 해당하는 Name의 수가 달라 없는 경우는 NULL 표기

(예시) Input 데이터가 아래의 표와 같다고 가정해 봅니다. 

Occupation	Name
Doctor	A
Doctor	B
Professor	C

 

문제에서 요구하는 Pivot 테이블은 다음과 같이 나오게 됩니다. Occupation 별 Name 수 최댓값은 총 2인데, Professor의 경우는 1명이므로 비는 셀은 NULL로 채워줘야 합니다.

Doctor	Professor
A	C
B	NULL

 

 

저는 창 함수(Window Function)를 이용하는 것과, 창 함수 없이 변수를 사용하는 방법 이렇게 2가지를 시도해 봤습니다. 

*참고로 저의 선택 환경은 MySQL 입니다!

*MySQL의 경우, 8.0 버전부터 창 함수를 지원하는 점 참고부탁드립니다.

 

 

문제 접근

GROUP BY는 컬럼 그룹 기준으로 집계 값 단 1개만 출력하므로 사용할 수 없습니다. 대신 단순하게 CASE 조건문으로 각 Occupation에 해당하는 Name을 출력하도록 시도해 봅니다.

 

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 O
;

 

그 결과는...

 

결과를 보면 무언가 잘못되었음을 확인할 수 있습니다.

 

위의 쿼리는 각 직업에 해당할 때 이름을 그 외는 모두 NULL을 출력하는 것인데 각 로우 당 NULL이 아닌 값이 들어가는 컬럼은 하나씩 밖에 나올 수 없습니다. 그렇다고 해당 쿼리를 인라인 뷰(inline view)로 넣어 다시 처리하기도 쉽지 않습니다. GROUP BY를 하고자 해도 공통으로 묶어줄 컬럼이 없기 때문입니다. 

 

그럼 여기서 각 Occupation(직업) 별로 Name(이름)을 알파벳 순서로 줄 세워 순서대로 번호를 매긴 ROW_NUM이라는 컬럼이 있다면 어떨까 생각해 봅니다.

Occupation	Name	ROW_NUM
Doctor	A	1
Doctor	B	2
Professor	C	1

 

예시로 표를 직접 만들어보니, 문제가 좀 간단해진 것 같습니다. 기존 데이터에 ROW_NUM이라는 컬럼을 추가로 만들어 GROUP BY로 묶어주는 방향을 잡고 문제를 풀어보면 될 것 같습니다.

 

 

(풀이 1) 윈도우 함수 ROW_NUMBER() 활용

여러 가지 윈도우 함수 중 ROW_NUMBER()를 활용한 방법입니다. ROW_NUMBER()는 말 그대로 창 안(파티션 기준)에 있는 데이터에 순차적인 번호를 부여하는 함수입니다.  일반적으로 사용하는 구문은 아래와 같습니다.

ROW_NUMBER() OVER (PARTITION BY col1 ORDER BY col2)

 

 

 

함수를 이용하여 쿼리 결과를 먼저 확인해 봅니다.

SELECT O.OCCUPATION
    ,O.NAME
    ,ROW_NUMBER() OVER (PARTITION BY O.OCCUPATION ORDER BY O.NAME ASC) AS ROW_NUM
FROM OCCUPATIONS O
;

 

 

위에서 생각한 형태의 데이터가 출력되는 걸 볼 수 있습니다. 이 데이터셋을 ROW_NUM 기준으로 GROUP BY만 해주면 됩니다.

SELECT  MIN(CASE WHEN OO.OCCUPATION = 'Doctor' THEN OO.NAME ELSE NULL END) AS Doctor
    ,MIN(CASE WHEN OO.OCCUPATION = 'Professor' THEN OO.NAME ELSE NULL END) AS Professor
    ,MIN(CASE WHEN OO.OCCUPATION = 'Singer' THEN OO.NAME ELSE NULL END) AS Singer
    ,MIN(CASE WHEN OO.OCCUPATION = 'Actor' THEN OO.NAME ELSE NULL END) AS Actor
FROM (
    SELECT O.OCCUPATION
        ,O.NAME
        ,ROW_NUMBER() OVER (PARTITION BY O.OCCUPATION ORDER BY O.NAME ASC) AS ROW_NUM
    FROM OCCUPATIONS O
) OO
GROUP BY ROW_NUM
-- ORDER BY ROW_NUM
;

 

 

Occupation(직업)을 각각 필터링하면 해당 데이터는 하나씩 밖에 없기 때문에 집계 함수는 MIN, MAX 둘 중 어느 걸 사용해도 상관없습니다. 

 

 

(풀이 2) 변수 활용

 

윈도우 함수 한 줄로 작성한 ROW_NUM 컬럼을 을 변수를 사용해서 강제적으로 만들어주면 동일 로직으로 풀 수 있습니다!

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

SELECT  MIN(CASE WHEN OO.OCCUPATION = 'Doctor' THEN OO.NAME ELSE NULL END) AS Doctor
    ,MIN(CASE WHEN OO.OCCUPATION = 'Professor' THEN OO.NAME ELSE NULL END) AS Professor
    ,MIN(CASE WHEN OO.OCCUPATION = 'Singer' THEN OO.NAME ELSE NULL END) AS Singer
    ,MIN(CASE WHEN OO.OCCUPATION = 'Actor' THEN OO.NAME ELSE NULL END) AS Actor
FROM (
    SELECT  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 ROW_NUM
            ,O.OCCUPATION
            ,O.NAME
    FROM OCCUPATIONS O
    ORDER BY O.NAME
) OO
GROUP BY OO.ROW_NUM
;

 

마무리

SQL 문제에 난이도 있는 문제를 공유하고 싶었습니다. 한창 취준 중에는 SQL 쿼리로 할 수 있는건가?! 했던 문제 테이블 피벗이었는데요. 이제는 해결할 수 있다는 개인적 성장의 발자취를 겸해서 남기고 싶어 글로 다시 한 번 정리해봤는데요. 막상 글로 문제 풀면서 생각한 과정들을 쪼개고 풀어서 쓰려니까 쉽지가 않네요..!