책의 예제를 따라 연습해 보다가 이해가 안가는게 있어 질문드립니다.
WITH TEMP AS(
SELECT '10' AS DEPTNO, 'KING' AS ENAME, 5000 AS SAL FROM DUAL
UNION ALL
SELECT '10' AS DEPTNO, 'CLARK' AS ENAME, 2450 AS SAL FROM DUAL
UNION ALL
SELECT '10' AS DEPTNO, 'MILLER' AS ENAME, 1300 AS SAL FROM DUAL
UNION ALL
SELECT '20' AS DEPTNO, 'SCOTT' AS ENAME, 3000 AS SAL FROM DUAL
UNION ALL
SELECT '20' AS DEPTNO, 'FORD' AS ENAME, 3000 AS SAL FROM DUAL
UNION ALL
SELECT '20' AS DEPTNO, 'JONES' AS ENAME, 2975 AS SAL FROM DUAL
UNION ALL
SELECT '20' AS DEPTNO, 'ADAMS' AS ENAME, 1100 AS SAL FROM DUAL
UNION ALL
SELECT '20' AS DEPTNO, 'SMITH' AS ENAME, 800 AS SAL FROM DUAL
UNION ALL
SELECT '30' AS DEPTNO, 'WARD' AS ENAME, 1250 AS SAL FROM DUAL
UNION ALL
SELECT '30' AS DEPTNO, 'ALLEN' AS ENAME, 1600 AS SAL FROM DUAL
UNION ALL
SELECT '30' AS DEPTNO, 'MARTIN' AS ENAME, 1250 AS SAL FROM DUAL
UNION ALL
SELECT '30' AS DEPTNO, 'TURNER' AS ENAME, 1500 AS SAL FROM DUAL
UNION ALL
SELECT '30' AS DEPTNO, 'BLAKE' AS ENAME, 2850 AS SAL FROM DUAL
UNION ALL
SELECT '30' AS DEPTNO, 'JAMES' AS ENAME, 950 AS SAL FROM DUAL
)
1) SELECT DEPTNO, ENAME, SAL, LAST_VALUE(ENAME) OVER(PARTITION BY DEPTNO ORDER BY SAL ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)AS AA FROM TEMP;
2) SELECT DEPTNO, ENAME, SAL, LAST_VALUE(ENAME) OVER(PARTITION BY DEPTNO ORDER BY SAL RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)AS AA FROM TEMP;
WINDOWING절의 ROWS와 RANGE차이를 구별하기 위해 다음과 같은 쿼리를 작성했고 둘 다 정상적으로 동작하는걸 확인했습니다. 그런데 이 다음으로
3) SELECT DEPTNO, ENAME, SAL, LAST_VALUE(ENAME) OVER(PARTITION BY DEPTNO ORDER BY SAL, ENAME ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)AS AA FROM TEMP;
4) SELECT DEPTNO, ENAME, SAL, LAST_VALUE(ENAME) OVER(PARTITION BY DEPTNO ORDER BY SAL, ENAME RANGE BETWEEN 1 PRECEDING AND CURRENT ROW)AS AA FROM TEMP;
위와 같이 ORDER BY 조건에 ENAME을 추가하여 실행하였는데
결과적으로 3은 정상동작, 4는 에러가 발생했습니다.
ORA-30486: invalid window aggregation group in the window specification
제 생각에는 4역시 정상적으로 동작해야 할 것 같은데 에러가 나는 이유를 이해할 수가 없습니다.
ROWS와 RANGE가 물리적 행 수/ 논리적 값의 범위로 구분된다는건 알고 있는데 이 차이 때문인 것 같아 적용을 해보려 해도 이해가 잘 안가네요.
고수님들 답변 부탁드리겠습니다. ㅠ