목 차 들어가기 ………… 2 작동원리 ………… 4 구문설명 ………… 6 FUNCTIONS …………11 예제 ………… 2

Presentation on theme: "목 차 들어가기 ………… 2 작동원리 ………… 4 구문설명 ………… 6 FUNCTIONS …………11 예제 ………… 2"— Presentation transcript:

1 목 차 들어가기 ………… 2 작동원리 ………… 4 구문설명 ………… 6 FUNCTIONS …………11 예제 ………… 2
목 차 목 차 들어가기 ………… 2 작동원리 ………… 4 구문설명 ………… 6 FUNCTIONS …………11 예제 ………… 2 5.1 Ranking ………… 12 5.2 Windowing ………… 23 5.3 Reporting ………… 27 5.4 LAG/LEAD ………… 29 5.5 Statistics ………… 30

2 1. 들어가기 1. 들어가기. 과거에 우리는 순위구하기, 누적치 구하기, 그룹별 합계,평균,비중구하기등 분석작업을 계산할때 어떻게 해왔습니까? Tool의 기능을 이용하거나, Data를 Select한후에 Loop문에서 첨자를 이용하여 각Row에 대하여 필요한 계산을 한후에 Display해왔습니다. 이유는 Sql이 집합단위로 Data를 Query를 하기 때문에 현재Row에 대해서 다른 Row들을 참조할수가 없습니다. 만약에 구현을 한다면 Self Join과 Inline View등을 이용해야 했는데 상당히 복잡한 Sql문장이 만들어 졌습니다. Oracle8.1.6이상에서 Sql한문장안에서 이러한 계산을 쉽게 할수 있게 되었습니다. Analytic Function이라는 걸 제공하기 시작했기 때문인데 Oracle8.1.6이 출시된지도 꽤오래되고 사용하는 업체가 많음에도 활용가치가 적은것 같아 나름대로 매뉴얼(Oracle8i Data Warehousing Guide)을 참조하여 정리해 보았습니다. 다른 DBMS업체에서는 아직 구현되지 않은걸로 알고 있으나 머지않아 ANSI에서 SQL표준에 추가하기 위한 검토작업을 한다고 합니다. 이자료를 읽고 조금이나마 업무에 도움이 되었으면 합니다.

3 예를 들어보겠습니다. 월별 매출 테이블이 있을때 월별누계를 구하려면 어떻게 해야 할까요.
1. 들어가기 예를 들어보겠습니다. 월별 매출 테이블이 있을때 월별누계를 구하려면 어떻게 해야 할까요. 월 매출금액 월 매출금액 매출누계 1월 200 1월 200 200 2월 150 2월 150 350 3월 300 3월 300 650 4월 120 4월 120 770 5월 230 5월 230 1000 Analytic Function을 사용하지 않을때 Select A.월, A.매출금액, Sum(B.매출금액) 매출누계 from test1 A, test1 B where A.월 >= B.월 group by A.월, A.매출금액 Analytic Function을 사용할때 Select 월, 매출금액, Sum(매출금액) Over ( Order by 월 ) 매출누계 from test1 A Analytic Function을 사용하지 않을때에는 기타 여러가지 방법이 있으나 테이블을 두번 Access해야하는 번거러움이 있는 반면에 Analytic Functio을 사용할때에는 테이블을 한번만 Access하여 필요한 모든 계산을 마치고 결과를 Return합니다. 이렇듯 Analytic을 이용하면 편리할때가 많겠죠……

