WINDOW FUNCTION 의이해와활용방법 엑셈컨설팅본부 / DB 컨설팅팀정동기 개요 Window Function 이란행과행간의관계를쉽게정의할수있도록만든함수이다. 윈도우함수를활용하면복잡한 SQL 들을하나의 SQL 문장으로변경할수있으며반복적으로 ACCESS 하는비효율역시쉽게해결할수있다. 이번화이트페이퍼에서는 Window Function 중순위 RANK, ROW_NUMBER, DENSE_RANK), 집계 COUNT) 에관련된부분을다루게될것이다. Window Function Syntax Window Function 은 Over) 함수가기본적으로포함되어야한다. Over) 함수와함께 Window Function 이함께쓰인다. SELECT WINDOW_FUNCTION ARGUMENTS) OVER [PARTITION BY 칼럼 ]] [ORDER BY 절 ] [WINDOWING 절 ] ) FROM 테이블명 ; partition by: 순위를정하는기준칼럼 GROUP BY 와동일 ) order by: 기준칼럼 partition by) 의데이터중 order by 절에기술된칼럼으로순위 를정한다. 테스트관련 Script. drop table t3 purge ; drop table t4 purge ; create table t3 c1 number, c2 char1)) ; create table t4 c1 number, c2 char1), c3 number) ; Part 1 ORACLE 237
insert into t3 values 1, 'A') ; insert into t3 values 1, 'B') ; insert into t3 values 1, 'C') ; insert into t3 values 2, 'D') ; insert into t3 values 2, 'E') ; insert into t3 values 2, 'F') ; insert into t3 values 3, 'G') ; insert into t3 values 3, 'H') ; insert into t3 values 3, 'I') ; commit ; insert into t4 values 1, 'A', 5) ; insert into t4 values 1, 'A', 10) ; insert into t4 values 1, 'A', 10) ; insert into t4 values 1, 'A', 15) ; insert into t4 values 1, 'B', 20) ; insert into t4 values 1, 'B', 25) ; insert into t4 values 1, 'C', 30) ; insert into t4 values 2, 'D', 1) ; insert into t4 values 2, 'D', 2) ; insert into t4 values 2, 'E', 3) ; insert into t4 values 2, 'F', 5) ; insert into t4 values 2, 'E', 4) ; insert into t4 values 3, 'G', 1) ; insert into t4 values 3, 'G', 2) ; insert into t4 values 3, 'I', 1) ; commit ; COUNT) SELECT C1,C2,C3 AND C2 = 'A' ; C1 C2 C3 ----- -- ---------- 1 A 5 238 2013 기술백서 White Paper
1 A 10 1 A 10 SELECT C1, C2, COUNT*) WHERE C2 = 'A' GROUP BY C1, C2 ORDER BY C1, C2 ; C1 C2 COUNT*) -------- -- ---------- 1 A 4 SELECT C1, C2, C3, COUNT*) AND C2 = 'A' GROUP BY C1, C2, C3 ORDER BY C1, C2, C3 ; COUNT*) ----- -- ---------- ---------- 1 A 5 1 1 A 10 2 1 < 최종추출데이터 > select c1,c2,c3 from T4 AND C2 = 'A' ; C3_CNT TOTAL_CNT --------- -- ---------- ---------- ---------- 1 A 5 1 4 1 4 Part 1 ORACLE 239
위의 < 추출데이터 > 의의도는 c1=1 and c2 = 'A' 인데이터를모두추출하면서 c1, c2 칼럼으로그룹핑된건수와 c1, c2, c3 로그룹핑된건수를같이보여주려는것이다. 단순하게아래 SQL[1] 과같이 T4 테이블의데이터를중복액세스 COUNT 수행하기위해 ) 를수행하여처리할수도있다. 물론, < 추출데이터 > 의데이터를보여줄수는있지만 SQL 의성능은손해를볼수밖에없다. SQL[1]. 성능문제가발생하는 SQL 작성유형 SELECT T4.C1, T4.C2, T4.C3, T5.C3_CNT, T6.TOTAL_CNT, SELECT C1, C2, C3, COUNTC3) AS C3_CNT AND C2 = 'A' GROUP BY C1, C2, C3 ) T5, SELECT C1, C2, COUNTC3) AS TOTAL_CNT AND C2 = 'A' GROUP BY C1, C2 ) T6 WHERE T4.C1 = 1 AND T4.C2 = 'A' AND T4.C1 = T5.C1 AND T4.C2 = T5.C2 AND T4.C3 = T5.C3 AND T4.C1 = T6.C1 AND T4.C2 = T6.C2 ORDER BY C1,C2,C3 ; 240 2013 기술백서 White Paper
C3_CNT TOTAL_CNT -------- -- ---------- ---------- ---------- 1 A 5 1 4 1 4 아래 SQL[2] 는 T4 테이블의데이터를중복액세스하지않기위해 Oracle 9i 이후버전에서는 SELECT AS WITH CLAUSE 을이용하여 T4 테이블의데이터를중복액세스하지않고 1 회수 행만으로의도한데이터를추출할수있다 SQL[2]. 성능문제가발생하는 SQL 작성유형 WITH T1 AS SELECT C1,C2,C3 AND C2 = 'A' ) SELECT T4.C1, T4.C2, T4.C3, T5.C3_CNT, T6.TOTAL_CNT FROM T1 T4, SELECT C1, C2, C3, COUNTC3) AS C3_CNT FROM T1 GROUP BY C1, C2, C3 ) T5, SELECT C1, C2, COUNTC3) AS TOTAL_CNT FROM T1 GROUP BY C1, C2 ) T6 WHERE T4.C1 = T5.C1 AND T4.C2 = T5.C2 AND T4.C3 = T5.C3 Part 1 ORACLE 241
AND T4.C1 = T6.C1 AND T4.C2 = T6.C2 ORDER BY C1,C2,C3 ; C3_CNT TOTAL_CNT --------- -- ---------- ---------- ---------- 1 A 5 1 4 1 4 그러나, 문제가있다. WITH CLAUSE 을이용해 T4 테이블의데이터를 1 회만읽는다는것은 C1 = 1 AND C2 = 'A' 조건에해당하는모든데이터를 GLOBAL TEMPORARY TABLE 에저장 해두고재사용하겠다는의미이다. 그런데, 만약 C1 = 1 AND C2 = 'A' 조건에해당하는데이터많고, OLTP 환경에서빈번하게조회되는 SQL 문이라면 GLOBAL TEMPORARY TABLE 을생성하여처리하는작업자체가부담이될수있다. 또한, 해당 SQL 이페이징시사용되는쿼리문이라면부분범위가되지않는또다른문제점이발생할수있다. 위의 SQL 에서 T4 테이블의데이터를불필요하게중복액세스처리하지않고, SQL 의성능까지 고려한 SQL 작성법은아래와같이 COUNT*) OVER ) 를활용하는것이다. SQL[1]. SQL[2] 의성능문제를해결할수있는 SQL 작성유형 SELECT C1, C2, C3, COUNT*) OVER PARTITION BY C1,C2,C3) AS C3_CNT, COUNT*) OVER PARTITION BY C1,C2) AS TOTAL_CNT WHERE C2 = 'A' ORDER BY C1,C2,C3 ; C3_CNT TOTAL_CNT -------- -- ---------- ---------- ---------- 1 A 5 1 4 242 2013 기술백서 White Paper
1 4. RANK), ROW_NUMNER), DENSE_RANK) select t3.c1, t3.c2, t4.c3 from t3, t4 and t3.c2 in 'A','E','G') order by 1,2,3 ; -------- -- ---------- 1 A 5 1 A 10 1 A 10 2 E 3 3 G 1 3 G 2 < 최종추출데이터 > -------- -- ---------- 위와같은데이터를추출하고자할때 C2 의값이 'A','E','G' 인데이터중 C3 가가장큰값을추출하여야 < 추출데이터 > 를추출할수있다. Oracle 10g 이전에는아래와같은패턴으로 SQL 을작성하여데이터를추출하였는데동일테이블을 2 번반복하여처리하였었다. Part 1 ORACLE 243
SQL[1] select t3.c1, t3.c2, t4.c3 from t3, t4, select t3.c1, t3.c2, maxt4.c3) max_c3 from t3, t4 and t3.c2 in 'A','E','G') group by t3.c1, t3.c2 ) t5 and t4.c3 = t5.max_c3 and t3.c2 in 'A','E','G') ; -------- -- ---------- 3 G 2 Oracle 10g 이후에는위에서본 < 추출데이터 > 를추출하기위한방법으로신규로추가된 Analytic Function 을사용하면동일테이블을중복처리하지않고 1 회만처리가되도록 SQL 을작성할수있다. 이때사용되는 FUNCTION 이 RANK, DENSE_RANK, ROW_NUMBER 이다. SQL[1]. RANK 활용 select t3.c1, t3.c2, t4.c3 from t3, select c1, c2, 244 2013 기술백서 White Paper
c3, rank) over partition by c1,c2 order by c3 desc) as rank_check from t4 ) t4 and t3.c2 in 'A','E','G') and t4.rank_check = 1 ; -------- -- ---------- 3 G 2 SQL[2]. ROW_NUMBER 활용 select c1, c2, c3 from select t3.c1, t3.c2, t4.c3, row_number) over partition by t3.c1, t3.c2 order by t4.c3 desc ) as rnum from t3, t4 and t3.c2 in 'A','E','G') ) where rnum <= 1 ; ------ -- ---------- 3 G 2 Part 1 ORACLE 245
SQL[3]. DENSE_RANK 활용 select t3.c1, t3.c2, t4.c3 from t3, select c1, c2, c3, dense_rank) over partition by c1,c2 order by c3 desc) as rank_check from t4 ) t4 and t3.c2 in 'A','E','G') and t4.rank_check = 1 ; ------ -- ---------- 3 G 2 SQL[1], SQL[2], SQL[3] 은 < 추출데이터 > 에부합되는데이터를추출할수있다. 여기에서사용된 Analytic Function 은 RANK, DENSE_RANK, ROW_NUMBER 이다. RANK, DENSE_RANK, ROW_NUMBER Analytic Function 은데이터의순위를정하는분석함수인데정확한쓰임새를알필요가있다 select c1, c2, c3, rank) overpartition by c1, c2 order by c3 desc) as rank, dense_rank) overpartition by c1, c2 order by c3 desc) as dense_rank, row_number) overpartition by c1, c2 order by c3 desc) as row_number from t4 where c2 = 'A' ; 246 2013 기술백서 White Paper
RANK DENSE_RANK ROW_NUMBER ------- -- ---------- ---------- ---------- ---------- 1 1 1 1 A 10 2 2 2 1 A 10 2 2 3 1 A 5 4 3 4 RANK: 같은순위 RANK-2) 에같은값이 2 개일때다음값의순위는 RANK-4 가된 다. DENSE_RANK: 같은순위 RANK-2) 에같은값이 2 개일때다음값의순위는 RANK-3 가된다. ROW_NUMBER: 같은순위더라도순위가매겨진다. 결론 지금까지몇가지 Window Function 활용방법에대해서간단히알아보았다. 비록간단한예제이지만해당 Window Function 을잘활용한다면실제업무 SQL 작성시에좀더효율적인 SQL 을작성할수있을것이다. Part 1 ORACLE 247