Analytic Function Oracle 8 i 8 1 6

Analytic Function 활용하기 ( Oracle 8 i 8. 1. 6이상 ) 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 4

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

Analytic Function 활용하기 ( Oracle 8 i 8. 1. 6이상 ) 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절에 사용할수 있다. 6

Analytic Function 활용하기 ( Oracle 8 i 8. 1. 6이상 ) • 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 작성자 : 이 인재 7

Analytic Function 활용하기 ( Oracle 8 i 8. 1. 6이상 ) 4. FUNCTIONS 구분 설 명 Function 종류 지정된 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없이 Current. Row의 이전, 이후 특정Row에 대한 Column값을 구할 수가 있다. LAG , LEAD Group Function과는 다르게 통계정보 에서 유용한 상관관계, 회귀등을 계산. VAR_POP, VAR_SAMP, STDDEV_POP/ STDDEV_SAMP, COVAR_POP, COVAR_SAMP CORR, LINEAR REGRESSION FUNCTIONS Ranking Statistics 작성자 : 이 인재 11

Analytic Function 활용하기 ( Oracle 8 i 8. 1. 6이상 ) 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_PORDUCTKEY S_AMOUNT DEFAULT_RANK CUSTOM_RANK SHOES JACKETS SWEATERS SHIRTS PANTS TIES 130 95 80 75 75 45 6 5 4 2 2 1 1 2 3 4 4 6 12

Analytic Function 활용하기 ( Oracle 8 i 8. 1. 6이상 ) 5 -1. Ranking Function - 5. 예 제 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'; R_REGIONKEY EAST S_PORDUCTKEY SHOES JACKETS S_AMOUNT 130 100 S_PROFIT 30 28 RANK_IN_EAST 1 2 EAST EAST SWEATERS SHIRTS PANTS TIES T-SHIRTS 100 75 75 60 20 24 24 10 3 4 4 6 7 작성자 : 이 인재 13

Analytic Function 활용하기 ( Oracle 8 i 8. 1. 6이상 ) 5 -1. Ranking Function - RANK and DENSE_RANK Difference - RANK()는 일반적인 순위이고, - DENSE_RANK()는 유일한 값을 하나의 순위로 보는 것 이다. 작성자 : 이 인재 5. 예 제 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; S_PORDUCTKEY SHOES JACKETS SUM_S_AMOUNT 100 RANK_ALL 1 1 RANK_DENSE 1 1 SWEATERS SHIRTS PANTS TIES T-SHIRTS 89 75 75 66 66 3 4 4 6 6 2 3 3 4 4 14

Analytic Function 활용하기 ( Oracle 8 i 8. 1. 6이상 ) 5 -1. Ranking Function - 5. 예 제 Per Group Ranking 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 BY r_regionkey, p_productkey ORDER BYRANK_OF_PRODUCT_TOTAL r_regionkey; S_PORDUCTKEY GROUP S_AMOUNT RANK_OF_PRODUCT_PER_REGION SHOES 130 1 1 JACKETS 95 2 4 SWEATERS 80 3 6 SHIRTS 75 4 7 - Select결과 내에서 Grouping하여 순위를 따로따로 결정할수가 있다. R_REGIONKEY EAST EAST WEST PANTS TIES T-SHIRTS SHOES JACKETS T-SHIRTS SWEATERS 60 50 20 100 99 89 75 5 6 7 1 2 3 4 11 12 14 2 3 5 7 WEST SHIRTS TIES PANTS 75 66 45 5 6 7 7 10 13 작성자 : 이 인재 15

Analytic Function 활용하기 ( Oracle 8 i 8. 1. 6이상 ) 5 -1. Ranking Function - 5. 예 제 Per Cube- and Rollup-group Ranking ( 1 / 2 ) r_regionkey, p_productkey, SUM(s_amount) AS - CUBE or ROLLUP : Group 통계를 보기위해 SELECT SUM_S_AMOUNT, 서 Sub. Total을 구하는 구문이다 자세한 설명은 RANK() OVER (PARTITION BY GROUPING(r_regionkey), 다음기회에… GROUPING(p_productkey) ORDER BY SUM(s_amount) DESC) AS rank_per_cube - CUBE or ROLLUP에 의한 Sub. Total에대해 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 S_PRODUCTKEY SUM_S_AMOUNT RANK_PER_CUBE EAST SHOES 130 1 EAST JACKETS 50 12 EAST EAST WEST SHIRTS SWEATERS T-SHIRTS TIES PANTS SHOES JACKETS 80 75 60 95 20 100 99 6 7 11 4 14 2 3 WEST SHIRTS SWEATERS 89 75 5 7 조회된 모든Row에대해서 순위 를 정한다. 다음장에 계속… 작성자 : 이 인재 16

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