4 2. 작동원리 2. 작동원리 Analytic Function은 마지막 Order By 절이 수행되기 바로 전에 실행이 됩니다.
즉, 모든 조인, Where절, Group By 절, Having절 들이 완벽하게 수행되고 난 뒤에야 Analytic Function이 수행된다는 것입니다. 따라서 Analytic Function은 Select List나 Order By 절에서만 사용할 수 있습니다. Analytic Function은 보통 compute cumulative, moving, centered,reporting aggregates에 사용된다. Analytic Function의 기본적으로 계산되는 Row의 그룹범위는 Partition by절에 의해서 나누어지고 Data를 저장할때 사용되는 Partition과는 구별되어야 한다. Analytic Function에서 Window라는 것은 ‘current row’가 계산을 수행하기위해서 사용되는 Rows의 범위를 결정한다. 따라서 Window는 Partiton에 포함된다. Window의 크기는 rows의 물리적인 숫자와 시간 같은 논리적인 범위로 사용될 수 있다. SQL Processing Order는 다음 그림과 같다. Joins, WHERE, GROUP BY, And HAVING clauses Partitions created; Analytic functions applied to each row in each partition Final ORDER BY

5 Result Set, Partition, Window, Current Row의 관계.
2. 작동원리 Result Set, Partition, Window, Current Row의 관계. Select 상품, 월, 매출금액, Sum(매출금액) Over ( Partition by 상품 Order by 월 RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) 매출누계 from test1 A 상품 월 매출금액 매출누계 상품A 1월 200 200 Window 상품A 2월 150 350 상품A 3월 300 650 Result Set Query된 전체List Current Row 상품A 4월 120 770 Partition 상품별 Group 상품A 5월 230 상품B 1월 200 Current Row가 계산을 하기위해서 이동할 때마다 Window의 범위도 변하게 된다. 상품B 2월 150 상품B 3월 300 상품B 4월 120 상품B 5월 230

6 3. 구문설명 3. 구문설명 analytic_function::= analytic_clause::=
analytic_function : analytic function의 이름이 사용되고 뒤에 리스트를 설명한다. Arguments : 0~3개 까지의 Argument를 가질수 있다. OVER : Analytic_clause FROM, WHERE, GROUP BY, HAVING절이 완료된후에 실행되고, Select List나 ORDER BY절에 사용할수 있다.

7 3. 구문설명 Query_partition_clause::= PARTITION BY : 하나이상의 value_expr에 의해서 Result Set이 Group들로 구분된다. 생략된다면 Result Set의 전체Row가 한 개의 Group으로 인식된다. 같은 Query안에 여러 개의 Analytic Function이 사용되면 각각에대해서 PARTITION BY절을 사용할수 있다. ( Note : 병렬Query를 사용하고 query_partition_clause을 사용하면 function 계산은 병렬로 처리된다. ) Value_expr : 가용한 표현방법은 다음과 같다. constants, columns, nonanalytic function, function expressions

8 3. 구문설명 ORDER_BY_clause::= ORDER BY : Partition안에서 데이터의 정열을 표현하고 한 개 이상의 키를 사용할 수 있다. 정열은 순위을 결정할때 매우 유용하게 사용할수 있다. Restriction : Analytic Function에서 사용될 때에는 expr과 position만을 사용가능하고 c_alias를 사용할 수 없다. ASC | DESC : 정열순서를 결정하고 ASC가 Default이다. NULLS FIRST | NULLS LAST : Null을 포함한 Row에 대해서 정열순서 상에서 처음이나 마지막에 나타내는 것을 결정한다. NULLS LAST는 ASC에서 Default NULLS FIRST는 DESC에서 Default

9 3. 구문설명 Windowing_clause::= ROWS | RANGE : function의 결과를 계산하기 위해서 사용되는 window(물리적, 논리적인row) 를 결정하기위한 Keyword이다. ROWS : 물리적인단위로 Window를 결정. RANGE : 논리적인 단위로 Window를 결정. ORDER_BY_clause없이 이절을 사용할 수 없다. ( Note : 물리적인 범위로 계산한다면 여러Column값으로 Unique한 정렬을 해야 한다. )

