이 문제에서 사원별 SCORE(상반기 score+하반기 score)를 기준으로 랭킹을 매겨야하는데, 윈도우 함수를 사용하며 어려움이 있었다.
1차 시도
WITH SCORES AS(
SELECT
DISTINCT EMP_NO,
SUM(SCORE) OVER(PARTITION BY EMP_NO) AS SCORE,
RANK() OVER(PARTITION BY EMP_NO ORDER BY SUM(SCORE)) AS R
FROM HR_GRADE
)
SELECT
SCORE,
EMP_NO,
EMP_NAME,
POSITION,
EMAIL
FROM HR_EMPLOYEES
LEFT JOIN SCORES
USING(EMP_NO)
WHERE R=1;
처음엔 이렇게 CTE로 SCORE와 RANKING을 구해서 1위 데이터만 뽑아보려고 했는데, 오류가 났다!
GROUP BY를 사용하지 않고 OVER 내에서 집계함수인 SUM(SCORE)를 사용해서 난 오류였다.
결론적으론 RANK()부분이 문제였는데,
나의 의도는 PARTITION BY로 EMP_NO 기준으로 그룹화하고, 그룹별로 SUM(SCORE)의 값으로 정렬해 랭크를 매기는 것이엇따.
근데 저렇게 하면, EMP_NO으로 그룹화하고 그룹 내에서만 정렬이 이뤄진다! 근데 그룹내에서 SUM(SCORE)은 같은 값이니 의미가 없는 것임. (차피 실행도 안되고)
그렇기에 저걸 제대로 수정한다면,
WITH SCORES AS(
SELECT
EMP_NO,
SUM(SCORE) AS SCORE,
RANK() OVER(ORDER BY SUM(SCORE) DESC) AS R
FROM HR_GRADE
GROUP BY EMP_NO
)
SELECT
SCORE,
EMP_NO,
EMP_NAME,
POSITION,
EMAIL
FROM HR_EMPLOYEES
LEFT JOIN SCORES
USING(EMP_NO)
WHERE R=1;이렇게 전체에서 GROUP BY를 사용하는 것으로 수정하면 깔끔해진다.
여기서 또 궁금했던 건 그러면 OVER안의 SUM은 GROUP BY의 영향을 받는건가?였는데 그렇다고 한다.
SUM(SCORE) OVER(PARTITION BY EMP_NO)는 각 행에서 반복되지만,
SUM(SCORE) + GROUP BY는 그룹별로 한 번만 계산된다!
by. 챗지피티
결국 중요한 건 집계함수를 사용하기 위해선
1) GROUP BY를 쓰거나,
2) 그 집계함수를 위해 OVER를 쓰는 수밖에!
역시나 윈도우 함수는 복잡하다.
내가 제대로 이해하지 못하고 쓰고 있던게 크긴 하다만,,,ㅎㅎ
+) 제대로 적어두자면, 내가 첫번째에 작성한 쿼리는 EMP_NO으로 그룹화를 하고 그 그룹 내에서 정렬을 하고자 하는거였고(의도와 다름), 두번째 제대로 된 쿼리가 EMP_NO으로 그룹화하고 그룹별 SUM(SCORE) 값으로 각 그룹을 ORDER해 랭킹을 매기는 것이다.
