Oracle8i データウェアハウス機能活用法 ~ レポーティングに有効な集計関数 分析関数 ~ Creation Date: Oct. 11, 2000 Last Update: Oct. 11, 2000 Version: 1.0!! DWH etc Business Intelligence Oracle8i RDBMS DWH Oracle8i Oracle Corporation Japan 1
目次 1 集計関数 / 分析関数とは 2 集計関数 / 分析関数のパフォーマンス効果 3 ケーススタディグループ小計やクロス集計を計算するランキングを表示する前月比較を表示する累計を計算する移動平均を計算する構成比を計算する Oracle8i SQL Oracle8i Oracle Oracle Corporation Japan 2
集計関数 / 分析関数とは DWH システムの分析要件で必要とされる計算ランキング 移動平均 累計計算 etc 今までの SQL 言語では高度なプログラミングによるインプリが必要 ヒストグラム 前年同月比 :PL/SQL : ファンクションランキング :rownum : サブトータル :union : 検索パフォーマンスが劣化高度なインプリ知識が必要 DWH Business Intelligence BI - - - - etc Oracle8i DB Oracle Corporation Japan 3
集計関数 / 分析関数とは 集計関数 (Oracle8i R8.1.5~) 分析関数 ( Oracle8i R8.1.6~) Oracle RDBMS を対象とした集計 分析計算を強化するためにサポートされた SQL 関数群 利点 - Oracle をターゲットとした主要な分析要件を直接サポート - 複雑な計算をサーバ側で処理させることが可能 パフォーマンスとスケーラビリティーが飛躍的に向上 - SQL 開発の簡素化 ( 複雑な SQL 文からの解放 ) Oracle Reports などの Oracle BI ツール製品との組み合わせが可能 定型帳票などのアプリケーションに応用できる ANSI において分析関数を SQL 規格に加えることを検討中 ( 集計関数はすでに準拠 ( )) ( ) ANSI and ISO proposals for SQL3, a draft standard for enhancements to SQL Oracle8i BI SQL Oracle8i R8.1.5 Oracle8i R8.1.6 Oracle SQL Oracle Reports Oracle BI Oracle Corporation Japan 4
集計関数 / 分析関数のパフォーマンス効果 例 ) 売上 Top5 ランキング Emp(14300 行 ) Dept(4 行 ) Deptno Ename Gender Salary comm Deptno Dname 10 smith 女性 5000 2000 10 エンジニア 10 blake 女性 3400 1000 20 マーケティング 20 jones 女性 2050 : : 10 king 男性 8000 3000 20 adams 男性 5000 1400 : : : : : 給与トップ 5 ランキング 1 位 king エンジニア 8000 2 位 smith エンジニア 7500 3 位 adams マーケティング 5000 4 位 clack マーケティング 5000 5 位 scott エンジニア 4050 Emp Dept Oracle Corporation Japan 5
集計関数 / 分析関数のパフォーマンス効果 select e.ename ename,d.dname dname,e.salary salary from emp e, dept d where e.deptno = d.deptno and 5 > ( select count(distinct(salary)) from emp where salary > e.salary ) order by salary desc ; 今までの SQL インプリ例 SQL Inline View SQL Select Select Oracle Corporation Japan 6
集計関数 / 分析関数のパフォーマンス効果 実行計画 Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (ORDER BY) 2 1 FILTER 3 2 NESTED LOOPS 4 3 TABLE ACCESS (FULL) OF EMP 5 3 TABLE ACCESS (BY INDEX ROWID) OF DEPT 6 5 INDEX (UNIQUE SCAN) OF PK_DEPTNO (UNIQUE) 7 2 SORT (GROUP BY) 8 7 TABLE ACCESS (FULL) OF EMP EMP Oracle Corporation Japan 7
集計関数 / 分析関数のパフォーマンス効果 パフォーマンス統計 Statistics 39984 recursive calls 47185 db block gets 322485 consistent gets 44157 physical reads 0 redo size 521 bytes sent via SQL*Net to client 416 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 2153 sorts (memory) 4998 sorts (disk) 5 rows processed SQL*Plus AUTOTRACE DB BLOCK GETS,CONSISTENT GETS SORTS(MEMORY),SORTS(DISK) Oracle Corporation Japan 8
集計関数 / 分析関数のパフォーマンス効果 select ename,dname,salary,rank_sal from ( select e.ename ename,d.dname dname,e.salary salary, rank() over(order by e.salary desc) as rank_sal from emp e,dept d where e.deptno = d.deptno ) where rank_sal < 6 分析関数を使用したインプリ例 SQL rank() rank() Oracle Corporation Japan 9
集計関数 / 分析関数のパフォーマンス効果 実行計画 Execution Plan 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 VIEW 2 1 WINDOW (SORT PUSHED RANK) 3 2 NESTED LOOPS 4 3 TABLE ACCESS (FULL) OF EMP 5 3 TABLE ACCESS (BY INDEX ROWID) OF DEPT 6 5 INDEX (UNIQUE SCAN) OF PK_DEPTNO (UNIQUE) 比較 EMP 表へのフルスキャンの回数が減っている ソート処理の回数が減っている SQL EMP WINDOW(SORT PUSHEC RANK) ORDER BY SORT Oracle Corporation Japan 10
集計関数 / 分析関数のパフォーマンス効果 パフォーマンス統計 ( 分析関数使用 ) 分析関数不使用 Statistics ---------------------------------------------------------- 8 recursive calls 6 db block gets 28682 consistent gets 1 physical reads 0 redo size 917 bytes sent via SQL*Net to client 424 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 1 sorts (disk) 5 rows processed Statistics ------------------------------ 0 57184 1157984 0 0 830 424 2 14296 0 5 経過 :09:57.70: 経過 :00:00.91 : 経過 : 比較 アクセスブロック数が減少している ソート処理の回数が少なくて済む パフォーマンスが改善されている DB BLOCK GETS + CONSISTENT GETS SORT(MEMORY) + SORT(DISK) EMP Oracle Corporation Japan 11
ケーススタディ 1. グループ小計やクロス集計を計算する ROLLUP/CUBE ( 集計関数 ) 2. ランキングを表示する ランキング関数 ( 分析関数 ) 3. 前月比較を表示する LAG/LEAD 関数 4. 累計を計算する 関数 ( 分析関数 ) ウィンドウ関数 ( 分析関数 ) 5. 移動平均を計算する ウィンドウ関数 ( 分析関数 ) 6. 構成比を計算する レポート関数 ( 分析関数 ) BI Oracle Corporation Japan 12
1. グループ小計やクロス集計を計算する! 性別 職種ごとのグループ小計! 性別 職種のクロス集計 Gender Dname Ename SUM(Salary) 女性 エンジニア smith 2000 女性 マーケティング allen 3000 女性 エンジニア ward 5000 女性 マーケティング jones 1500 女性 : : : 女性 : : : 男性 マーケティング scott 12500 男性 エンジニア king 12500 女性 : : : 女性 : : : 女性総計 27,500 エンジニア 15,000 マーケティング 7,500 男性総計 30,800 エンジニア 19,800 マーケティング 11,000 SQL group by select union all select select N N+1 select N 2N select ROLLUP CUBE Oracle Corporation Japan 13
集計関数 従来の GROUP BY 句から拡張された演算子 ROLLUP 指定した集計軸の並びに沿ったサブ トータル及び総合計を返す CUBE 指定した集計軸のクロス集計及び総合計を返す Gender Dname エンジニア マーケティング 小計 男性女性 3000 2000 4500 3000 7500 5000 小計 12500 GROUP BY GROUP BY ROLLUP Oracle Corporation Japan 14
集計関数 従来の GROUP BY 句から拡張された演算子 ROLLUP 指定した集計軸の並びに沿ったサブ トータル及び総合計を返す CUBE 指定した集計軸のクロス集計及び総合計を返す Gender Dname エンジニア マーケティング 小計 男性女性 3000 2000 4500 3000 7500 5000 小計 5000 7500 12500 CUBE Oracle Corporation Japan 15
集計関数 ROLLUP 従来の GROUP BY 句から拡張された演算子 性別 職種ごとのグループ小計 select gender,dname,sum(salary) from emp,dept group by rollup (gender,dname); Gender Dname SUM(Salary) 女性 エンジニア 2000 女性 マーケティング 3000 女性 5000 男性 エンジニア 3000 男性 マーケティング 4500 男性 7500 12500 引数 : グループ化列の順序づけリスト 1 右 左の順にレベル付け第 1 レベル :dname : 第 2 レベル :gender : 2 レベル順に小計を作成 - Gender に対するそれぞれの Dname にわたって集計 - 各 Genderに対して全ての Dname にわたって集計 - 総計を計算 SQL SQL SQL Group by rollup rollup dname gender Rollup Oracle Corporation Japan 16
集計関数 CUBE 従来の GROUP BY 句から拡張された演算子 性別 職種のクロス集計 select gender,dname,sum(salary) from emp,dept group by cube (gender,dname); 引数 : グループ化列の順序づけリスト 1 右 左の順にレベル付け第 1 レベル :dname : 第 2 レベル :gender : 2 レベル順に小計を作成 Gender Dname SUM(Salary) 女性 エンジニア 2000 女性 マーケティング 3000 女性 5000 男性 エンジニア 3000 男性 マーケティング 4500 男性 7500 エンジニア 5000 マーケティング 7500 12500 - Genderに対する全ての Dname にわたって集計 - 各 Genderに対して全ての Dname にわたって集計 - 各 Dnameに対して全ての Gender にわたって集計 - 総計を計算 Group by cube rollup cube dname gender cube Oracle Corporation Japan 17
参考 :GROUPING : 関数 全体 を表す NULL 値と実際の NULL 値を区別する select gender,dname,sum(salary),grouping(gender) G,grouping(dname) D from emp,dept 引数 : グループ化列 group by cube (gender,dname); 全体 を表す NULL 値である場合には 1 を返すそれ以外は 0 を返す Gender Dname SUM(Salary) G D 女性 エンジニア 2000 0 0 女性 マーケティング 3000 0 0 女性 5000 0 1 男性 エンジニア 3000 0 0 男性 マーケティング 4500 0 0 男性 7500 0 1 エンジニア 5000 1 0 マーケティング 7500 1 0 12500 1 1 GROUPING NULL Oracle grouping NULL grouping SQL where grouping(gender) = 1 Oracle Corporation Japan 18
参考 : 集計関数に関する Oracle8i R8.1.6 の新機能 従来の GROUP BY 句から拡張された演算子 性別 職種ごとのグループ小計 select gender,dname,sum(salary) from emp,dept group by gender rollup (dname); Gender Dname SUM(Salary) 女性 エンジニア 2000 女性 マーケティング 3000 女性 5000 男性 エンジニア 3000 男性 マーケティング 4500 男性 7500 Oracle8i R8.1.6 からの新機能 rollup/cube の前にレベルを書くと そのレベルまでの集計を行い それ以降のレベルと総計の計算を行わない例では総計の計算が行われない Oracle8i R8.1.6 Oracle8i R8.1.6 rollup/cube dname gender rollup grouping rollup/cube Location group by gender,location rollup(dname) Oracle Corporation Japan 19
2. ランキングを表示する! 総合給与ランキング! 総合給与ランキング ( 同順位の場合はインセンティブでランク付け )! 性別給与ランキング ( 同順位の場合はインセンティブでランク付け ) 9 月度総合給与ランキング 1 位 king 8000 2 位 smith 7500 3 位 scott 5500 4 位 adams 5 位 clark 6 位 allen 7 位 turner 8 位 jones 9 位 blake 10 位 martin 9 月度給与ランキング ( 男性 ) 1 位 king 8000 2 位 scottt 5500 3 位 adams 5300 ( 女性 ) 1 位 smith 7500 2 位 allen 5000 3 位 jones 4600 rownum rank Oracle Corporation Japan 20
分析関数 ( ランキング関数 ) 分析関数 データセットのレコードに対し レコードのランクレコードのランクを計算する 総合給与ランキング select ename,gender,salary,comm, rank () over (order by sal desc) as rank_sal from emp ; Ename Gender Salary comm rank_sal king 男性 8000 3000 1 smith 女性 5000 2000 2 adams 男性 5000 1400 2 clark 男性 5000 1000 2 scott 男性 4050 1200 5 blake 女性 3400 1000 6 allen 女性 3250 500 7 turner 男性 2500 8 jones 女性 2050 9 ward 女性 2000 10 martin 女性 1500 11 rank over - order by ASC/DESC sal Oracle Corporation Japan 21
分析関数 ( ランキング関数 ) 分析関数 データセットのレコードに対し レコードのランクレコードのランクを計算する 総合給与ランキング ( 同順位の場合はインセンティブでランク付け ) select ename,gender,salary,comm, rank () over (order by sal desc, comm desc) as rank_sal from emp ; Ename Gender Salary comm rank_sal king 男性 8000 3000 1 smith 女性 5000 2000 2 adams 男性 5000 1400 3 clark 男性 5000 1000 4 scott 男性 4050 1200 5 blake 女性 3400 1000 6 allen 女性 3250 500 7 turner 男性 2500 8 jones 女性 2050 9 ward 女性 2000 10 martin 女性 1500 11 最初の式で同じ値が解決できない 2 番目の式を用いて解決 sal DESC comm DESC Oracle Corporation Japan 22
分析関数 ( ランキング関数 ) データセットのレコードに対し レコードのランクレコードのランクを計算する 性別給与ランキング ( 同順位の場合はインセンティブでランク付け ) select ename,gender,salary,comm, rank () over (partition by gender order by sal desc, comm desc) as rank_sal from emp ; Ename Gender Salary comm rank_sal smith 女性 5000 2000 1 blake 女性 3400 1000 2 allen 女性 3250 500 3 jones 女性 2050 4 ward 女性 2000 5 martin 女性 1500 6 king 男性 8000 3000 1 adams 男性 5000 1400 2 clark 男性 5000 1000 3 scott 男性 4050 1200 4 turner 男性 2500 5 性別によってカテゴリー分け カテゴリー別にランク表示 partition by オプションによって rank が操作するグループにデータセットが分割される グループが変更されるたびランクがリセットされる - partition by. partition by gender Oracle Corporation Japan 23
( 参考 ) 結果セット パーティションについて select ename,gender,salary from emp Ename Gender Salary smith 女性 5000 allen 女性 3250 ward 女性 2000 jones 女性 2050 martin 女性 1500 blake 女性 3400 clark 男性 5000 scott 男性 4050 king 男性 8000 turner 男性 2500 adams 男性 1000 Oracle select Oracle Corporation Japan 24
( 参考 ) 結果セット パーティションについて select ename,gender,salary, max(salary) over(partition by gender) as max_sal from emp Ename Gender Salary max_sal smith 女性 5000 5000 allen 女性 3250 5000 ward 女性 2000 5000 jones 女性 2050 5000 martin 女性 1500 5000 blake 女性 3400 5000 clark 男性 5000 8000 scott 男性 4050 8000 king 男性 8000 8000 turner 男性 2500 8000 adams 男性 1000 8000 結果セット パーティション 問い合わせ結果セットを分析関数によって分割した行グループのこと Oracleの表のパーティションとは異なることに注意する over partition by. gender max max max Oracle Corporation Japan 25
3. 前月比較を表示する! 地域別売上金額の前月との差分を表示する 2000 年上半期地区別売上推移 売上金額前月との差額 ( 関東 ) 1 月 3250 0 2 月 3400 150 3 月 2050-1350 4 月 1500-550 5 月 5000 3500 6 月 2000-3000 ( 東海 ) 1 月 1500 0 : : : : LAG LEAD Oracle Corporation Japan 26
分析関数 分析関数 (LAG/LEAD 関数 ) 内部結合を用いることなく 表の複数行への同時アクセス表の複数行への同時アクセスが可能 地域別に前月売上金額を表示する例 select time,region,sales, lag(sales,1,0) over (partition by region from emp ; order by time) as lag_sales Time Region Sales lag_sales 00/01/01 関東 3250 0 00/02/01 関東 3400 3250 00/03/01 関東 2050 3400 00/04/01 関東 1500 2050 00/05/01 関東 5000 1500 00/06/01 関東 2000 5000 00/01/01 東海 5000 0 00/02/01 東海 5000 5000 00/03/01 東海 8000 5000 00/04/01 東海 4050 8000 LAG 関数位置の前の任意のオフセットの行にアクセス LEAD 関数位置の後の任意のオフセットの行にアクセス Lag(<name>,<offset>,<default>) <name>: 表示したいデータ <offset>: 何行前のデータを表示するか <default>:<offset> が表 or パーティションの境界外となる場合に戻される値 LAG/LEAD LAG/LEAD LAG LEAD LAG LAG - Name sales - Offset 1 sales - Default offset sales 0 0 over region time Oracle Corporation Japan 27
分析関数 分析関数 (LAG/LEAD 関数 ) 内部結合を用いることなく 表の複数行への同時アクセス表の複数行への同時アクセスが可能 地域別に売上の前月差額を表示する例 select time,region,sales, lag(sales,1,0) over (partition by region order by time) as lag_sales, (sales - lag_sales) as sales_change from emp ; Time Region Sales lag_sales sales_change 00/01/01 関東 3250 0 3250 00/02/01 関東 3400 3250 150 00/03/01 関東 2050 3400-1350 00/04/01 関東 1500 2050-550 00/05/01 関東 5000 1500 3500 00/06/01 関東 2000 5000-3000 00/01/01 東海 5000 0 5000 00/02/01 東海 5000 5000 0 00/03/01 東海 8000 5000 3000 00/04/01 東海 4050 8000-3950 LAG/LEAD LAG LAG/LEAD Oracle Corporation Japan 28
4. 累計を計算する! 口座毎の残高を表示する Time Cname Amount Balance 00/01/01 Scott 3250 3250 00/02/01 Scott 3400 6650 00/03/01 Scott -2050 4600 00/04/01 Scott -1500 3100 00/05/01 Scott 5000 8100 00/06/01 Scott -2000 6100 オラクル銀行横浜支店 Scott 様 2000 年 6 月 1 日支払お取扱金額 2,000 残高 6,100 SQL PL/SQL PL/SQL Oracle Corporation Japan 29
分析関数 ( ウィンドウ関数 ) 分析関数 ウィンドウとカレント行の仕組みを用いて累計集計 移動集計 集中集計を計算 口座毎の残高を表示する例 ( 累計集計 ) select time,cname,amount, sum(amount) over (partition by cname order by time rows unbounded preceding) as balance from cust ; Time Cname Amount Balance 00/01/01 Scott 3250 3250 00/02/01 Scott 3400 6650 00/03/01 Scott -2050 4600 00/04/01 Scott -1500 3100 00/05/01 Scott 5000 8100 00/06/01 Scott -2000 6100 00/01/01 Tom 5000 5000 00/02/01 Tom 5000 10000 00/03/01 Tom -8000 2000 00/04/01 Tom 4050 6050 ウィンドウ関数 sum,avg,max,min,count,first_value,last_value rows: 物理ウィンドウ指定 ( 行単位で指定 ) range: 論理ウィンドウ指定 ( 論理間隔で指定 ) unbounded preceding: パーティションの最初の行からウィンドウ開始 unbounded following: パーティションの最後の行でウィンドウ終了 sum(amount) amount over cname time - - - Oracle Corporation Japan 30
( 参考 ) ウィンドウとカレント行について Ename Gender hiredate Salary smith 女性 99/02/01 5000 allen 女性 99/03/01 3250 ward 女性 99/06/01 2000 jones 女性 99/06/01 2050 martin 女性 99/10/01 1500 blake 女性 99/11/01 3400 clark 男性 00/02/01 5000 scott 男性 00/02/01 4050 king 男性 00/03/01 8000 turner 男性 00/04/01 2500 adams 男性 00/06/01 1000 ウィンドウ カレント行の計算に使用される行の範囲 カレント行 分析関数を使用した計算が実行される行のこと sum smith jones - jones - smith jones - sum smith jones jones Oracle Corporation Japan 31
( 参考 ) ウィンドウとカレント行について ウィンドウ開始点 Ename Gender hiredate Salary smith 女性 99/02/01 5000 allen 女性 99/03/01 3250 ward 女性 99/06/01 2000 jones 女性 99/06/01 2050 martin 女性 99/10/01 1500 blake 女性 99/11/01 3400 clark 男性 00/02/01 5000 scott 男性 00/02/01 4050 king 男性 00/03/01 8000 turner 男性 00/04/01 2500 adams 男性 00/06/01 1000 unbounded preceding: パーティションの最初の行からウィンドウ開始 ウィンドウ終了点 unbounded preceding unbounded following Oracle Corporation Japan 32
( 参考 ) 累計計算に対するウィンドウの設定 Ename Gender hiredate Salary smith 女性 99/02/01 5000 allen 女性 99/03/01 3250 ward 女性 99/06/01 2000 jones 女性 99/06/01 2050 martin 女性 99/10/01 1500 blake 女性 99/11/01 3400 clark 男性 00/02/01 5000 scott 男性 00/02/01 4050 king 男性 00/03/01 8000 turner 男性 00/04/01 2500 adams 男性 00/06/01 1000 ウィンドウ開始点 ウィンドウ終了点 累計合計関数に対するウィンドウの場合 開始点はパーティションの最初の行に固定終了点はパーティションの最初の行から最後の行までスライド sum sum - - - Oracle Corporation Japan 33
5. 移動平均を計算する 売上推移 ( 前後 1 ヶ月移動平均 ) Scott 売上推移 売上金額 移動平均 2000 年 1 月 1 日 3250 3325 2000 年 2 月 1 日 3400 2900 2000 年 3 月 1 日 2050 2316.67 2000 年 4 月 1 日 1500 2850 2000 年 4 月 1 日 5000 2833.33 2000 年 5 月 1 日 2000 4000 2000 年 6 月 1 日 5000 3500! 前後一ヶ月の移動平均を用いて売上推移を調査する Scott SQL PL/SQL PL/SQL Oracle Corporation Japan 34
分析関数 ( ウィンドウ関数 ) 分析関数 物理ウィンドウを使用した前後一ヶ月の売上移動平均の例 select time,ename,sales, avg(sales) over (partition by ename order by time rows between 1 preceding and 1 following) as balance from emp ; Time Ename Sales Balance 00/01/01 Scott 3250 3325 00/02/01 Scott 3400 2900 00/03/01 Scott 2050 2316.67 00/04/01 Scott 1500 2850 00/04/01 Scott 5000 2833.33 00/05/01 Scott 2000 4000 00/06/01 Scott 5000 3500 rows: 物理ウィンドウ指定 ( 行単位で指定 ) between and : ウィンドウの開始点と終了点を指定 preceding/ following : カレント行を基準としたウィンドウの開始点 ( 終了点 ) を指定 rowsによる行単位の指定または order by 句の後が number 型の場合 preceding/followingの前に数値を入れる avg avg(sales) sales over ename time - - - rows between 1 preceding and 1 following 1 preceding 1 following Oracle Corporation Japan 35
分析関数 ( ウィンドウ関数 ) 分析関数 論理ウィンドウを使用した前後一ヶ月の売上移動平均の例 select time,ename,sales, avg(sales) over (partition by ename order by time range between interval 1 month preceding and interval 1 month following) as balance from emp ; Time Ename Sales Balance 00/01/01 Scott 3250 3325 00/02/01 Scott 3400 2900 00/03/01 Scott 2050 2987.5 00/04/01 Scott 1500 2637.5 00/04/01 Scott 5000 2637.5 00/05/01 Scott 2000 3375 00/06/01 Scott 5000 3500 range: 論理ウィンドウ指定 ( 論理間隔で指定 ) between and : ウィンドウの開始点と終了点を指定 preceding/ following : カレント行を基準としたウィンドウの開始点 ( 終了点 ) を指定 order by 句の後が date 型の場合 preceding/following の前にインターバル ( 期間 ) を入れる range between interval 1 month preceding and interval 1 following order by date preceding/following day,month,year. preceding/following 00/03/01 Oracle Corporation Japan 36
( 参考 ) 物理ウィンドウと論理ウィンドウについて 物理ウィンドウ行数で範囲指定 rows between 1 preceding and 1 following 前後 1 行を含めた 3 行がウィンドウ Time Ename Sales Balance 00/01/01 Scott 3250 3325 00/02/01 Scott 3400 2900 00/03/01 Scott 2050 2316.67 00/04/01 Scott 1500 2850 00/04/01 Scott 5000 2833.33 00/05/01 Scott 2000 4000 00/06/01 Scott 5000 3500 カレント行ウィンドウ 00/04/01 Oracle Corporation Japan 37
( 参考 ) 物理ウィンドウと論理ウィンドウについて 論理ウィンドウ時間などの論理間隔で指定 range between interval 1 month preceding and interval 1 month following 前後一ヶ月を含めた 4 行がウィンドウ Time Ename Sales Balance 00/01/01 Scott 3250 3325 00/02/01 Scott 3400 2900 00/03/01 Scott 2050 2316.67 00/04/01 Scott 1500 2850 00/04/01 Scott 5000 2833.33 00/05/01 Scott 2000 4000 00/06/01 Scott 5000 3500 カレント行ウィンドウ 00/04/01 Oracle Corporation Japan 38
( 参考 ) 移動平均計算に対するウィンドウの設定 Time Ename Sales Balance 00/01/01 Scott 3250 3325 00/02/01 Scott 3400 2900 00/03/01 Scott 2050 2316.67 00/04/01 Scott 1500 2850 00/04/01 Scott 5000 2833.33 00/05/01 Scott 2000 4000 00/06/01 Scott 5000 3500 ウィンドウ開始点ウィンドウ終了点 移動平均に対するウィンドウの場合 開始点および終了点の両方がスライド一定の物理範囲 or 論理範囲が維持される avg avg - - - Oracle Corporation Japan 39
6. 構成比を計算する 売上総計 (1 月 ~8 月 ) Scott 売上総計 売上金額 構成比 00/01/01 3250 0.091 00/02/01 3400 0.095 00/03/01 2050 0.057 00/04/01 5000 0.14 00/05/01 5000 0.14 00/06/01 5000 0.14 00/07/01 8000 0.224 00/08/01 4050 0.113! 売上金額の構成比を表示する Scott Oracle Corporation Japan 40
分析関数 ( レポート関数 ) 分析関数 パーティション内における集計 平均 カウントを計算 売上総計を表示する例 select time,sum(sales) sales, sum(sum(sales)) over () as total_sales from emp group by time ; Time Sales Total_sales 00/01/01 3250 35750 00/02/01 3400 35750 00/03/01 2050 35750 00/04/01 5000 35750 00/05/01 5000 35750 00/06/01 5000 35750 00/07/01 8000 35750 00/08/01 4050 35750 LAG/LEAD sum,avg,count,max,min sum sales total_sales Oracle Corporation Japan 41
分析関数 ( レポート関数 ) 分析関数 パーティション内における集計 平均 カウントを計算 売上総計に対する値の構成比を表示する例 select time,sum(sales) sales, sum(sum(sales)) over () as total_sales, ratio_to_report(sum(sales)) over () as ratio_to_report from emp group by time ; Time Sales Total_sales Ratio_to_report 00/01/01 3250 35750 0.091 00/02/01 3400 35750 0.095 00/03/01 2050 35750 0.057 00/04/01 5000 35750 0.14 00/05/01 5000 35750 0.14 00/06/01 5000 35750 0.14 00/07/01 8000 35750 0.224 00/08/01 4050 35750 0.113 Ratio_to_report 関数値の集合の合計に対する値の割合 ( 構成比 ) を計算 ratio_to_report Oracle Corporation Japan 42
( 参考 ) 分析関数の処理順序について 全ての結合 WHERE/GROUP BY/HAVING 句 結果セット パーティション作成分析関数の適用 結合 集計 WHERE 句の処理が行われた後にカテゴリー分けを行い 分析関数が適用される ORDER BY 句の処理 問い合わせに ORDER BY 句がある場合は最後に行うことで正確な順序付けが行われる Oracle SQL WHERE GROUP BY HAVING ORDER BY Oracle Corporation Japan 43
( 参考 ) 分析関数の処理順序について 売上総計に対する値の構成比を表示する例 ( 構成比の降順ソート ) select time,sum(sales) sales, sum(sum(sales)) over () as total_sales, ratio_to_report(sum(sales)) over () as ratio_to_report from emp group by time order by ratio_to_report desc ; Time Sales Total_sales Ratio_to_report 00/07/01 8000 35750 0.224 00/04/01 5000 35750 0.14 00/05/01 5000 35750 0.14 00/06/01 5000 35750 0.14 00/08/01 4050 35750 0.113 00/02/01 3400 35750 0.095 00/01/01 3250 35750 0.091 00/03/01 2050 35750 0.057 SQL ratio_to_report SQL Oracle Corporation Japan 44
( 参考 ) 分析関数の処理順序について 売上総計に対する値の構成比を表示する例 ( 構成比の降順ソート ) select time,sum(sales) sales, sum(sum(sales)) over () as total_sales, ratio_to_report(sum(sales)) over () as ratio_to_report from emp group by time order by ratio_to_report desc ; 実行計画 Execution Plan 3. 最後の ORDER BY 0 SELECT STATEMENT Optimizer=CHOOSE 1 0 SORT (ORDER BY) 2. 分析関数処理 2 1 WINDOW (BUFFER) 3 2 SORT (GROUP BY) 4 3 TABLE ACCESS (FULL) OF EMP 1. GROUP BY 処理 GROUP BY ORDER BY Oracle Corporation Japan 45
Oracle Oracle8 Oracle8i SQL*Plus Oracle Corporation Japan 46