MYSQL/HakerRank_Easy

Occupations 과 Pivot 테이블

수스리 2025. 4. 3. 10:59

문제

Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output should consist of four columns (Doctor, Professor, Singer, and Actor) in that specific order, with their respective names listed alphabetically under each column.

OCCUPATIONS 테이블에서 Occupation(직업) 컬럼을 피벗(Pivot) 하여, 각 이름(Name)을 해당 직업 아래에 정렬하여 표시하시오.출력은 네 개의 컬럼(Doctor, Professor, Singer, Actor) 으로 구성되며,
각 직업 아래에는 해당 직업을 가진 사람들의 이름이 알파벳순(A~Z)으로 정렬되어 표시되어야 합니다

 

WITH TEMP AS(
    SELECT Name, Occupation,
    ROW_NUMBER() OVER(PARTITION BY Occupation ORDER BY Name) AS ROW_NUM
    FROM OCCUPATIONS
)
SELECT
    MAX(CASE WHEN Occupation = "Doctor" THEN Name ELSE NULL END) AS Doctor,
    MAX(CASE WHEN Occupation = "Professor" THEN Name ELSE NULL END) AS Doctor,
    MAX(CASE WHEN Occupation = "Singer" THEN Name ELSE NULL END) AS Doctor,
    MAX(CASE WHEN Occupation = "Actor" THEN Name ELSE NULL END) AS Doctor
FROM
    TEMP
GROUP BY ROW_NUM
ORDER BY ROW_NUM

 

Occupation 테이블 예시

 

결론부터 말하면 어려운 문제였다. 매우. 우선 내가 PIVOT을 몰랐고 당연히 만드는 방법도 몰랐다.

Pivot(이하 '피벗')은 데이터의 행과 열을 바꾸는 작업을  말한다. 피벗은 가독성이 좋아지고 데이터분석이 쉬워진다. 

예시

ID Month Product Sales
1 Jan A 100
2 Jan B 200
3 Feb A 150
4 Feb B 250

이런 데이터를

Month A B
Jan 100 200
Feb 150 250

이렇게 월별 데이터 매출로 바꿨다. 

이런 식으로 변화된 테이블을 피벗이라고 한다. 이제 문제로 가서 자세하게 알아보자

 

WITH TEMP AS(
    SELECT Name, Occupation,
    ROW_NUMBER() OVER(PARTITION BY Occupation ORDER BY Name) AS ROW_NUM
    FROM OCCUPATIONS
)

ROW_NUMBER를 이용한 가상 테이블을 만든다. 아래는 결과. 이 가상 테이블을 MAX와 CASE_WHEN을 이용해서  PIVOT을 만든다. 그리고 RowNum을 기준으로 그룹화 한다.

Name Occupation RowNum
Jenny Doctor 1
Samantha Doctor 2
Ashley Professor 1
Christeen Professor 2
Ketty Professor 3
Jane Singer 1
Meera Singer 2
Julia Actor 1
Maria Actor 2

 

SELECT
    MAX(CASE WHEN Occupation = "Doctor" THEN Name ELSE NULL END) AS Doctor,
    MAX(CASE WHEN Occupation = "Professor" THEN Name ELSE NULL END) AS Professor,
    MAX(CASE WHEN Occupation = "Singer" THEN Name ELSE NULL END) AS Singer,
    MAX(CASE WHEN Occupation = "Actor" THEN Name ELSE NULL END) AS Actor

그다름 CASE WHEN을 이용해서 각 직업별로 해당하는 이름을 새로운 컬럼에 넣는다. 참고로 여기서 Name은 테이블에 있는 Name을 말한다. MAX를 사용하는 이유는 MAX()를 사용하면 NULL이 아닌 값들 중 **가장 큰 값(알파벳 순)**이 선택된다. 만약 MAX 없이 CASE만사용한다면?

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
FROM OCCUPATIONS;
Doctor   | Professor | Singer   | Actor
---------|----------|----------|-------
Alice    | NULL     | NULL     | NULL  
NULL     | NULL     | Charlie  | NULL  
NULL     | David    | NULL     | NULL  
Eve      | NULL     | NULL     | NULL  
NULL     | NULL     | NULL     | Bob   
NULL     | NULL     | NULL     | Frank

이런 결과가 나온다.  반면 MAX를 쓰면 내가 원하는 행만 가져와서 원하는 결과가 나온다. CASE WHEN 자체가 새로운 컬럼을 추가하는 함수로 볼 수 있다. 내가 원하는 컬럼으로 반드시 채우려면 어떤자리는 NULL이 필연적으로 올 수 밖에 없다. 그래서 MAX를 쓴다. MAX를 쓰면 내가 원하는 데이터 하나만 뽑아오니. 
어렵지만 새로운 걸 발견해서 좋았다.

<2025년 4월 28일 리뷰>

복습차원에서 이 문제를 다시 풀어 봤다.

SELECT 
        MIN(CASE WHEN Occupation = "Doctor" THEN Name ELSE NULL END),
        MIN(CASE WHEN Occupation = "Professor" THEN Name ELSE NULL END),
        MIN(CASE WHEN Occupation = "Singer" THEN Name ELSE NULL END),
        MIN(CASE WHEN Occupation = "Actor" THEN Name ELSE NULL END)
FROM (
    SELECT Name, Occupation,
        ROW_NUMBER() OVER(PARTITION BY Occupation ORDER BY Name) AS ROW_NUM
    FROM Occupations
) AS TEMP
GROUP BY ROW_NUM

이번에는 서브쿼리로 이 문제를 풀었다.
이전까지 이 문제를 이해 잘 한 줄 알았는데 아니었다. 한번더 설명한다.

우선 서브쿼리부터 보자. 서브쿼리로 이렇게 직업별로 RowNum을 매겼다. 

Name Occupation RowNum
Jenny Doctor 1
Samantha Doctor 2
Ashley Professor 1
Christeen Professor 2
Ketty Professor 3
Jane Singer 1
Meera Singer 2
Julia Actor 1
Maria Actor 2

그다음 주목할 부분은 GROUP BY

ROW_NUM으로 GROUP BY 해준다.

  • ROW_NUM 1 : Jenny, Ashley, Jane, Julia
  • ROW_NUM 2 : Samantha, Christen, Meera, Maria
  • ROW_NUM 3 : NULL, NULL, Ketty, NULL

이렇게 ROW_NUM으로 묶는다. 여기서 CASE WHEN을 써서 직업별로 하나만 가져온다. 나는 집계함수를 왜 쓰는지 몰랐는데 멍청한 생각이었다. GROUP BY를 썼으면 집계함수를 써서 값을 가져와야 한다. MIN을 쓴 이유는 값을 하나만 가져오기 위해서다. 이 쿼리에서 집계함수는 그자체의 목적이라기 보다 "NULL이 아닌 값이 있으면 그 값을 선택하라"는 논리적 기능을 한다.