HackerRank SQL - Occupations

 

 

Pivot the Occupation column in OCCUPATIONS so that each Name is sorted alphabetically and displayed underneath its corresponding Occupation. The output column headers should be Doctor, Professor, Singer, and Actor, respectively.

Note: Print NULL when there are no more names corresponding to an occupation.

Input Format

The OCCUPATIONS table is described as follows:

Occupation will only contain one of the following values: Doctor, Professor, Singer or Actor.

Sample Input

Sample Output

Jenny    Ashley     Meera  Jane
Samantha Christeen  Priya  Julia
NULL     Ketty      NULL   Maria

Explanation

The first column is an alphabetically ordered list of Doctor names.
The second column is an alphabetically ordered list of Professor names.
The third column is an alphabetically ordered list of Singer names.
The fourth column is an alphabetically ordered list of Actor names.
The empty cell data for columns with less than the maximum number of names per occupation (in this case, the Professor and Actor columns) are filled with NULL values.

Problem

첫 번째 열은 알파벳순으로 정렬된 직업이 의사인 이름,

두 번째 열은 알파벳순으로 정렬된 직업이 교수인 이름,

세 번째 열은 알파벳순으로 정렬된 직업이 가수인 이름,

네 번째 열은 알파벳순으로 정렬된 직업이 배우인 이름으로

빈 셀 데이터는 NULL로 표기하라.

---

Answer1

SELECT GROUP_CONCAT(IF(TEMP.NUM_COL = 1, TEMP.NAME, NULL)) AS 'Doctor'
      ,GROUP_CONCAT(IF(TEMP.NUM_COL = 2, TEMP.NAME, NULL)) AS 'Professor'
      ,GROUP_CONCAT(IF(TEMP.NUM_COL = 3, TEMP.NAME, NULL)) AS 'Singer'
      ,GROUP_CONCAT(IF(TEMP.NUM_COL = 4, TEMP.NAME, NULL)) AS 'Actor'
FROM
((SELECT '1' NUM_COL, row_number() over (order by D.NAME) NUM_ROW, D.NAME FROM OCCUPATIONS D WHERE D.Occupation = 'Doctor')
UNION ALL
(SELECT '2' NUM_COL, row_number() over (order by P.NAME) NUM_ROW, P.NAME FROM OCCUPATIONS P WHERE P.Occupation = 'Professor')
UNION ALL
(SELECT '3' NUM_COL, row_number() over (order by S.NAME) NUM_ROW, S.NAME FROM OCCUPATIONS S WHERE S.Occupation = 'Singer')
UNION ALL
(SELECT '4' NUM_COL, row_number() over (order by A.NAME) NUM_ROW, A.NAME FROM OCCUPATIONS A WHERE A.Occupation = 'Actor')) TEMP
GROUP BY TEMP.NUM_ROW

각 직업별, 이름을 row, column의 번호를 부여해 그 번호를 IF문으로 pivot했다.

물론 case when 구문으로도 가능하다.

---

How to solve

 실무에서 SQL을 쓰면서 pivot이 필요한 경우는 거의 없었다. 아니 정확하게 말하면 굳이 쓸 필요가 없다고 하는게 맞을 것 같다.

 

왜냐하면 대충 추출해서 엑셀로 pivot을돌려버리는게 훨씬 편하고 빠르니까.

우선 MySQL은 pivot 내장 함수가 없어서 직접 쿼리를 짜야 하는데 작성 시간도 그렇고 안그래도 복잡해질 쿼리에 최적화까지 생각하면 도저히 견적이 안나온다.

 

그래서 부끄럽게도 한 번도 시도해보지도 않았지만, 마침 문제가 나와서 풀어보게 되었다.

 

(SELECT '1' NUM_COL, row_number() over (order by D.NAME) NUM_ROW, D.NAME FROM OCCUPATIONS D WHERE D.Occupation = 'Doctor')
UNION ALL
(SELECT '2' NUM_COL, row_number() over (order by P.NAME) NUM_ROW, P.NAME FROM OCCUPATIONS P WHERE P.Occupation = 'Professor')
UNION ALL
(SELECT '3' NUM_COL, row_number() over (order by S.NAME) NUM_ROW, S.NAME FROM OCCUPATIONS S WHERE S.Occupation = 'Singer')
UNION ALL
(SELECT '4' NUM_COL, row_number() over (order by A.NAME) NUM_ROW, A.NAME FROM OCCUPATIONS A WHERE A.Occupation = 'Actor')