10 3. 구문설명 BETWEEN … AND … : Window의 시작점과 끝점을 결정한다. 첫번째 표현이 시작점을 결정하고 두번째 표현이 끝점을 결정한다. BETWEEN을 생략하면 current row을 Default로 끝점으로 인식하게 된다. UNBOUNDED PRECEDING : Partition의 첫번째row을 시작점으로 하고 끝점으로 사용될 수 없다. UNBOUNDED FOLLOWING : Partition의 마지막row을 끝점으로 하고 시작점으로 사용할 수 없다. CURRENT ROW : 시작점으로 사용할때 value_expr PRECEDING을 끝점으로 사용할 수 없다. CURRENT ROW : 끝점으로 사용할때 value_expr FOLLOWING을 시작점으로 사용할 수 없다. value_expr : RANG or ROWS에 사용될때 PRECEDING value_expr FOLLOWING이 시작점이면 끝점으로 또 사용할 수 없다. value_expr value_expr PRECEDING이 끝점이면 시작점으로 또 사용할 수 없다. FOLLOWING 논리적인 범위를 결정할떄 시간이나 숫자의 범위를 사용하게 되면 Conversion function( NUMTOYMINTERVAL, NUMTODSINTERVAL)을 사용한다. ROWS에 사용될때 - value_expr는 물리적인 Offset이고, 상수나 표현식을 사용해야하며 양수의 값을 가진다. 시작점으로 사용될땐 끝점이전의 값이어야만 한다. RANGE에 사용될때 - value_expr는 논리적인 Offset이고, 양수값을 가진 상수나 표현식을 사용한다. ORDER_BY_clause에는 하나의 expression만 사용할수 있고, value_expr이 숫자이면 NUMBER or DATE, Interval이면 Date Type만이 ORDER BY에 사용할수 있다. ● Windowing_clause을 사용하지 않으면 “RANG BETWEEN UNBOUNDED PRECEDING AND CURENT ROW”가 Default가 된다.

11 4. FUNCTIONS 구분 Ranking Windowing Reporting LAG/LEAD Statistics
설 명 Function 종류 Ranking 지정된 Window에서 순위를 계산하는 Function들이다. RANK and DENSE_RANK, ROW_NUMBER CUME_DIST and PERCENT_RANK, NTILE Windowing Window범위내에서 각Row에 맞는 합계, 평균, 최소/최대값등을 구하며, Self Join 없이 첫번째,마지막 Row을 구할 수 있다. SUM, AVG, MAX, MIN, COUNT, STDDEV, VARIANCE, FIRST_VALUE, LAST_VALUE Reporting Query후 Partition내에서 숫자형태의 데이터에 대해서 합계, 평균, 개수, 편차, 표준편차의 값을 구한다. SUM , AVG , MAX , MIN , COUNT , STDDEV , VARIANCE LAG/LEAD Self-Join없이 CurrentRow의 이전,이후 특정Row에 대한 Column값을 구할 수가 있다. LAG , LEAD Statistics Group Function과는 다르게 통계정보 에서 유용한 상관관계, 회귀등을 계산. VAR_POP, VAR_SAMP, STDDEV_POP/ STDDEV_SAMP, COVAR_POP, COVAR_SAMP CORR, LINEAR REGRESSION FUNCTIONS

12 5. 예제 5. 예 제 5-1 . Ranking Function - Ranking Order
ASC, DESC Option이 Rank()에서 어떻게 사용되는지 보여준다. - S_AMOUNT로 정렬하기 위해서는 Select절 마지막에 ORDER BY 절을 사용해야 한다. SELECT s_productkey, s_amount, RANK() OVER (ORDER BY s_amount) AS default_rank, RANK() OVER (ORDER BY s_amount DESC NULLS LAST) AS custom_rank FROM sales; S_AMOUNT DEFAULT_RANK 130 6 95 5 80 4 75 2 45 1 S_PORDUCTKEY SHOES JACKETS SWEATERS SHIRTS PANTS TIES CUSTOM_RANK 3

