GROUP BY - [언어별 개발자 분류하기] _ LEVEL 4

해당 문제는 원래 9주차에 풀었던 문제지만, 풀이 과정에 대한 자세한 설명이 필요해 따로 포스팅한다

프로그래머스 문제 링크 : https://school.programmers.co.kr/learn/courses/30/lessons/276036

---

[ 문제 ]

SKILLCODES 테이블은 개발자들이 사용하는 프로그래밍 언어에 대한 정보를 담은 테이블입니다.

DEVELOPERS 테이블은 개발자들의 프로그래밍 스킬 정보를 담은 테이블입니다.

DEVELOPERS 테이블에서 GRADE별 개발자의 정보를 조회하려 합니다. GRADE는 다음과 같이 정해집니다.

• A : Front End 스킬과 Python 스킬을 함께 가지고 있는 개발자
• B : C# 스킬을 가진 개발자
• C : 그 외의 Front End 개발자

GRADE가 존재하는 개발자의 GRADE, ID, EMAIL을 조회하는 SQL 문을 작성해 주세요.

결과는 GRADE와 ID를 기준으로 오름차순 정렬해 주세요.

 

[ 풀이 ]

• 해당 문제를 풀기 위해서는 아래 개념에 대한 이해가 필요하다
  1. 비트연산을 조건으로 하는 JOIN : 8주차 - 1의 FrontEnd 개발자 찾기 풀이 참고
  2. GROUP_CONCAT() 함수
  3. ASCII CODE의 성질을 이용한 MAX(문자열) 활용 : 5주차의 자동차 대여 기록에서 대여중/대여 가능 여부 구분하기 풀이 참고
  4. CASE WHEN
  5. GROUP BY와 HAVING절
• 하나의 문제에 이해해야 할 개념이 다수 포함되어 있으므로, 단계별로 나눠서 풀이

---

STEP 1. 테이블 JOIN ( WITH 비트연산 )

 

• DEVELOPERS 테이블의 SKILLCODES 필드는 개발자가 지닌 한 개 이상의 스킬 코드를 나타내는 정보
• 비트연산의 성질을 이용해서 두 테이블을 JOIN

SELECT *
FROM developers D
JOIN skillcodes S ON D.skill_code & S.code = S.code;

D.skill_code & S.code = S.code 조건으로 두 테이블을 JOIN한 결과. 위에서 예시로 든 D165 개발자가 javascript, java, python의 스킬을 가지고 있기 때문에 JOIN 결과 각각 총 3개의 행으로 출력.

 

---

STEP 2. GROUP_CONCAT(), MAX(문자열)로 조건 구성

SELECT id, GROUP_CONCAT(name), GROUP_CONCAT(category), MAX(category)
FROM developers D
JOIN skillcodes S ON D.skill_code & S.code = S.code
GROUP BY 1
ORDER BY 1;

1. GROUP_CONCAT() 함수
   • GROUP_CONCAT() : 기존에 CONCAT()함수를 GROUP BY의 기준별로 실행하는 함수. default로 ,(comma)가 separator로 붙는다.
   • 개발자별(GROUP BY id)로 가진 스킬들의 이름(name)을 나열해서 나타내고, 후에 ‘Python’, ‘C#’등의 스킬명을 찾기위해 사용
2. MAX(문자열)
   • 문제의 조건에서, A/C grade 의 조건에서 ‘Front End’ 범주의 스킬을 가지고 있는지를 확인해야 하므로, 개발자별(GROUP BY id)로 가진 스킬들의 범주(category) 중 ‘Front End’ 가 있는지를 확인해 봐야 함
   • MAX(문자열)은 문자열의 ASCII code에 따라 작동하는데, ‘Front End‘과 ‘Back End’의 아스키 코드는 각각 70, 66으로 MAX() 함수를 적용했을 시 개발자에게 ‘Front End‘ 스킬이 있다면 ‘Front End‘가 출력될 것
   • id별로 MAX(‘Front End’만 존재) → ’Front End’
     id별로 MAX(‘Back End’과 ‘Front End’이 혼재) → ‘Front End’
     id별로 MAX(‘Back End’만 존재) → ’Back End’
   • 만약 문제에서 ‘Back End’범주의 스킬을 갖고 있는지를 확인했어야 한다면, MIN(category)