Analytic Function 활용하기 ( Oracle 8 i 8. 1. 6이상 ) 5 -1. Ranking Function - 5. 예 제 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 rank 1, RANK() OVER (ORDER BY s_amount ASC NULLS LAST) AS rank 2, RANK() OVER (ORDER BY s_amount DESC NULLS FIRST) AS rank 3, RANK() OVER (ORDER BY s_amount DESC NULLS LAST) AS rank 4 FROM sales; S_PRODUCTKEY S_AMOUNT RANK 1 RANK 2 RANK 3 RANK 4 SHOES 100 6 4 3 1 JACKETS 100 6 4 3 1 SHIRTS 89 5 3 SWEATERS 75 3 1 6 4 T-SHIRTS 75 3 1 6 4 TIES NULL 1 6 PANTS NULL 1 6 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_PRODUCTKEY S_AMOUNT S_QUANTITY S_PROFIT RANK SHOES 75 6 4 1 JACKETS 75 NULL 4 2 SWEAT-SHIRTS 96 NULL 6 3 SWEATERS 100 NULL 1 5 T-SHIRTS 100 NULL 3 6 TIES NULL 1 2 7 PANTS 작성자 : 이 인재 다음장에 계속… NULL 18 8

Analytic Function 활용하기 ( Oracle 8 i 8. 1. 6이상 ) 5 -1. Ranking Function - TOP_N, BOTTON_N - 상위4씩개만, 하위4씩개만 이라는 조회를 쉽게 할수 있다. - RANK function을 Sub. Query안에서 사용하고 Outside에서 원하는 개수만큼 Select할수 있다. -예제는 상위4개씩 조회하는 것이다. 작성자 : 이 인재 5. 예 제 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 rank 1, 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 rank 1 <= 4; REGION EAST WEST PRODUCT SHOES JACKETS SHIRTS SWEATERS SHOES JACKETS SUM_S_AMOUNT 130 95 80 75 100 99 WEST T-SHIRTS SWEATERS 89 75 WEST SHIRTS 75 19

Analytic Function 활용하기 ( Oracle 8 i 8. 1. 6이상 ) 5 -1. Ranking Function - 5. 예 제 CUME_DIST, PERCENT_RANK 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 - PERCENT_RANK = (rank of row in its FROM region, product, sales partition - 1) / (number of rows in the partition WHERE r_regionkey = s_regionkey AND p_productkey = s_productkey 1) GROUP BY r_regionkey, p_productkey ORDER BY r_regionkey, s_amount DESC; R_REGIONKEY S_PRODUCTKEY SUM_S_AMOUNT CUME_DIST_PER_REGION EAST SHOES 130 1. 00 EAST JACKETS 95. 84 EAST SHIRTS 80. 70 EAST SWEATERS 75. 56 - CUME_DIST(x) = number of values (different from, or equal to, x) in S coming before x in the specified order/ N 작성자 : 이 인재 EAST WEST T-SHIRTS TIES PANTS SHOES JACKETS T-SHIRTS SWEATERS 60 50 20 100 99 89 75 . 42. 18. 14 1. 00. 84. 70. 56 WEST SHIRTS TIES PANTS 75 66 45 . 28. 14 20

Analytic Function 활용하기 ( Oracle 8 i 8. 1. 6이상 ) 5 -1. Ranking Function - NTILE - 조회된 Rows에 대해서 원하는 개수만큼 Grouping된 숫자를 Return한다. - Sweaters, Jeans, Ties는 같은 Amount을 가지 고 있으나 다른 Bucket을 가질수 있다. 따라서 정확하게 Bucket을 구분하기 위해서는 Unique Key로 정렬해야 한다. 작성자 : 이 인재 5. 예 제 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; P_PRODUCTKEY S_AMOUNT 4_TILE SUITS SHOES JACKETS SHIRTS T-SHIRTS SWEATERS JEANS TIES PENTS BELTS SOCKS NULL 100 90 89 84 75 75 75 69 56 45 1 1 1 2 2 2 3 3 3 4 4 Bucket 1 Bucket 2 Bucket 3 Bucket 4 21

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

Analytic Function 활용하기 ( Oracle 8 i 8. 1. 6이상 ) 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; ACCT_NUMBER TRANS_DATE TRANS_AMOUNT BALANCE 73829 82939 1998 -11 -01 1998 -11 -05 1998 -11 -13 1998 -11 -01 1998 -11 -29 113. 45 -52. 01 36. 25 10. 56 32. 55 -5. 02 113. 45 61. 44 97. 69 10. 56 43. 11 38. 09 작성자 : 이 인재 5. 예 제 Partition에 대해서 Trans_amount의 누계를 구한다. = = = 113. 45 + (-52. 01) + 36. 25 10. 56 + 32. 55 + (-5. 02) 23