13 5-1 . Ranking Function - Ranking on Multiple Expressions
5. 예 제 5-1 . Ranking Function Ranking on Multiple Expressions 2개이상의 컬럼에 대해서 순위를 결정한다. - ORDER BY절에 기술된 Column의 값이 같아야만 순위가 같아진다. SELECT r_regionkey, p_productkey, s_amount, s_profit, RANK() OVER (ORDER BY s_amount DESC, s_profit DESC) AS rank_in_east FROM region, product, sales WHERE r_regionkey = s_regionkey AND p_productkey = s_productkey AND r_regionkey = 'east'; S_AMOUNT S_PROFIT 130 30 100 28 24 75 60 S_PORDUCTKEY SHOES JACKETS SWEATERS SHIRTS PANTS TIES RANK_IN_EAST 1 2 3 4 6 R_REGIONKEY EAST 20 10 T-SHIRTS 7

14 5-1 . Ranking Function - RANK and DENSE_RANK Difference
5. 예 제 5-1 . Ranking Function RANK and DENSE_RANK Difference RANK()는 일반적인 순위이고, DENSE_RANK()는 유일한 값을 하나의 순위로 보는 것이다. SELECT s_productkey, SUM(s_amount) as sum_s_amount, RANK() OVER (ORDER BY SUM(s_amount) DESC) AS rank_all, DENSE_RANK() OVER (ORDER BY SUM(s_amount) DESC) AS rank_dense FROM sales GROUP BY s_productkey; SUM_S_AMOUNT RANK_ALL 100 1 89 3 75 4 66 6 S_PORDUCTKEY SHOES JACKETS SWEATERS SHIRTS PANTS TIES RANK_DENSE 2 T-SHIRTS

15 5-1 . Ranking Function - Per Group Ranking
5. 예 제 5-1 . Ranking Function Per Group Ranking Select결과 내에서 Grouping하여 순위를 따로따로 결정할수가 있다. SELECT r_regionkey, p_productkey, SUM(s_amount) AS S_AMOUNT, RANK() OVER (PARTITION BY r_regionkey ORDER BY SUM(s_amount) DESC) AS rank_of_product_per_region, RANK() OVER (ORDER BY SUM(s_amount) DESC) AS rank_of_product_total FROM product, region, sales WHERE r_regionkey = s_regionkey AND p_productkey = s_productkey GROUP BY r_regionkey, p_productkey ORDER BY r_regionkey; S_AMOUNT RANK_OF_PRODUCT_PER_REGION 130 1 95 2 80 3 75 4 60 5 50 6 S_PORDUCTKEY SHOES JACKETS SWEATERS SHIRTS PANTS TIES RANK_OF_PRODUCT_TOTAL 7 11 12 R_REGIONKEY EAST 20 T-SHIRTS 14 100 99 45 66 13 10 WEST 89

16 5-1 . Ranking Function - Per Cube- and Rollup-group Ranking ( 1 / 2 )
5. 예 제 5-1 . Ranking Function Per Cube- and Rollup-group Ranking ( 1 / 2 ) CUBE or ROLLUP : Group 통계를 보기위해서 SubTotal을 구하는 구문이다 자세한 설명은 다음기회에… CUBE or ROLLUP에 의한 SubTotal에대해서도 순위를 정할수 있다. SELECT r_regionkey, p_productkey, SUM(s_amount) AS SUM_S_AMOUNT, RANK() OVER (PARTITION BY GROUPING(r_regionkey), GROUPING(p_productkey) ORDER BY SUM(s_amount) DESC) AS rank_per_cube FROM product, region, sales WHERE r_regionkey = s_regionkey AND p_productkey = s_productkey GROUP BY CUBE(r_regionkey, p_productkey) ORDER BY GROUPING(r_regionkey), GROUPING(p_productkey), r_regionkey; R_REGIONKEY SUM_S_AMOUNT EAST 130 50 75 80 20 95 S_PRODUCTKEY SHOES JACKETS SWEATERS SHIRTS PANTS TIES RANK_PER_CUBE 1 12 7 6 14 4 60 T-SHIRTS 11 WEST 100 99 89 2 3 5 조회된 모든Row에대해서 순위를 정한다. 다음장에 계속…

