[SQL] model dimension by

문의가 와서 Oracle 10g에서 제공되기 시작한 model dimension by 에 대해 살펴보게 되었다.

DB2의 오라클 호환성이 어지간한 신택스는 다 지원된다고 생각하고 있었는데, 이런 엑셀스러운 신택스도 있는 것을 알게 된 것 같다.

 

처음보는 기능이기에 어떤 방식으로 처리되는지를 알고자 웹 검색을 해보니 “증분 누적?”(바로 전 값을 계속 누적 연산) 처리 로직정도로 이해가 되었다.

 

(참고) http://www.gurubee.net/lecture/2203

 

어떤 문서에는 common table (with 문을 통한 recursive 처리) 로의 변환이 되지 않게 쓰인 문서들도 있었는데 위의 참조된 웹 문서를 기준으로 db2 방식으로 변환을 해 보았다.

 

[예제1] 증분 누적

 

입력 값 및 결과

MM	AMT	결과 값
1	-2000	0
2	4000	4000
3	-5000	0
4	-2000	0
5	3000	3000
6	1500	4500
7	-250	4250
8	320	4570
9	-4000	570
10	10000	10570
11	-20000	0

 

변환 SQL

with    t1(mm,amt) as      (select * from               (values(1,-2000),(2,4000),(3,-5000),(4,-2000),(5,3000),(6,1500),(7,-250),(8,320),(9,-4000),(10,10000),(11,-20000)) t(mm,amt)),    tmp(c1,c2,c3) as     (select mm, greatest(0,amt), amt from t1 where mm=1         union all      select c1+1,             (select greatest(0,c2+amt) from t1 where mm=c1+1),             (select amt from t1 where mm=c1+1)      from tmp where c1 < (select max(mm) from t1)     ) select c1,c3 amt, c2 result from tmp ;

 

[예제2] 행 분할 누적

 

입력값

M	AMT
1	100
5	200
6	100
9	300

 

결과

M	결과
1	100
2	100
3	100
4	100
5	200
6	100
7	100
8	100
9	300
10	300
11	300
12	300

 

변환 SQL

with   t1(mm,amt) as      (select * from (values(1,100),(5,200),(6,100),(9,300)) t(mm,amt)) ,   tmp (c1,c2,c3) as    (select t1.mm, t1.amt, t1.amt from t1 where t1.mm=1     union all     select c1+1,            case when (select amt from t1 where c1+1 = mm) is null then c2                 else (select amt from t1 where c1+1 = mm) end ,            (select amt from t1 where c1+1 = mm)     from  tmp     where c1 < 12      ) select c1, c2 from tmp order by c1 ;

 

SQL의 복잡성에 따라서 어떤 경우에는 위처럼 변환되지 않는 경우가 있을 수도 있겠다.

 

개인적으로 SQL 표현을 간소하게 하여 SQL 문을 단순화하고 직관적으로 볼 수 있는 것을 좋아하기에 SQL에 대해서는 Oracle SQL 철학을 좋아한다. (+, connect by 등)

표준 SQL을 준수하는 DB2에서는 이런 부분을 장문의 SQL로 표현을 해야 되는 번거로움을 느끼지만 common table의 미학을 느껴볼 수 있는 계기가 된 것 같다.