Analytic Function 활용하기 ( Oracle 8 i 8. 1. 6이상 ) 5 -2. Windowing Functions 5. 예 제 - Example of Moving Aggregate function SELECT Account_number, Trans_date, Trans_amount, AVG (Trans_amount) OVER (PARTITION BY Account_number ORDER BY Trans_date RANGE INTERVAL '7' DAY - RANGE INTERVAL ‘ 7’ DAY PRECEDING : PRECEDING) Partition내에서 Trans_date가 7일이전까지가 계 AS mavg_7 day 산범위이다. FROM Ledger; 현재Row의 – 7일이전까지 평균 을 구한다. ACCT_NUMBER TRANS_DATE TRANS_AMOUNT BALANCE - Acct_Number별로 Tran_Amount로 정렬해서 평균를 구한다. 73829 73829 82939 1998 -11 -03 1998 -11 -09 1998 -11 -13 1998 -11 -14 1998 -11 -20 1998 -11 -01 1998 -11 -10 113. 45 -52. 01 36. 25 10. 56 32. 55 100. 25 10. 01 113. 45 30. 72 -7. 88 -1. 73 26. 45 100. 25 10. 01 82939 1998 -11 -25 1998 -11 -26 11. 02 100. 56 11. 02 55. 79 = ( 11. 02 ) / 1 = ( 11. 02 + 100. 56 ) / 2 82939 1998 -11 -30 -5. 02 35. 52 = ( 11. 02 + 100. 56 + (-5. 02) ) / 3 5 -2. Windowing Functions ( ( ( ( 113. 45 ) / 1 113. 45 + (-52. 01) ) / 2 (-52. 01) + 36. 25 + 10. 56) / 3 36. 25 + 10. 56 + 32. 55 ) /3 100. 25 ) / 1 10. 01 ) / 1 - 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; 24

Analytic Function 활용하기 ( Oracle 8 i 8. 1. 6이상 ) 5 -2. Windowing Functions 5. 예 제 - 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작은 값은 합계. R_RKEY P_PKEY EAST 1 EAST 2 EAST 3 EAST 3 3 4 S_AMT 130 50 80 CURRENT_GROUP_SUM 130 180 265 = 130 + 50 = 50 + ( 80 + 75 + 60 ) 75 60 20 265 235 = 50 + ( 80 + 75 + 60 ) = ( 80 + 75 + 60 ) + 20 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; 25

Analytic Function 활용하기 ( Oracle 8 i 8. 1. 6이상 ) 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; T_TIMEKEY S_AMOUNT LAG_PHYSICAL MOVINGSUM 92 -10 -11 92 -10 -12 92 -10 -15 1 4 3 2 5 1 1 4 3 2 1 5 7 5 -2. Windowing Functions 5. 예 제 물리적으로 하나이전의 데이터 와 합계를 구한다. = = = 1 4 3 2 5 + + 1 4 3 2 - FIRST_VALUE AND LAST_VALUE FUNCTIONS - Window의 범위 안에서 첫번째 Row 나 마지막 Row에서 특정 Column값을 Return한다. - 예를 들면 매주 월요일에 대한 매출증가율을 볼때 주별로 Partittion하고 FIRST_VALUE를 사용하면 쉽게 해결할수 있을 것이다. 작성자 : 이 인재 26

Analytic Function 활용하기 ( Oracle 8 i 8. 1. 6이상 ) 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; Inline View의 Max값을 가져온 Query Result Outer Query 의 결과 S_PRODUCTKEY S_REGIONKEY SUM_S_AMOUNT JACKETS WEST 99 JACKETS EAST 50 PANTS EAST 20 PANTS WEST 45 SHIRTS EAST 60 SHIRTS WEST 80 SHOES WEST 100 SHOES EAST 130 SWEATERS WEST 75 SWEATERS EAST 75 TIES 작성자 : 이 인재 EAST WEST 5. 예 제 95 66 MAX_SUM 99 99 45 45 80 80 130 75 75 S_PRODUCTKEY S_REGIONKEY SUM_S_AMOUNT JACKETS WEST 99 PANTS WEST 45 SHIRTS WEST 80 SHOES EAST 130 SWEATERS WEST 75 SWEATERS EAST 75 TIES EAST 95 95 95 27

Analytic Function 활용하기 ( Oracle 8 i 8. 1. 6이상 ) 5 -3. Reporting Functions - RATIO_TO_REPORT - Window범위내 전체값중 비중를 구한다. - Expression이 Null이면 값도 Null이다. S_PRODUCTKEY SUM_S_AMOUNT SHOES 100 JACKETS 90 SHIRTS 80 SWEATERS T-SHIRTS TIES PANTS SOCKS 작성자 : 이 인재 75 75 10 45 45 5. 예 제 SELECT s_productkey, SUM(s_amount) AS sum_s_amount, 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_TOTAL 520 520 RATIO_TO_REPORT 0. 19 = 100 / 520 0. 17 = 90 / 520 0. 15 = 80 / 520 0. 14 0. 01 0. 08 = = = 75 75 10 45 45 / / / 520 520 520 28

Analytic Function 활용하기 ( Oracle 8 i 8. 1. 6이상 ) 5. 예 제 5 -4. Lag/Lead Functions 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 - LAG는 이전의 Row를 LEAD는 Current Row이 LEAD_amount 후의 Row을 Access할수 있다. FROM sales, time WHERE sales. s_timekey = time. t_timekey ORDER BY t_timekey; - Self Join없이 Query된 결과값에 대해서 다른 Row의 값을 동시에 Access할수 있다. 작성자 : 이 인재 T_TIMEKEY 99 -10 -11 99 -10 -12 99 -10 -13 S_AMOUNT 1 2 3 LAG_AMOUNT NULL 1 2 LEAD_AMOUNT 2 3 4 99 -10 -15 4 5 3 4 5 NULL 29