WINDOW FUNCTION의 이해와 활용방법_Wh oracle

WINDOW FUNCTION의 이해와 활용방법_Wh oracle

• 1.
  Part 1 ORACLE│237 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 char(1)) ; create table t4 (c1 number, c2 char(1), c3 number) ;
• 2.
  238│2013 기술백서 WhitePaper 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 FROM T4 WHERE C1 = 1 AND C2 = 'A' ; C1 C2 C3 ----- -- ---------- 1 A 5
• 3.
  Part 1 ORACLE│239 1 A 15 1 A 10 1 A 10 SELECT C1, C2, COUNT(*) FROM T4 WHERE C2 = 'A' GROUP BY C1, C2 ORDER BY C1, C2 ; C1 C2 COUNT(*) -------- -- ---------- 1 A 4 SELECT C1, C2, C3, COUNT(*) FROM T4 WHERE C1 = 1 AND C2 = 'A' GROUP BY C1, C2, C3 ORDER BY C1, C2, C3 ; C1 C2 C3 COUNT(*) ----- -- ---------- ---------- 1 A 5 1 1 A 10 2 1 A 15 1 < 최종 추출 데이터 > select c1,c2,c3 from T4 WHERE C1 = 1 AND C2 = 'A' ; C1 C2 C3 C3_CNT TOTAL_CNT --------- -- ---------- ---------- ---------- 1 A 5 1 4 1 A 10 2 4 1 A 10 2 4 1 A 15 1 4
• 4.
  240│2013 기술백서 WhitePaper 위의 < 추출 데이터 >의 의도는 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 FROM T4, ( SELECT C1, C2, C3, COUNT(C3) AS C3_CNT FROM T4 WHERE C1 = 1 AND C2 = 'A' GROUP BY C1, C2, C3 ) T5, ( SELECT C1, C2, COUNT(C3) AS TOTAL_CNT FROM T4 WHERE C1 = 1 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 ;
• 5.
  Part 1 ORACLE│241 C1 C2 C3 C3_CNT TOTAL_CNT -------- -- ---------- ---------- ---------- 1 A 5 1 4 1 A 10 2 4 1 A 10 2 4 1 A 15 1 4 아래 SQL[2]는 T4 테이블의 데이터를 중복 액세스하지 않기 위해 Oracle 9i 이후 버전에서는 SELECT AS WITH CLAUSE 을 이용하여 T4 테이블의 데이터를 중복 액세스 하지 않고 1 회 수 행만으로 의도한 데이터를 추출할 수 있다 SQL[2]. 성능 문제가 발생하는 SQL 작성유형 WITH T1 AS ( SELECT C1,C2,C3 FROM T4 WHERE C1 = 1 AND C2 = 'A' ) SELECT T4.C1, T4.C2, T4.C3, T5.C3_CNT, T6.TOTAL_CNT FROM T1 T4, ( SELECT C1, C2, C3, COUNT(C3) AS C3_CNT FROM T1 GROUP BY C1, C2, C3 ) T5, ( SELECT C1, C2, COUNT(C3) 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
• 6.
  242│2013 기술백서 WhitePaper AND T4.C1 = T6.C1 AND T4.C2 = T6.C2 ORDER BY C1,C2,C3 ; C1 C2 C3 C3_CNT TOTAL_CNT --------- -- ---------- ---------- ---------- 1 A 5 1 4 1 A 10 2 4 1 A 10 2 4 1 A 15 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 FROM T4 WHERE C2 = 'A' ORDER BY C1,C2,C3 ; C1 C2 C3 C3_CNT TOTAL_CNT -------- -- ---------- ---------- ---------- 1 A 5 1 4
• 7.
  Part 1 ORACLE│243 1 A 10 2 4 1 A 10 2 4 1 A 15 1 4. RANK(), ROW_NUMNER(), DENSE_RANK() select t3.c1, t3.c2, t4.c3 from t3, t4 where t3.c1 = t4.c1 and t3.c2 = t4.c2 and t3.c2 in ('A','E','G') order by 1,2,3 ; C1 C2 C3 -------- -- ---------- 1 A 5 1 A 10 1 A 10 1 A 15 2 E 3 2 E 4 3 G 1 3 G 2 < 최종 추출 데이터 > C1 C2 C3 -------- -- ---------- 1 A 15 2 E 4 위와 같은 데이터를 추출 하고 자 할 때 C2 의 값이 'A','E','G' 인 데이터 중 C3 가 가장 큰 값을 추출하여야 < 추출 데이터 > 를 추출할 수 있다. Oracle 10g 이전에는 아래와 같은 패턴으로 SQL 을 작성하여 데이터를 추출 하였는데 동일 테이블을 2 번 반복하여 처리하였었다.
• 8.
  244│2013 기술백서 WhitePaper SQL[1] select t3.c1, t3.c2, t4.c3 from t3, t4, ( select t3.c1, t3.c2, max(t4.c3) max_c3 from t3, t4 where t3.c1 = t4.c1 and t3.c2 = t4.c2 and t3.c2 in ('A','E','G') group by t3.c1, t3.c2 ) t5 where t3.c1 = t4.c1 and t3.c2 = t4.c2 and t4.c3 = t5.max_c3 and t3.c2 in ('A','E','G') ; C1 C2 C3 -------- -- ---------- 1 A 15 2 E 4 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,
• 9.
  Part 1 ORACLE│245 c3, rank() over (partition by c1,c2 order by c3 desc) as rank_check from t4 ) t4 where t3.c1 = t4.c1 and t3.c2 = t4.c2 and t3.c2 in ('A','E','G') and t4.rank_check = 1 ; C1 C2 C3 -------- -- ---------- 1 A 15 2 E 4 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 where t3.c1 = t4.c1 and t3.c2 = t4.c2 and t3.c2 in ('A','E','G') ) where rnum <= 1 ; C1 C2 C3 ------ -- ---------- 1 A 15 2 E 4 3 G 2
• 10.
  246│2013 기술백서 WhitePaper 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 where t3.c1 = t4.c1 and t3.c2 = t4.c2 and t3.c2 in ('A','E','G') and t4.rank_check = 1 ; C1 C2 C3 ------ -- ---------- 1 A 15 2 E 4 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() over(partition by c1, c2 order by c3 desc) as rank, dense_rank() over(partition by c1, c2 order by c3 desc) as dense_rank, row_number() over(partition by c1, c2 order by c3 desc) as row_number from t4 where c2 = 'A' ;
• 11.
  Part 1 ORACLE│247 C1 C2 C3 RANK DENSE_RANK ROW_NUMBER ------- -- ---------- ---------- ---------- ---------- 1 A 15 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 을 작성 할 수 있을 것이다.