[SQL][프로그래머스] 조건에 맞는 사원 정보 조회하기 - GROUP BY + INNER JOIN + SUM + LIMIT + SUBQUERY(INLINEVIEW) MYSQL

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

프로그래머스

코드설명

GROUP BY + INNER JOIN + SUM + LIMIT + SUBQUERY(INLINEVIEW)를 활용합니다.

 

문제에서 HR_DEPARTMENT 테이블은 사용안해도됩니다.

 

두가지 방식으로 풀 수 있습니다.

첫번쨰 방식은, INNER JOIN을 활용하여 NESTED LOOP JOIN으로 테이블을 만들어 점수 합으로 내림차순 정렬한뒤 최상단의 데이터를 LIMIT 1 로 조회합니다.

이 방식은, 큰 데이터에 대해 불필요하게 정렬이 진행되므로 비효율적일 것 이라 예상됩니다.

 

두번째 방식은, 

 

문제에서 유의할점은 HAVING 이 언제 쓰이는가에 대하여입니다.

오류코드에 대한 설명입니다.

왜 GROUP BY에서 HAVING HG1.YEAR = 2022를 안했을까요?

이유는, HAVING절은 그룹화된 데이터에 대해서 필터링을 수행하기 때문입니다.

이미 GROUP BY 과정에서 HG1.EMP_NO로 그룹화된 상태이기에, 작동이 안됩니다. 대신에, 이미 그룹화된 데이터에서는 필터링이 작동하겠지요.

만약, HAVING절에 집계함수가 사용된 경우 SUM(HG1.SCORE) >= 0 와 같은 형태일경우 GROUP BY가 진행된 이후 필터링을 처리하기에 가능합니다.

SELECT 
    SUM(HG1.SCORE) AS SCORE, 
    HG1.EMP_NO AS EMP_NO, 
    HE1.EMP_NAME AS EMP_NAME, 
    HE1.POSITION AS POSITION,
    HE1.EMAIL AS EMAIL
FROM HR_EMPLOYEES AS HE1
INNER JOIN HR_GRADE AS HG1
ON HE1.EMP_NO = HG1.EMP_NO
# WHERE HG1.YEAR = 2022
GROUP BY HG1.EMP_NO
HAVING HG1.YEAR = 2022
ORDER BY SUM(HG1.SCORE) DESC
LIMIT 1;

 

 처음에 HAVING 절에는 SUBQUERY 를 사용해서 처리할려고했지만, 서브쿼리 안에 GROUP BY를 사용하고.. 불필요하게 복잡해집니다.

 

코드

2번 조인되므로 NLP(NESTED LOOP JOIN)으로 시간이 오래걸립니다.

SELECT SUM(SCORE) AS SCORE, HR_GRADE.EMP_NO, HR_EMPLOYEES.EMP_NAME, HR_EMPLOYEES.POSITION, HR_EMPLOYEES.EMAIL 
FROM HR_GRADE
INNER JOIN HR_EMPLOYEES
ON HR_EMPLOYEES.EMP_NO = HR_GRADE.EMP_NO
INNER JOIN HR_DEPARTMENT
ON HR_DEPARTMENT.DEPT_ID = HR_EMPLOYEES.DEPT_ID
WHERE HR_GRADE.YEAR = 2022
GROUP BY HR_GRADE.EMP_NO
ORDER BY SCORE DESC
LIMIT 1;

 

가장 적합한 코드입니다.

SELECT 
    SUM(HG1.SCORE) AS SCORE, 
    HG1.EMP_NO AS EMP_NO, 
    HE1.EMP_NAME AS EMP_NAME, 
    HE1.POSITION AS POSITION,
    HE1.EMAIL AS EMAIL
FROM HR_EMPLOYEES AS HE1
INNER JOIN HR_GRADE AS HG1
ON HE1.EMP_NO = HG1.EMP_NO
WHERE HG1.YEAR = 2022
GROUP BY HG1.EMP_NO
ORDER BY SUM(HG1.SCORE) DESC
LIMIT 1;

가장 적합한 코드2입니다.

SELECT SUM(HG.SCORE) AS SCORE, HE.EMP_NO, HE.EMP_NAME, HE.POSITION, HE.EMAIL
FROM HR_EMPLOYEES AS HE
INNER JOIN HR_GRADE AS HG
ON HE.EMP_NO = HG.EMP_NO
WHERE HG.YEAR = 2022
GROUP BY EMP_NO
ORDER BY SCORE DESC
LIMIT 1

 

아래와 같이 인라인 뷰를 활용해서도 가능합니다. (FROM절안에 서브쿼리가 들어가는 형태)

필요한 부분만 가져와서 처리하므로 이 쿼리가 더 빠를것입니다.

 

인라인뷰를 활용했을때, DRIVING TABLE과 DRIVEN 테이블의 개수 관계 또한 고려하면 좋을 것 입니다.

매번 GROUP BY, ORDER BY 가 일어나는 QUERY의 데이터 양이 적을수록 좋을 것 입니다.

즉, 주어진 문제에서는 HR_GRADE의 개수가 더 작기 때문에 해당 QUERY를 인라인뷰를 활용한다면 더 효과적일 것으로 예상됩니다.

-- 코드를 작성해주세요

SELECT MAXSCORETABLE.SCORE, MAXSCORETABLE.EMP_NO, HR_EMPLOYEES.EMP_NAME, HR_EMPLOYEES.POSITION, HR_EMPLOYEES.EMAIL
FROM (
    SELECT EMP_NO, SUM(SCORE) AS SCORE
    FROM HR_GRADE
    GROUP BY EMP_NO
    ORDER BY SCORE DESC
    LIMIT 1
) AS MAXSCORETABLE
INNER JOIN HR_EMPLOYEES
ON MAXSCORETABLE.EMP_NO = HR_EMPLOYEES.EMP_NO;

 

만약, HR_EMPLOYEES를 인라인뷰로 사용하는 경우입니다.

HR_EMPLOYEES 보다 HR_GRADE의 데이터 레코드가 더 많으므로 비교적 비효율적일 것 입니다. (정렬이 더 많이 일어나기 때문)

SELECT HG1_SUBQUERY.HG1_SCORE_SUM AS SCORE, 
HE1.EMP_NO AS EMP_NO,
HE1.EMP_NAME AS EMP_NAME,
HE1.POSITION AS POSITION,
HE1.EMAIL AS EMAIL
FROM HR_EMPLOYEES AS HE1
INNER JOIN ( SELECT
                HG1.EMP_NO AS EMP_NO,
                SUM(HG1.SCORE) AS HG1_SCORE_SUM
            FROM HR_GRADE AS HG1
            WHERE HG1.YEAR = 2022
            GROUP BY HG1.EMP_NO
            ORDER BY SUM(HG1.SCORE) DESC
            LIMIT 1
           ) AS HG1_SUBQUERY
ON HE1.EMP_NO = HG1_SUBQUERY.EMP_NO