Tag Archives: oracle

oracle sum() over()

출처 : http://blog.naver.com/hjc426/130037056133

SUM() OVER() ORACLE 내장 함수
SCOTT 계정의 EMP TABLE에서 테스트를 진행한다
다음의 QUERY 을 보면 이해가 쉽다.
———————————————————————————————-
SELECT ENAME,
DEPTNO,
SAL,
SUM(SAL) OVER (PARTITION BY DEPTNO)                         “부서별 급여합계”,
SUM(SAL) OVER (PARTITION BY DEPTNO ORDER BY SAL)    “부서별 급여오름차순 누적합”,
SUM(SAL) OVER (ORDER BY DEPTNO, SAL)                       “부서별 급여오름차순 누적합”,
SUM(SAL) OVER ()                                                        “전체 급여합”
FROM EMP;
———————————————————————————————-
위 QUERY  의 결과는 다음과 같다.

원래 SUM 함수는 GROUP 함수다.
따라서 위와 같은 부분에서 ENAME, DEPTNO, SAL 로 GROUP BY 을 해줘야 한다.
하지만 SUM() OVER()를 사용할 때는 GROUP BY 가 필요 없다

1.  SUM(COLUMN1) OVER(PARTITION BY COLUMN2) —>
이는 특정 컬럼2로 그룹핑 한 컬럼1의 SUM 값을 보여준다
위에서처럼 부서별 급여합이 된다. 10부서, 20부서 등의 급여 합을 보여준다

2. SUM(COLUMN1) OVER(PARTITION BY COLUMN2 ORDER BY COLUMN1) —>
이는 위에서보는 것처럼 컬럼2로 그룹핑한 데이터를 컬럼1로 오름차순 정렬을 하면서 SUM을 구한다.
이는 위의 예에서 보는 부서별 급여의 누적된 합을 구할 때 쓸 수 있다.
여기서 위와 같은 부분은 PARTITION BY 않하고 정렬을 통해서도 구현이 가능하다SUM(COLUMN1) OVER(ORDER BY COLUMN1, COLUMN1)
다음처럼 쓰면 PARTITION 한 것과 같은 결과를 얻을 수 있다.

3. SUM(COLUMN) OVER() —>
마지막으로 이는 컬럼의 전체 합을 리턴하게 된다..
GROUP BY 는 쓰는데 제약이 존재할 때가 있다.
난 개인적으로 그런 GROUP BY 를 싫어한다.
하지만 개발시에 그룹별로 보여줘야 할 부분은 너무 많다.
법인별, 부서별 등등의 어떤 내역 조회의 경우….이는 불가피할 것이다.

이런 TIP도 추가로 알면 개발할 때 유용할 것이다.

 

toad function debugging

function 만들다가 debugging을 하고 싶다면..중간 중간에 DBMS_OUTPUT.PUT_LINE()을 이용하여 디버깅 메시지를 찍어주면 되는데……그전에 DBMS_OUTPUT.ENABLE(10000) 과 같이 dbms_output을 활성화 시켜야 한다.

CREATE OR REPLACE FUNCTION LH_DEV.FN_GET_TEST
(
    arg_1      in varchar2,
    arg_2      in varchar2
) RETURN varchar2
IS
  ret_val      varchar2(300) default '';

  BEGIN
    DBMS_OUTPUT.ENABLE(10000);  -- buffer 사이즈 설정.
    DBMS_OUTPUT.PUT_LINE('debug 01');

    SELECT TO_CHAR(SYSDATE, 'YYYYMMDD')
      INTO ret_val
      FROM DUAL;

    DBMS_OUTPUT.PUT_LINE('debug 02 : ' || ret_val);

    RETURN ret_goods_nm;

  END;

위와같은 방법으로 작업하고, 실행해보면 toad에서 DBMS Output tab에서 디버깅 메시지를 확인할 수 있다.