[MySQL] 프로그래머스 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/284528

프로그래머스

코딩테스트 연습 > GROUP BY > 연간 평가점수에 해당하는 평가 등급 및 성과금 조회하기

 

문제 분석

난이도: LEVEL4

 

문제 요구사항

HR_DEPARTMENT, HR_EMPLOYEES, HR_GRADE 테이블을 이용해 사원별 성과금 정보를 조회하려합니다.

평가 점수별 등급과 등급에 따른 성과금 정보가 아래와 같을 때, 사번, 성명, 평가 등급, 성과금을 조회하는 SQL문을 작성해주세요.
평가등급의 컬럼명은 GRADE로, 성과금의 컬럼명은 BONUS로 해주세요.
결과는 사번 기준으로 오름차순 정렬해주세요.

 

정답 코드 1

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

 

필요한 정보 사원번호, 사원이름, 등급(GRADE), 성과금(BONUS)

문제에서는 HR_DEPARTMENT, HR_EMPLOYEES, HR_GRADE 이렇게 3개 테이블이 있지만

필요한 정보들은 HR_EMPLOYEES, HR_GRADE 2개 테이블에 있어서 두 테이블만 조회하면 된다. 

 

EMP_NO (사원 번호)를 기준으로 INNER JOIN 

HR_GRADE를 보면 2022년에는 2개의 분기로 SCORE가 나눠져있다. 

EMP_NO 기준으로 GROUP BY 

그리고 점수는 평균을 내주면 된다. 

SUM(SCORE) / COUNT(*) = AVG(SCORE) 나는 AVG(SCORE)를 사용했다. 

 

등급과 보너스를 CASE WHEN THEN을 사용하여 조건에 따라 다르게 출력한다. 

 

정답코드1 문제점

쿼리를 보면 매번 AVG(SCORE)를 계산하고 있다. 

각 CASE 구문에서 이 평균 값을 매번 계산하는데 이것은 성능 저하로 이어질 수 있다.

동일한 값을 여러 번 계산하는 대신, 서브 쿼리 혹은 CTE를 사용하여 AVG(SCORE)를 한 번만 계산하고 

그 값을 재사용하는 것이 더 효율적이다. 

 

정답 코드 2(정답 코드1 개선) 

SELECT E.EMP_NO, EMP_NAME, 
CASE 
   WHEN AVG_SCORE >= 96 THEN 'S'
   WHEN AVG_SCORE >= 90 THEN 'A'
   WHEN AVG_SCORE >= 80 THEN 'B'
   ELSE 'C'
END
GRADE, 
CASE 
   WHEN AVG_SCORE >= 96 THEN (SAL / 100 * 20)
   WHEN AVG_SCORE >= 90 THEN (SAL / 100 * 15)
   WHEN AVG_SCORE >= 80 THEN (SAL / 100 * 10)
   ELSE 0
END
BONUS
FROM HR_EMPLOYEES E JOIN (SELECT EMP_NO, AVG(SCORE) AVG_SCORE 
                          FROM HR_GRADE 
                          GROUP BY EMP_NO) SB -- 서브 쿼리에서 GROUP BY 
ON E.EMP_NO = SB.EMP_NO
ORDER BY 1

 

HR_GRADE 테이블의 전체 데이터를 조인하는 게 아닌 

GROUP BY를 통해 1분기 2분기의 SCORE 평균과, EMP_NO 사원번호만 가져와서 조인하도록 개선했다.

 

해당 개선을 통해 이전에는 여러 번 AVG(SCORE)가 실행되었는데 한 번의 계산을 재사용 하도록 바꾸었다.