17 5-1 . Ranking Function - Per Cube- and Rollup-group Ranking ( 2 / 2 )
5. 예 제 5-1 . Ranking Function Per Cube- and Rollup-group Ranking ( 2 / 2 ) R_REGIONKEY SUM_S_AMOUNT 230 149 150 169 65 161 S_PRODUCTKEY SHOES JACKETS SWEATERS SHIRTS PANTS TIES RANK_PER_CUBE 1 5 4 2 7 3 135 T-SHIRTS 6 1059 EAST 510 WEST 549 45 66 13 10 75 조회된 모든Row에대해서 순위를 정한다. R_REGIONKEY로 Grouping된 결과로 순위를 결정한다. S_PRODUCTKEY로 Grouping된 결과로 순위를 결정한다. ※ 값이 없는 Field는 NULL값이다.

18 5-1 . Ranking Function - Treatment of NULLs
5. 예 제 5-1 . Ranking Function Treatment of NULLs 순위를 결정하는데 있어서 서로다른 NULL Value는 같은 값으로 인식한다. ASC | DESC, NULL FIRST | NULL LAST에 따라서 NULL Value의 순위가 결정된다. SELECT s_productkey, s_amount, RANK() OVER (ORDER BY s_amount ASC NULLS FIRST) AS rank1, RANK() OVER (ORDER BY s_amount ASC NULLS LAST) AS rank2, RANK() OVER (ORDER BY s_amount DESC NULLS FIRST) AS rank3, RANK() OVER (ORDER BY s_amount DESC NULLS LAST) AS rank4 FROM sales; SELECT s_productkey, s_amount, s_quantity, s_profit, RANK() OVER (ORDER BY s_amount NULLS LAST, s_quantity NULLS LAST, s_profit NULLS LAST) AS rank FROM sales; S_AMOUNT 100 75 89 NULL S_PRODUCTKEY SHOES JACKETS SWEATERS SHIRTS PANTS TIES RANK1 6 3 5 1 T-SHIRTS RANK2 4 RANK3 RANK4 S_AMOUNT 75 100 96 NULL S_PRODUCTKEY SHOES JACKETS SWEATERS SHIRTS PANTS TIES RANK 1 2 5 3 8 7 T-SHIRTS 6 SWEAT-SHIRTS S_QUANTITY S_PROFIT 4 다음장에 계속…

