[SQL] 그룹간 rownum 과 누적합 구하기(MONOTONIC 대안)

[백승민]

- 제목 : 그룹간 rownum 과 누적합 구하기(MONOTONIC 대안)

- 오라클의 분석함수인 ROW_NUMBER 나 RANK를 직접 구현 방식

* MONOTONIC의 경우는 관측치 셑의 있는 그대로에 대하여 메모리상에서 숫자를 지정하는듯 하네요.

기준변수들에 대하여 순서를 고려하거나, 실행시 이상한경우(번호가 관측치 숫자 다음번부터 다시 시작가능)에는 문제가 있는듯 합니다.

아래의 경우는 self조인을 하여 Full 조인을 유도후에 카운트와 Sum을 하게 되는 방식으로 대용량 데이터에는 적합하지는 않을듯 하지만.. 알아두면 유용할듯..

궁금한경우 select문장에 * 으로, ORDER BY 구문을 제거하여 모든 값을 보시면 아실겁니다.;

data back;
input deptno EMPNO SAL;
cards;
10 7782 2450
10 7839 5000
10 7934 1300
20 7369  800
20 7566 2975
20 7788 3000
20 7876 1100
20 7902 3000
30 7499 1600
30 7521 1250
30 7654 1250
30 7698 2850
30 7844 1500
30 7900  950
;

* EMPNO에 따르는 전체 관측치 순서를 부여하고, 누적합 구하기;
proc sql;
  select count(*) as no,
         t1.empno,
         t1.sal,
         sum(t2.sal) as sum_cnt
  from   back t1, back t2
  where  t1.empno >= t2.empno
  group by t1.empno,
           t1.sal
  order by calculated no;
quit;

* DEPTNO에 따르는 그룹간 관측치 순서를 부여하고, 그룹간 누적합 구하기;

proc sql;
  select t1.deptno,
         t1.empno,
         t1.sal,
         count(*) as no,
         sum(t2.sal) as sum_cnt
  from   back t1, back t2
  where  t1.empno >= t2.empno
  and    t1.deptno   = t2.deptno
  group by t1.deptno,
           t1.empno,
           t1.sal
  order by t1.deptno,
           calculated no;
quit;

[플라스틱머니]

오라클 lag()함수를 구현하기 위해서는 monotonic()과 자기 조인을 이용하면 되는건지요? 승민씨 정말 감사합니다. 이거 원래 회사였으면 술한잔 사드려야 하는 件 인데요 *^^* 이렇게 유용한 SQL 시리즈 계속 연재 부탁드립니다. ps: 원래 monotonic 필요했던 이유는 datestep 중 transpose를 sql로 구현하려는 의도였답니다. *^^* 전혀 모르던 monotonic을 알게 되고 좋네요 ~ 이것저것 오라클 함수를 SAS sql로 구현하는 것에 흥미를 갖게 되네요 ㅎㅎ