프로그래머스: 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기 - 시도 및 해결 과정 (MySQL)

 

1. 문제

---

2. 문제 풀기 전에,

• HR_DEPARTMENT 👉🏻 X
• HR_EMPLOYEES 👉🏻 사번, 사원명, 보너스 조회
• HR_GRADE  👉🏻 평가 등급 조회
• 평가 점수별 등급과 성과금 정보에 따른 값 계산하기
• 사번 ASC

---

3. 테이블

HR_DEPARTMENT AS DEPT

 

HR_EMPLOYEES AS EMP

 

HR_GRADE AS GR

 

4. 나의 시도

시도 1) 테이블 DEPT은 조회할 컬럼이 없으니 제외하고, 테이블 EMP와 테이블 GR은 ‘사번’이라는 동일한 컬럼을 가지고 있으니 INNER JOIN을 해준다.

SELECT
FROM HR_EMPLOYEES AS EMP
INNER JOIN HR_GRADE AS GR ON EMP.EMP_NO = GR.EMP_NO

 

시도 2) 아래는 ‘평가 점수별 등급과 등급에 따른 성과금 정보’에 대한 정보이다.

이를 통해 1) 점수에 따른 평가 등급  2) 평가 등급에 따른 성과금을 계산할 수 있다.

특정 컬럼에 대하여 여러 조건에 따른 결과값을 출력해 줘야 하니 CASE문을 사용한다.

조건을 제시해 주고, 그에 반환하는 값을 작성해 주는 MySQL의 CASE문은 Python의 IF문과 비슷하다.

CASE                                         -- CASE문 기본 형식
	WHEN 조건1 THEN 조건1 충족할 때 반환되는 값        -- WHEN과 THEN은 짝꿍이므로 항상 같이 써주기!
	WHEN 조건2 THEN 조건2 충족할 때 반환되는 값
	WHEN 조건3 THEN 조건3 충족할 때 반환되는 값
	ELSE 모든 조건 해당되지 않을 때 반환되는 값
END                                          -- 조건문 마지막에 END는 필수!

 

CASE문을 이용하여 점수에 따른 평가 등급 조건과 평가 등급에 따른 보너스 조건을 추가해 준다.

테이블 GR에 사원들의 점수가 상,하반기(1, 2 In HALF_YEAR)로 나누어져 있기 때문에 AVG() 함수를 이용하여 평균값을 구해준다.

SELECT EMP.EMP_NO, EMP.EMP_NAME,
       (CASE
            WHEN AVG(GR.SCORE) >= 96 THEN 'S'
            WHEN AVG(GR.SCORE) >= 90 THEN 'A'
            WHEN AVG(GR.SCORE) >= 80 THEN 'B'
            ELSE 'C'
        END) AS GRADE,
       (CASE
            WHEN GRADE = 'S' THEN (EMP.SAL*20/100)
            WHEN GRADE = 'A' THEN (EMP.SAL*15/100)
            WHEN GRADE = 'B' THEN (EMP.SAL*10/100)
            ELSE 0
        END) AS BONUS
FROM HR_EMPLOYEES AS EMP
INNER JOIN HR_GRADE AS GR ON EMP.EMP_NO = GR.EMP_NO
GROUP BY EMP.EMP_NO 
ORDER BY EMP.EMP_NO ASC;

 

시도 3) 사번이 곧 사원명을 의미하니 사번을 기준으로 그룹화를 해주고, 사번을 기준으로 오름차순 정렬을 해준다.

SELECT EMP.EMP_NO, EMP.EMP_NAME,
       (CASE
            WHEN AVG(GR.SCORE) >= 96 THEN 'S'
            WHEN AVG(GR.SCORE) >= 90 THEN 'A'
            WHEN AVG(GR.SCORE) >= 80 THEN 'B'
            ELSE 'C'
        END) AS GRADE,
       (CASE
            WHEN GRADE = 'S' THEN (EMP.SAL*20/100)
            WHEN GRADE = 'A' THEN (EMP.SAL*15/100)
            WHEN GRADE = 'B' THEN (EMP.SAL*10/100)
            ELSE 0
        END) AS BONUS
