SELECT절 alias는 윈도우 함수에서 바로 참조하지 말자.

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

1. 문제 상황

MySQL에서 특정 연도(YEAR = 2022)의 직원 점수를 합산한 후, 가장 높은 점수를 가진 직원을 찾는 경우였고, 이를 위해 ROW_NUMBER()를 활용해 다음과 같은 쿼리를 작성했는데 틀림

WITH SCORE_CTE AS (
    SELECT EMP_NO, SUM_SCORE
    FROM (
        SELECT
            EMP_NO,
            SUM(SCORE) AS SUM_SCORE,
            ROW_NUMBER() OVER (ORDER BY SUM_SCORE DESC) AS RN
        FROM HR_GRADE
        WHERE YEAR = 2022
        GROUP BY EMP_NO
    ) TEMP
    WHERE RN = 1
)
SELECT SC.SUM_SCORE, SC.EMP_NO, HE.POSITION, HE.EMAIL
FROM HR_EMPLOYEES HE
INNER JOIN SCORE_CTE SC
ON HE.EMP_NO = SC.EMP_NO;

2. 원인: SELECT 절에서 정의한 별칭(alias)은 동일한 SELECT 절의 윈도우 함수에서 직접 참조할 수 없다.

SQL의 실행 순서 상, SELECT 절 내에서 별칭은 아직 정의되지 않았기 때문에 별칭으로 참조하면 에러가 발생

FROM 및 JOIN: 데이터 소스 확인

WHERE: 행 필터링

GROUP BY: 그룹화

HAVING: 그룹 필터링

SELECT: 열 선택 및 별칭 정의

ORDER BY: 결과 정렬

윈도우 함수는 SELECT 또는 ORDER BY 절에서 사용되지만, SELECT 절 내부에서 별칭을 참조하려고 하면 아직 별칭이 생성되지 않은 상태이기 때문에 오류가 발생

3. 해결 방법

WITH SCORE_CTE AS (
    SELECT EMP_NO, SUM_SCORE
    FROM (
        SELECT
            EMP_NO,
            SUM(SCORE) AS SUM_SCORE,
            ROW_NUMBER() OVER (ORDER BY SUM(SCORE) DESC) AS RN
        FROM HR_GRADE
        WHERE YEAR = 2022
        GROUP BY EMP_NO
    ) TEMP
    WHERE RN = 1
)
SELECT SC.SUM_SCORE AS SCORE, SC.EMP_NO, HE.EMP_NAME, HE.POSITION, HE.EMAIL
FROM HR_EMPLOYEES HE
INNER JOIN SCORE_CTE SC
ON HE.EMP_NO = SC.EMP_NO;

4. 결론

윈도우절에서 SELECT alias를 바로 참조하지 말자

• 동일한 SELECT 절에서는 윈도우 함수가 별칭을 참조할 수 없으니, 서브쿼리나 CTE를 통해 별칭을 먼저 정의한 후에 참조해야함!
• 코드 가독성을 위해서는 CTE 활용하면 좋음

ORACLE

WITH SCORE_CTE AS (
        SELECT 
            EMP_NO, 
            SUM(SCORE) AS SUM_SCORE
            ROW_NUMBER 
        FROM HR_GRADE
        WHERE YEAR = 2022
        GROUP BY EMP_NO
        ORDER BY SUM_SCORE DESC
        FETCH FIRST 1 ROW ONLY
        )

SELECT SC.SUM_SCORE AS SCORE, SC.EMP_NO, HE.EMP_NAME, HE.POSITION, HE.EMAIL
FROM HR_EMPLOYEES HE
INNER JOIN SCORE_CTE SC
ON HE.EMP_NO = SC.EMP_NO;

MS SQL SERVER

WITH SCORE_CTE AS (
        SELECT TOP 1
            EMP_NO, 
            SUM(SCORE) AS SUM_SCORE
            ROW_NUMBER 
        FROM HR_GRADE
        WHERE YEAR = 2022
        GROUP BY EMP_NO
        ORDER BY SUM_SCORE DESC
        )

SELECT SC.SUM_SCORE AS SCORE, SC.EMP_NO, HE.EMP_NAME, HE.POSITION, HE.EMAIL
FROM HR_EMPLOYEES HE
INNER JOIN SCORE_CTE SC
ON HE.EMP_NO = SC.EMP_NO;