SQL 문제풀이 1)

SQL 문제풀이 중 인상 깊은 문제가 존재하여 게시글을 작성합니다.

 

문제 내용을 요약하자면 다음과 같습니다.

1.테이블의 열을 행으로 표시해라.

2.행으로 표시할 때 열에 해당되는 특정 데이터는 순서가 존재한다.

3. 특정 데이터가 여러 개 일경우 알파벳 순으로 다음 행에 표시한다.

4. 행에 표시할 데이터가 없을경우 NULL로 표시한다.

 

Output

Jun July Kim hong
Null amily doc collen

 

우선 요구사항을 정리하자면 열을 행으로 표시하기 위해 피벗을 이용해야한다는 것을 알 수 있었습니다.

MySQL은 pivot을 지원하지 않지만 group_concat 함수를 이용하여 행에 열 데이터(구분자 포함)를 표시할 수 있습니다. 

 

group_concat(column, separator ',')  

 

하지만 열을 나타내는 데이터 간에는 순서가 존재하기 때문에 group_concat 함수를 사용할  수 없습니다.

 

테이블

Name	Occupation
Lee	Doctor
Hong	Professor
Kim	Actor
Jun	Singer
Lyier	Doctor

 

예를들면 'Doctor'을 나타내는 데이터는 첫번째로 표시해야 하며 'Programmer'를 나타내는 데이터는 두번째로 표시해야합니다. 또한, Doctor를 표시하는 데이터는 Lee, Lyier가 존재하는데 Lee를 첫번째 행에 표시하며 Lyier를 두번째 행에 표시해야합니다.

조건을 만족하기 위해서는 같은 Occupation을 가진 데이터 간의 rank를 매겨야합니다.

 

SELECT Name, OCCUPATION, ROW_NUMBER() OVER(PARTITION BY OCCUPATION ORDER BY Name) as num
FROM OCCUPATIONS

 

rank를 매긴 데이터들을 기반으로 각 Occupation에 순서를 정합니다.

부여했던 rank 기준으로 그룹핑한 후 MAX 함수를 사용합니다.(데이터를 필드로 표현할 때 NULL을 표시하지 않기 위해) 

 

SELECT 
    MAX(CASE WHEN OCCUPATION = 'doctor' THEN Name ELSE NULL END),
    MAX(CASE WHEN OCCUPATION = 'professor' THEN Name ELSE NULL END),
    MAX(CASE WHEN OCCUPATION = 'singer' THEN Name ELSE NULL END),
    MAX(CASE WHEN OCCUPATION = 'actor' THEN Name ELSE NULL END)
FROM (
SELECT Name, OCCUPATION, ROW_NUMBER() OVER(PARTITION BY OCCUPATION ORDER BY Name) as num
FROM OCCUPATIONS) OC
GROUP BY num

 

* 보통 pivot을 하기 위해 group by와 MAX 함수를 많이 사용하지만 이번 문제와 같이 표시해야하는 데이터가 숫자가 아닌 문자인 경우가 존재합니다. 문자일경우 rank를 부여함으로써 해결할 수 있음을 알 수 있었습니다.