rownum을 넣고 하는 방법도 있지만 그 방법 너무 손에 익질 않아서 윈도우 함수를 이용해서 row number를 세어주고, 직업별 순서는 제시가 되었기 때문에 각 직업마다 colum number를 부여했다.

첫 번째 컬럼이 열번호, 두 번째 컬럼이 행번호가 된다.

그럼 상기와 같은 모습으로 추출되는데, 이걸 이용해서 테이블을 다시 만들어준다.

 

SELECT GROUP_CONCAT(TOTAL.NAME), GROUP_CONCAT(NUM_COL)
FROM
((SELECT '1' NUM_COL, row_number() over (order by D.NAME) NUM_ROW, D.NAME FROM OCCUPATIONS D WHERE D.Occupation = 'Doctor')
UNION ALL
(SELECT '2' NUM_COL, row_number() over (order by P.NAME) NUM_ROW, P.NAME FROM OCCUPATIONS P WHERE P.Occupation = 'Professor')
UNION ALL
(SELECT '3' NUM_COL, row_number() over (order by S.NAME) NUM_ROW, S.NAME FROM OCCUPATIONS S WHERE S.Occupation = 'Singer')
UNION ALL
(SELECT '4' NUM_COL, row_number() over (order by A.NAME) NUM_ROW, A.NAME FROM OCCUPATIONS A WHERE A.Occupation = 'Actor')) TEMP
GROUP BY TEMP.NUM_ROW

그리고 서브쿼리로 묶어두고, GROUP_CONCAT함수를 사용해준다.

GROUP_CONCAT은 GROUP BY 절을 기준으로 구분자를 넣지 않으면 쉼표로 같은 컬럼으로 묶어주는 함수다.

 

그럼 아래와 같이 나온다.

TEMP.NUM_ROW로 묶어주었기 때문에 열이 직업이 되고 행이 해당 직업을 가진 사람과 순서가 나온다.

 

 

 

SELECT GROUP_CONCAT(IF(TEMP.NUM_COL = 1, TEMP.NAME, NULL)) AS 'Doctor'
      ,GROUP_CONCAT(IF(TEMP.NUM_COL = 2, TEMP.NAME, NULL)) AS 'Professor'
      ,GROUP_CONCAT(IF(TEMP.NUM_COL = 3, TEMP.NAME, NULL)) AS 'Singer'
      ,GROUP_CONCAT(IF(TEMP.NUM_COL = 4, TEMP.NAME, NULL)) AS 'Actor'
FROM
((SELECT '1' NUM_COL, row_number() over (order by D.NAME) NUM_ROW, D.NAME FROM OCCUPATIONS D WHERE D.Occupation = 'Doctor')
UNION ALL
(SELECT '2' NUM_COL, row_number() over (order by P.NAME) NUM_ROW, P.NAME FROM OCCUPATIONS P WHERE P.Occupation = 'Professor')
UNION ALL
(SELECT '3' NUM_COL, row_number() over (order by S.NAME) NUM_ROW, S.NAME FROM OCCUPATIONS S WHERE S.Occupation = 'Singer')
UNION ALL
(SELECT '4' NUM_COL, row_number() over (order by A.NAME) NUM_ROW, A.NAME FROM OCCUPATIONS A WHERE A.Occupation = 'Actor')) TEMP
GROUP BY TEMP.NUM_ROW

마지막으로 문제 요구사항에 맞게 row는 맞춰놨으니 column까지 맞춰준다.

 

우여곡절 끝에 다 맞추긴 했는데 역시 효율적이었나에는 의문이 든다.

그래서 그런지 레퍼런스로 참고했던 분의 방식이 더 깔끔하긴 하다.

 

 

 

참고

https://mia-dahae.tistory.com/82