F의 아스키코드는 70, B의 아스키코드는 66

---

STEP 3. CASE WHEN 절로 GRADE 필드 구성

SELECT 
    CASE WHEN (GROUP_CONCAT(name) LIKE "%Python%") AND MAX(category) = "Front End" THEN 'A'
        WHEN GROUP_CONCAT(name) LIKE "%C#%" THEN 'B'
        WHEN MAX(category) = "Front End" THEN 'C'
        ELSE NULL END AS grade
    , id
    , MIN(email) email
FROM developers D
JOIN skillcodes S ON D.skill_code & S.code = S.code
GROUP BY id
ORDER BY 1, 2;

출력 결과

 

• A : Front End 스킬과 Python 스킬을 함께 가지고 있는 개발자

WHEN (GROUP_CONCAT(name) LIKE "%Python%") AND MAX(category) = "Front End" THEN 'A'

• B : C# 스킬을 가진 개발자

WHEN GROUP_CONCAT(name) LIKE '%C#%' THEN 'B'

• C : 그 외의 Front End 개발자

WHEN MAX(category) = "Front End" THEN 'C'

• 문제에서는 언급이 없지만 그 외의 (Back End) 개발자는 Grade를 매길 수 없으므로 NULL 값 부여

ELSE NULL END AS grade

• 주의) CASE WHEN 절은 Python의 if가 아니라 else if와 같은 역할을 한다. 즉 실행의 순서가 결과에 영향을 줄 수 있다
  • MySQL에서 CASE WHEN 문은 조건을 순차적으로 평가하며, 첫 번째로 참인 조건에 해당하는 결과를 즉시 반환하고 나머지 조건은 무시. 이런 특성 때문에 실행 순서가 결과에 매우 중요한 영향을 미치게 됨.
  • 따라서, 여러 조건이 중첩될 수 있는 상황에서는 어느 조건을 먼저 평가하느냐에 따라 결과가 달라질 수 있음.
  • 만약 C > B > A 순으로 CASE WHEN문을 구성하게 되면, Front End 스킬과 Python 스킬을 함께 가지고 있는 개발자가 A 등급이 아닌 C 등급으로 먼저 분류가 되는 오류가 발생!

---

STEP 4. HAVING절에서 GRADE가 존재하는 결과만 필터링

SELECT 
    CASE WHEN (GROUP_CONCAT(name) LIKE "%Python%") AND MAX(category) = "Front End" THEN 'A'
        WHEN GROUP_CONCAT(name) LIKE "%C#%" THEN 'B'
        WHEN MAX(category) = "Front End" THEN 'C'
        ELSE NULL END AS grade
    , id
    , MIN(email) email
FROM developers D
JOIN skillcodes S ON D.skill_code & S.code = S.code
GROUP BY id
HAVING grade IS NOT NULL
ORDER BY 1, 2;

• grade IS NOT NULL 이라는 조건을 걸어줘야 하는데, 중요한 건 WHERE 절이 아닌 HAVING 절에 명시해야 한다는 것
  • 목적 : 그룹화된 결과에 대해 조건을 적용
  • 사용 시점 : FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY
  • 만약 WHERE 절에 위의 조건을 걸면 쿼리의 실행순서 때문에 grade라는 필드를 인식하지 못한다

WHERE grade IS NOT NULL로 실행 시 에러 발생

 

---

[ 최종 정답 쿼리 ]

SELECT 
    CASE WHEN (GROUP_CONCAT(name) LIKE "%Python%") AND MAX(category) = "Front End" THEN 'A'
        WHEN GROUP_CONCAT(name) LIKE "%C#%" THEN 'B'
        WHEN MAX(category) = "Front End" THEN 'C'
        ELSE NULL END AS grade
    , id
    , MIN(email) email
FROM developers D
JOIN skillcodes S ON D.skill_code & S.code = S.code
GROUP BY id
HAVING grade IS NOT NULL
ORDER BY 1,2;

최종 출력 결과