오라클 분석함수 avg 에대해서.. 0 3 2,008

by 우태열 [SQL Query] 분석함수 avg [2017.07.15 19:55:42]


반갑습니다.

오라클의 분석함수를 학습하다, 질문이 있어서 이렇게 문의 드립니다.

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
	

도대체 어떻게 되는지...

선배님들의 도움 부탁드립니다.

그럼 즐거운 주말되시길...

by 모래가흙흙 [2017.07.16 00:54:27]

Partition by 조건이 없으므로 전체row가 대상이고 , deptno값이 20의값은  order by구문으로 정렬했을때 20까지의 누적값이므로  

(deptno가 10인값 3건이랑 + 20인값 5건)/8


by 우태열 [2017.07.16 09:11:47]

네 감사합니다.

그러니까...

(2450 + 5000 + 1300 + 2975 + 3000 + 1100 + 800 + 3000 ) / 8 = 2453 이 계산되는것을 확인했고,

deptno 30에 대해서도 마찬가지로 계산하면되는군요.

결국은  분석함수의  over()에 사용된는 order by의 설명이 이상한거 같습니다.

다시한번 감사드립니다.


by 마농 [2017.07.17 08:35:11]

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)

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입