19 5-1 . Ranking Function - TOP_N, BOTTON_N
5. 예 제 5-1 . Ranking Function TOP_N, BOTTON_N 상위4씩개만, 하위4씩개만 이라는 조회를 쉽게 할수 있다. RANK function을 SubQuery안에서 사용하고 Outside에서 원하는 개수만큼 Select할수 있다. 예제는 상위4개씩 조회하는 것이다. SELECT region, product, sum_s_amount FROM (SELECT r_regionkey AS region, p_product_key AS product, SUM(s_amount) AS sum_s_amount, RANK() OVER(PARTITION BYr_region_key ORDER BY SUM(s_amount) DESC AS rank1, FROM product, region, sales WHERE r_region_key = s_region_key AND p_product_key = s_product_key GROUP BY r_region_key ORDER BY r_region_key) WHERE rank1 <= 4; REGION SUM_S_AMOUNT EAST 130 95 75 80 WEST 89 PRODUCT SHOES JACKETS SWEATERS SHIRTS T-SHIRTS 100 99

20 CUME_DIST_PER_REGION
5. 예 제 5-1 . Ranking Function CUME_DIST, PERCENT_RANK CUME_DIST(x) = number of values (different from, or equal to, x) in S coming before x in the specified order/ N PERCENT_RANK = (rank of row in its partition - 1) / (number of rows in the partition - 1) SELECT r_regionkey, p_productkey, SUM(s_amount) AS SUM_S_AMOUNT, CUME_DIST() OVER (PARTITION BY r_regionkey ORDER BY SUM(s_amount)) AS cume_dist_per_region FROM region, product, sales WHERE r_regionkey = s_regionkey AND p_productkey = s_productkey GROUP BY r_regionkey, p_productkey ORDER BY r_regionkey, s_amount DESC; R_REGIONKEY SUM_S_AMOUNT EAST 130 95 75 80 20 50 S_PRODUCTKEY SHOES JACKETS SWEATERS SHIRTS PANTS TIES CUME_DIST_PER_REGION 1.00 .84 .56 .70 .14 .18 60 T-SHIRTS .42 WEST 100 99 89 45 66 .28

21 5-1 . Ranking Function - NTILE
5. 예 제 5-1 . Ranking Function NTILE 조회된 Rows에 대해서 원하는 개수만큼 Grouping된 숫자를 Return한다. Sweaters, Jeans, Ties는 같은 Amount을 가지고 있으나 다른 Bucket을 가질수 있다. 따라서 정확하게 Bucket을 구분하기 위해서는 Unique Key로 정렬해야 한다. SELECT p_productkey, s_amount, NTILE(4) (ORDER BY s_amount DESC NULLS FIRST) AS 4_tile FROM product, sales WHERE p_productkey = s_productkey; 4_TILE S_AMOUNT 1 100 90 2 75 89 84 P_PRODUCTKEY SHOES JACKETS SWEATERS SHIRTS T-SHIRTS 3 69 56 JEANS TIES PENTS BELTS 4 45 NULL SOCKS SUITS Bucket 1 Bucket 2 Bucket 3 Bucket 4

22 5-1 . Ranking Function - ROW_NUMBER
5. 예 제 5-1 . Ranking Function ROW_NUMBER 순위을 결정하는 것하고는 다르게 정렬된 순서에 따러서 Unique Number을 결정한다. SELECT p_productkey, s_amount, ROW_NUMBER() (ORDER BY s_amount DESC NULLS LAST) AS srnum FROM product, sales WHERE p_productkey = s_productkey; SRNUM S_AMOUNT 1 100 2 90 5 75 3 89 4 84 P_PRODUCTKEY SHOES JACKETS SWEATERS SHIRTS T-SHIRTS 6 69 56 JEANS TIES PENTS BELTS 7 9 8 45 NULL SOCKS SUITS 11 10

23 5-2 . Windowing Functions - Example of Cumulative Aggregate Function
5. 예 제 5-2 . Windowing Functions Example of Cumulative Aggregate Function Acct_Number별로 Tran_Amount로 정렬해서 누계를 구한다. ROWS UNBOUNDED PRECEDING : Partition의 첫번째 Row부터 Current Row까지 Window 범위(실제로 계산되어지는 범위)가 된다. SELECT Acct_number, Trans_date, Trans_amount, SUM(Trans_amount) OVER (PARTITION BY Acct_number ORDER BY Trans_date ROWS UNBOUNDED PRECEDING) AS Balance FROM Ledger ORDER BY Acct_number, Trans_date; Partition에 대해서 Trans_amount의 누계를 구한다. BALANCE TRANS_AMOUNT 113.45 61.44 -52.01 43.11 32.55 97.69 36.25 10.56 TRANS_DATE 38.09 -5.02 ACCT_NUMBER 73829 82939 = = 10.56 = (-52.01) = (-52.01) = = (-5.02)

24 5-2 . Windowing Functions - Example of Moving Aggregate function
5. 예 제 5-2 . Windowing Functions Example of Moving Aggregate function Acct_Number별로 Tran_Amount로 정렬해서 평균를 구한다. RANGE INTERVAL ‘7’ DAY PRECEDING : Partition내에서 Trans_date가 7일이전까지가 계산범위이다. SELECT Account_number, Trans_date, Trans_amount, AVG (Trans_amount) OVER (PARTITION BY Account_number ORDER BY Trans_date RANGE INTERVAL '7' DAY PRECEDING) AS mavg_7day FROM Ledger; 현재Row의 –7일이전까지 평균을 구한다. BALANCE TRANS_AMOUNT 113.45 30.72 -52.01 10.01 -7.88 36.25 100.25 TRANS_DATE 11.02 ACCT_NUMBER 73829 82939 = ( ) / 1 = ( (-52.01) ) / 3 = ( (-52.01) ) / 2 = ( (-52.01) ) / 2 = ( ) /3 = ( ) / 1 -1.73 10.56 26.45 32.55 55.79 100.56 35.52 -5.02 = ( ) / 1 = ( ) / 1 = ( ) / 2 = ( (-5.02) ) / 3 5-2 . Windowing Functions Example of Centered Aggregate function Current Row에 대해서 Trans_date의 전후1개월간의 평균을 구한다. SELECT Account_number, Trans_date, Trans_amount, AVG (Trans_amount) OVER (PARTITION BY Account_number ORDER BY Trans_date RANGE BETWEENINTERVAL '1‘ MONTH PRECEDING AND INTERVAL '1' MONTH FOLLOWING) as c_avg FROM Ledger;

25 5-2 . Windowing Functions - Example of Variable Sized Window
5. 예 제 5-2 . Windowing Functions Windowing Aggregate Functions with Logical Offsets RANGE BETWEEN 1 PRECEDING AND CURRENT ROW : P_PKEY값의 범위(RANGE)가 Current Row의 현재값보다 1작은 값의 S_AMT 합계를 구한다. FIRST_VALUE, LAST_VALUE를 제외하고는 논리적인 범위를 정할수 있다. SELECT r_rkey, p_pkey, s_amt, SUM(s_amt) OVER (ORDER BY p_pkey RANGE BETWEEN 1 PRECEDING AND CURRENT ROW) AS current_group_sum FROM product, region, sales WHERE r_rkey = s_rkey AND p_pkey = s_pkey AND r_rkey = 'east' ORDER BY r_rkey, p_pkey; P_PKEY값이 1작은 값은 합계. CURRENT_GROUP_SUM S_AMT 130 180 50 265 80 P_PKEY 1 2 3 R_RKEY EAST = 130 = 50 + ( ) = 75 60 = ( ) + 20 235 20 4 5-2 . Windowing Functions Example of Variable Sized Window RANGE fn(t_timekey) PRECEDING : Current Row의 fn(t_timekey) Function의 Return값에 따라서 논리적인 범위가 바뀔수 있다. SELECT t_timekey, AVG(stock_price) OVER (ORDER BY t_timekey RANGE fn(t_timekey ) PRECEDING) AS av_price FROM stock, time WHERE st_timekey = t_timekey ORDER BY t_timekey;

26 5-2 . Windowing Functions - FIRST_VALUE AND LAST_VALUE FUNCTIONS
5. 예 제 5-2 . Windowing Functions Windowing Aggregate Functions with Physical Offsets ROWS 1 PRECEDING : 물리적으로 하나이전의 Row를 참조한다. SELECT t_timekey, s_amount, FIRST_VALUE(s_amount) OVER (ORDER BY t_timekey ROWS 1 PRECEDING) AS LAG_physical, SUM(s_amount) OVER (ORDER BY t_timekey ROWS 1 PRECEDING) AS MOVINGSUM, FROM sales, time WHERE sales.s_timekey = time.t_timekey ORDER BY t_timekey; 물리적으로 하나이전의 데이터와 합계를 구한다. MOVINGSUM LAG_PHYSICAL 1 5 7 2 4 3 S_AMOUNT T_TIMEKEY = 1 = 2 + 3 = 4 + 1 = 3 + 4 = 5 + 2 5-2 . Windowing Functions FIRST_VALUE AND LAST_VALUE FUNCTIONS Window의 범위 안에서 첫번째 Row 나 마지막 Row에서 특정 Column값을 Return한다. 예를 들면 매주 월요일에 대한 매출증가율을 볼때 주별로 Partittion하고 FIRST_VALUE를 사용하면 쉽게 해결할수 있을 것이다.

27 5-3 . Reporting Functions -
5. 예 제 5-3 . Reporting Functions - ‘*’문자는 Count(*)에서만 사용할수 있다. PARTITION BY 절이 없으면 전체Result Set에 대해서 계산을 한다. 다음 예제는 각 상품별 가장큰매출을 올린 지역을 찾는 것이다. SELECT s_productkey, s_regionkey, sum_s_amount FROM (SELECT s_productkey, s_regionkey, SUM(s_amount) AS sum_s_amount, MAX(SUM(s_amount)) OVER (PARTITION BY s_productkey) AS max_sum FROM sales GROUP BY s_productkey, s_regionkey) WHERE sum_s_amount = max_sum_s_amount; S_REGIONKEY SUM_S_AMOUNT WEST 99 EAST 50 45 20 100 S_PRODUCTKEY JACKETS PANTS SHOES 60 80 130 75 SHIRTS SWEATERS 95 TIES 66 MAX_SUM Inline View의 Max값을 가져온 Query Result S_REGIONKEY SUM_S_AMOUNT WEST 99 45 S_PRODUCTKEY JACKETS PANTS 80 130 75 SHIRTS SHOES SWEATERS EAST 95 TIES Outer Query 의 결과

28 5-3 . Reporting Functions - RATIO_TO_REPORT
5. 예 제 5-3 . Reporting Functions RATIO_TO_REPORT Window범위내 전체값중 비중를 구한다. Expression이 Null이면 값도 Null이다. SELECT s_productkey, SUM(s_amount) AS sum_s_amount, SUM(SUM(s_amount)) OVER () AS sum_total, RATIO_TO_REPORT(SUM(s_amount)) OVER () AS ratio_to_report FROM sales GROUP BY s_productkey; SUM_S_AMOUNT SUM_TOTAL 90 45 100 520 S_PRODUCTKEY JACKETS SOCKS PANTS SHOES 80 SHIRTS T-SHIRTS SWEATERS 75 TIES 10 RATIO_TO_REPORT 0.17 0.08 0.19 0.15 0.14 0.01 = 100 / 520 = 90 / 520 = 80 / 520 = 75 / 520 = 10 / 520 = 45 / 520

29 5. 예 제 5-4 . Lag/Lead Functions
Self Join없이 Query된 결과값에 대해서 다른Row의 값을 동시에 Access할수 있다. LAG는 이전의 Row를 LEAD는 Current Row이후의 Row을 Access할수 있다. SELECT t_timekey, s_amount, LAG(s_amount,1) OVER (ORDER BY t_timekey) AS LAG_amount, LEAD(s_amount,1) OVER (ORDER BY t_timekey) AS LEAD_amount FROM sales, time WHERE sales.s_timekey = time.t_timekey ORDER BY t_timekey; S_AMOUNT LAG_AMOUNT 2 1 NULL T_TIMEKEY 3 5 4 LEAD_AMOUNT

30 5-4 . Statistics Functions
5. 예 제 5-4 . Statistics Functions Group Function과는 다르게 통계정보에서 유용한 상관관계, 회귀등을 계산. VAR_POP, VAR_SAMP, STDDEV_POP, STDDEV_SAMP, COVAR_POP, COVAR_SAMP, CORR, LINEAR REGRESSION FUNCTIONS 여기에서는 전문적인 통계함수인관계로 통계에 대한 실력이 미비하여 정확한 매뉴얼 해석이 안된는 관계로 좀더 자세한 내용을 알고자 한다면 Oracle8i Data Warehousing Guide를 참조하기 바랍니다.