반갑습니다.
오라클의 분석함수를 학습하다, 질문이 있어서 이렇게 문의 드립니다.
avg를 분석함수로 사용할때 over() 에서 사용하는 order by의 역활이 궁금합니다.
인터넷에서 확인한 바로는 "ORDER BY 절은 Partition by 로 정의된 WINDOW 내에서의 행들의 정렬 순서를 정의 한다." 인데 실습을 해보니 전혀 avg()와 사용해보니 이상합니다.
아래 두개의 쿼리를 보면..
select empno, ename, sal, deptno, job, avg(sal) over(order by deptno) avg_sal from emp ;
이것의 결과는 아래와 같습니다.
EMPNO ENAME SAL DEPTNO JOB AVG_SAL ---------- ---------- ---------- ---------- --------- ---------- 7782 CLARK 2450 10 MANAGER 2917 7839 KING 5000 10 PRESIDENT 2917 7934 MILLER 1300 10 CLERK 2917 7566 JONES 2975 20 MANAGER 2453 7902 FORD 3000 20 ANALYST 2453 7876 ADAMS 1100 20 CLERK 2453 7369 SMITH 800 20 CLERK 2453 7788 SCOTT 3000 20 ANALYST 2453 7521 WARD 1250 30 SALESMAN 2073 7844 TURNER 1500 30 SALESMAN 2073 7499 ALLEN 1600 30 SALESMAN 2073 7900 JAMES 950 30 CLERK 2073 7698 BLAKE 2850 30 MANAGER 2073 7654 MARTIN 1250 30 SALESMAN 2073
마지막 avg_sal의 값이 도대체 무었인지 모르겠습니다.
deptno 가 10 인것은 avg값이 맞는데
deptno 가 20인것의 값인 2453은 무슨값인지 모르겠습니다.
deptno 가 20인것의 avg값은 2175인데..
원래 쿼리의 의도데로라면 전체 평균값인 2073이 모두 나오고, 정렬만 deptno 로 되어야 할거 같은데..
혹 sum()에서 order by를 사용하면 누적값이 되는것을 확인했는데,
avg()에서도 order by 를 사용하면 뭐 그 비슷한것이 있을까 찾와봐도 그런건 없는듯합니다.
참고로
select empno, ename, sal, deptno, job, round(avg(sal) over(partition by deptno) ) avg_sal from emp ;
의 결과는 아래와 같습니다.
EMPNO ENAME SAL DEPTNO JOB AVG_SAL ---------- ---------- ---------- ---------- --------- ---------- 7782 CLARK 2450 10 MANAGER 2917 7839 KING 5000 10 PRESIDENT 2917 7934 MILLER 1300 10 CLERK 2917 7566 JONES 2975 20 MANAGER 2175 7902 FORD 3000 20 ANALYST 2175 7876 ADAMS 1100 20 CLERK 2175 7369 SMITH 800 20 CLERK 2175 7788 SCOTT 3000 20 ANALYST 2175 7521 WARD 1250 30 SALESMAN 1567 7844 TURNER 1500 30 SALESMAN 1567 7499 ALLEN 1600 30 SALESMAN 1567 7900 JAMES 950 30 CLERK 1567 7698 BLAKE 2850 30 MANAGER 1567 7654 MARTIN 1250 30 SALESMAN 1567
도대체 어떻게 되는지...
선배님들의 도움 부탁드립니다.
그럼 즐거운 주말되시길...
ORDER BY 구문은 윈도우절을 동반하게 됩니다.
윈도우절 생략시 기본값은
RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW 입니다.
정렬기준의 범위를 대상으로 맨 처음부터 현재값까지 입니다.
정렬키 자체가 지금 중복 값이므로 중복값은 한거번에 처리됩니다.
RANGE 가 아닌 ROWS 구문으로 변경하면 값을 기준으로 한 범위가 아닌
행을 기준으로 한 범위가 되어 한건 한건 단위로 처리됩니다.
RANGE 와 ROWS 에 따라 동작방식이 조금 다릅니다.
RANGE 사용시에는 중복값에 대해 주의해야 합니다.
정렬은 될 수 있으면 중복값 외에 유일값을 포함시켜 주는게 좋습니다.(예 ORDER BY deptno, empno)
윈도우 절에 대한 설명이 없을 뿐 Order By 자체에 대한 설명은 틀린거는 없네요.
전체 평균값인 2073 이 모두 나오도록 하고 싶다면?
AVG(sal) OVER()
전체 평균값인 2073 이 모두 나오면서 deptno 로 정렬하고 싶다면?
AVG(sal) OVER(ORDER BY deptno, empno ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)