프로그래머스 Level 4 - 언어별 개발자 분류하기

• 언어별 개발자 분류하기 [LEVEL 4]

이번부터는 SQL 문제도 포스팅해보기로 했다.
코딩테스트에 SQL문제가 포함이 되는 회사도 있기도 하고,
상당히 어려운 SQL 문제들도 많기 때문이다.

이번 문제는 프로그래머스의 레벨 4 언어별 개발자 분류하기이다.
문제를 차근차근 읽어보도록 하자.

아래와 같은 SKILLCODE 테이블이 주어진다.

해당 테이블은 각 언어에 대한 정보가 담겨있다.
아래와 같이 DEVELOPER 테이블이 주어진다.

해당 테이블은 개발자들에 대한 정보가 담겨있다.
위의 테이블들을 이용하여 개발자들의 등급을 매기면 된다.
등급의 기준은 아래와 같다.

• A : Front End 기술과 Python 기술을 동시에 가진 경우
• B : C# 기술을 가진 경우
• C : Front End 기술만 가진 개발자

문제를 어떻게 해결해야 할까?

WITH 절을 이용한 풀이

나는 가장 먼저 등급을 정할 수 있는 임시 테이블이 필요하다고 생각했다.
하지만 WITH 절을 사용하는 것이 익숙치 않아, 검색을 해야 했다.
예를 들어 아래와 같이 사용할 수 있다.

WITH tmp_table as (
    SELECT 'KIM' AS NAME, 25 AS AGE
    FROM DUAL
    UNION
    SELECT 'PARK' AS NAME, 28 AS AGE
    FROM DUAL
)

이렇게 WITH 절을 사용하게 되면, 이름과 나이가 저장된 테이블이 생성된다.
여기서 FROM DUAL은 빈 더미 테이블을 의미한다.
WITH의 이런 기능을 이용해서 코드를 미리 저장해놓는 테이블을 만드는 것이다.
아래와 같이 3개의 임시 테이블을 생성했다.

WITH 
py as(
    SELECT CODE
    FROM SKILLCODES
    WHERE NAME = 'Python'
),
csh as(
    SELECT CODE
    FROM SKILLCODES
    WHERE NAME = 'C#'
),
fe as(
    SELECT SUM(CODE) AS CODE
    FROM SKILLCODES
    WHERE CATEGORY = 'Front End'
)

위의 테이블들은 각 조건에 맞는 언어 코드를 담고있다.
위의 테이블들을 이용해서 아래와 같이 쿼리를 짤 수 있다.

SELECT 
CASE WHEN d.SKILL_CODE & py.CODE AND d.SKILL_CODE & fe.CODE THEN 'A'
WHEN d.SKILL_CODE & csh.CODE THEN 'B'
WHEN d.SKILL_CODE & fe.CODE THEN 'C'
ELSE NULL END
AS GRADE, d.ID, d.EMAIL
FROM DEVELOPERS d, py, csh, fe
HAVING GRADE IS NOT NULL
ORDER BY GRADE, d.ID

CASE WHEN THEN 절을 이용하여 깔끔하게 쿼리가 짜졌다.
해당 코드에 부합하는 기술을 보유했는지 검증하기 위해 &를 사용한다.
처음 보게 되면 굉장히 당황스러운 방식이다.

하지만 문제를 다시 꼼꼼히 읽어보면 비트 연산을 통한 교집합 검증임을 알 수 있다.
py.CODE에서 1이 있는 자리와 d.SKILL_CODE에서 1이 있는 자리가 겹치면?
이 조건은 true를 반환하게 될 것이다.

예를 들어 개발자의 CODE가 400이라고 가정해보자.
이는 110010000라는 이진수 비트로 바꿀 수 있다.
즉, 해당 개발자는 256, 128, 16의 CODE를 갖는 언어를 사용할 수 있다.
이는 SKILLCODE 테이블에서, Python, Java, Javascript를 의미한다.

위와 같은 원리를 통해 복잡했지만 문제를 풀 수 있었다.

CASE THEN WHEN 절을 이용한 풀이

WITH 절 없이도 이 문제를 풀 수 있는 꽤 신기한 방법이다.
나는 CASE THEN WHEN 절에도 익숙치 않아 떠올리지 못한 방법이다.

가장 먼저 CASE THEN WHEN 절을 이용해 등급을 분류해야 한다.
아래와 같이 쿼리를 짤 수 있다.

SELECT
CASE WHEN EXISTS (SELECT s.CODE FROM SKILLCODE s WHERE s.CODE & d.SKILL_CODE AND s.NAME = 'PYTHON')
AND EXISTS (SELECT s.CODE FROM SKILLCODE s WHERE s.CODE & d.SKILL_CODE AND s.CATEGORY = 'Front End') THEN 'A'
WHEN EXISTS (SELECT s.CODE FROM SKILLCODE s WHERE s.CODE & d.SKILL_CODE AND s.NAME = 'C#') THEN 'B'
WHEN EXISTS (SELECT s.CODE FROM SKILLCODE s WHERE s.CODE & d.SKILL_CODE AND s.CATEGORY = 'Front End') THEN 'C'
ELSE NULL END
AS GRADE, d.ID, d.EMAIL
FROM DEVELOPER d

먼저 EXISTS를 통해 boolean 값을 반환하도록 한다.
EXISTS 구문 내의 쿼리에 반환되는 행이 있으면 True가 되는 식이다.
첫 SELECT 문은 Python 기술을 가진 개발자라면 True를 반환하게 된다.
또한, AND 이후로 Front End 기술을 동시에 가짐을 검증하고 있다.

이후로 등장하는 WHEN EXISTS문도 모두 같은 원리로 검증된다.
또한 FROM DEVELOPER문에 의해 해당 검증은 개발자 1명 씩 차례차례 진행된다.

마지막 ELSE NULL 조건에 의해 등급이 없는 개발자는 NULL이 된다.
CASE WHEN ~ THEN ~ ELSE ~ END 구문이 모두 끝났다.
이제 NULL 등급의 개발자를 지워준 뒤, 정렬하여 출력하면 끝이다.

HAVING GRADE IS NOT NULL
ORDER BY GRADE, ID

Appendix : CASE WHEN THEN과 HAVING

위의 풀이를 보면, 조건절이 WHERE 대신 HAVING임을 볼 수 있다.
보통 GROUP BY와 짝을 지어 나타나야 하는데, 왜 단독으로 HAVING을 쓸까?
또, 왜 WHERE을 써서는 안될까?

WHERE절은 기본적으로 SELECT에서 데이터를 가공하기 이전에,
즉 가공되지 않은 DEVELOPER 테이블에서 GRADE 열을 찾으려니 오류가 발생한다.
HAVING절은 기본적으로 GROUP BY로 가공된 테이블을 정렬한다.
즉, SELECT에서 가공된 테이블을 하나의 GROUP으로 보고 필터링을 하게 된다!

따라서 단독 HAVING 절을 사용해야 올바르게 결과가 나오는 것이다.