FROM HR_EMPLOYEES AS EMP
INNER JOIN HR_GRADE AS GR ON EMP.EMP_NO = GR.EMP_NO
GROUP BY EMP.EMP_NO 
ORDER BY EMP.EMP_NO ASC;

 

땡!

호기롭게 시도하였지만 오답이었다. 이유는 뭘까?

 

5. 오답에 대한 고찰

고찰 1) 첫번째 조건문에서 점수에 따른 평가 등급을 ‘GRADE’라는 컬럼으로 정의하였고, 두번째 조건문에서 이 ‘GRADE’라는 컬럼에 따른 보너스를 계산해주는 CASE문을 작성하였다.

개인적인 생각으로 위 조건문에서 새로운 컬럼을 정의를 해두었으니, 이 컬럼을 가지고 두번째 조건문에 적용하면 순차적으로 계산이 가능할 것이라고 판단하였다.

(CASE
    WHEN AVG(G.SCORE) >= 96 THEN 'S'
    WHEN AVG(G.SCORE) >= 90 THEN 'A'
    WHEN AVG(G.SCORE) >= 80 THEN 'B'
    ELSE 'C'
END) AS GRADE,
(CASE
    WHEN GRADE = 'S' THEN (EMP.SAL*20/100)
    WHEN GRADE = 'A' THEN (EMP.SAL*15/100)
    WHEN GRADE = 'B' THEN (EMP.SAL*10/100)
    ELSE 0
END) AS BONUS

 

고찰 2) 두 조건문을 모두 돌렸을 때, "Unknown column 'GRADE' in 'field list'”라는 에러를 확인하였다. GRADE 컬럼에 문제가 있다는 것이다.

    ▶ 첫번째 조건문만 돌렸을 때는 정상적으로 출력이 되는 것을 확인하였고, 두번째 조건문에 GRADE 대신 첫번째 조건문에서 썼던 것과 동일한 형식의 조건을 적용하였다.

 

조건문을 수정하는 김에 보너스 계산식도 간단하게 소수점으로 변경해 주었다.

(CASE
    WHEN AVG(G.SCORE) >= 96 THEN 'S'
    WHEN AVG(G.SCORE) >= 90 THEN 'A'
    WHEN AVG(G.SCORE) >= 80 THEN 'B'
    ELSE 'C'
END) AS GRADE,
(CASE
    WHEN AVG(G.SCORE) >= 96 THEN E.SAL*0.2
    WHEN AVG(G.SCORE) >= 90 THEN E.SAL*0.15
    WHEN AVG(G.SCORE) >= 80 THEN E.SAL*0.1
    ELSE 0
END) AS BONUS

     👨🏻‍🏫 두번째 조건문을 작성할 때 주의할 필요가 있다. 위 조건문에서 정의한 컬럼은 아래 조건문에 바로 적용이 되지 않는다.

 

6. 완성 코드

SELECT E.EMP_NO, E.EMP_NAME,
       (CASE
            WHEN AVG(G.SCORE) >= 96 THEN 'S'
            WHEN AVG(G.SCORE) >= 90 THEN 'A'
            WHEN AVG(G.SCORE) >= 80 THEN 'B'
            ELSE 'C'
        END) AS GRADE,
       (CASE
            WHEN AVG(G.SCORE) >= 96 THEN E.SAL*0.2
            WHEN AVG(G.SCORE) >= 90 THEN E.SAL*0.15
            WHEN AVG(G.SCORE) >= 80 THEN E.SAL*0.1
            ELSE 0
        END) AS BONUS
FROM HR_EMPLOYEES AS E
INNER JOIN HR_GRADE AS G ON E.EMP_NO = G.EMP_NO
GROUP BY E.EMP_NO
ORDER BY E.EMP_NO ASC;

 

 

🔗 관련 링크

프로그래머스 문제 - https://school.programmers.co.kr/learn/courses/30/lessons/284528
CASE문 설명 - https://wnwa.tistory.com/21