目次 1 集計関数 / 分析関数とは 2 集計関数 / 分析関数のパフォーマンス効果 3 ケーススタディグループ小計やクロス集計を計算するランキングを表示する前月比較を表示する累計を計算する移動平均を計算する構成比を計算する Oracle8i SQL Oracle8i Oracle Oracle C

Size: px

Start display at page:

Download "目次 1 集計関数 / 分析関数とは 2 集計関数 / 分析関数のパフォーマンス効果 3 ケーススタディグループ小計やクロス集計を計算するランキングを表示する前月比較を表示する累計を計算する移動平均を計算する構成比を計算する Oracle8i SQL Oracle8i Oracle Oracle C"

なごみくだら
5 years ago
Views:

1 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

2 目次 1 集計関数 / 分析関数とは 2 集計関数 / 分析関数のパフォーマンス効果 3 ケーススタディグループ小計やクロス集計を計算するランキングを表示する前月比較を表示する累計を計算する移動平均を計算する構成比を計算する Oracle8i SQL Oracle8i Oracle Oracle Corporation Japan 2

3 集計関数 / 分析関数とは DWH システムの分析要件で必要とされる計算ランキング移動平均累計計算 etc 今までの SQL 言語では高度なプログラミングによるインプリが必要ヒストグラム前年同月比 :PL/SQL : ファンクションランキング :rownum : サブトータル :union : 検索パフォーマンスが劣化高度なインプリ知識が必要 DWH Business Intelligence BI etc Oracle8i DB Oracle Corporation Japan 3

4 集計関数 / 分析関数とは集計関数 (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

5 集計関数 / 分析関数のパフォーマンス効果例 ) 売上 Top5 ランキング Emp(14300 行 ) Dept(4 行 ) Deptno Ename Gender Salary comm Deptno Dname 10 smith 女性エンジニア 10 blake 女性マーケティング 20 jones 女性 2050 : : 10 king 男性 adams 男性 : : : : : 給与トップ 5 ランキング 1 位 king エンジニア位 smith エンジニア位 adams マーケティング位 clack マーケティング位 scott エンジニア 4050 Emp Dept Oracle Corporation Japan 5

6 集計関数 / 分析関数のパフォーマンス効果 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

7 集計関数 / 分析関数のパフォーマンス効果実行計画 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

8 集計関数 / 分析関数のパフォーマンス効果パフォーマンス統計 Statistics recursive calls db block gets consistent gets 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

9 集計関数 / 分析関数のパフォーマンス効果 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

10 集計関数 / 分析関数のパフォーマンス効果実行計画 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

11 集計関数 / 分析関数のパフォーマンス効果パフォーマンス統計 ( 分析関数使用 ) 分析関数不使用 Statistics recursive calls 6 db block gets 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 経過 :09:57.70: 経過 :00:00.91 : 経過 : 比較アクセスブロック数が減少しているソート処理の回数が少なくて済むパフォーマンスが改善されている DB BLOCK GETS + CONSISTENT GETS SORT(MEMORY) + SORT(DISK) EMP Oracle Corporation Japan 11

12 ケーススタディ 1. グループ小計やクロス集計を計算する ROLLUP/CUBE ( 集計関数 ) 2. ランキングを表示するランキング関数 ( 分析関数 ) 3. 前月比較を表示する LAG/LEAD 関数 4. 累計を計算する関数 ( 分析関数 ) ウィンドウ関数 ( 分析関数 ) 5. 移動平均を計算するウィンドウ関数 ( 分析関数 ) 6. 構成比を計算するレポート関数 ( 分析関数 ) BI Oracle Corporation Japan 12

13 1. グループ小計やクロス集計を計算する! 性別職種ごとのグループ小計! 性別職種のクロス集計 Gender Dname Ename SUM(Salary) 女性エンジニア smith 2000 女性マーケティング allen 3000 女性エンジニア ward 5000 女性マーケティング jones 1500 女性 : : : 女性 : : : 男性マーケティング scott 男性エンジニア king 女性 : : : 女性 : : : 女性総計 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

14 集計関数従来の GROUP BY 句から拡張された演算子 ROLLUP 指定した集計軸の並びに沿ったサブトータル及び総合計を返す CUBE 指定した集計軸のクロス集計及び総合計を返す Gender Dname エンジニアマーケティング小計男性女性小計 GROUP BY GROUP BY ROLLUP Oracle Corporation Japan 14

15 集計関数従来の GROUP BY 句から拡張された演算子 ROLLUP 指定した集計軸の並びに沿ったサブトータル及び総合計を返す CUBE 指定した集計軸のクロス集計及び総合計を返す Gender Dname エンジニアマーケティング小計男性女性小計 CUBE Oracle Corporation Japan 15

16 集計関数 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 男性引数 : グループ化列の順序づけリスト 1 右左の順にレベル付け第 1 レベル :dname : 第 2 レベル :gender : 2 レベル順に小計を作成 - Gender に対するそれぞれの Dname にわたって集計 - 各 Genderに対して全ての Dname にわたって集計 - 総計を計算 SQL SQL SQL Group by rollup rollup dname gender Rollup Oracle Corporation Japan 16

17 集計関数 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 マーケティング Genderに対する全ての Dname にわたって集計 - 各 Genderに対して全ての Dname にわたって集計 - 各 Dnameに対して全ての Gender にわたって集計 - 総計を計算 Group by cube rollup cube dname gender cube Oracle Corporation Japan 17

18 参考 :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 女性エンジニア女性マーケティング女性男性エンジニア男性マーケティング男性エンジニアマーケティング GROUPING NULL Oracle grouping NULL grouping SQL where grouping(gender) = 1 Oracle Corporation Japan 18

19 参考 : 集計関数に関する 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

20 2. ランキングを表示する! 総合給与ランキング! 総合給与ランキング ( 同順位の場合はインセンティブでランク付け )! 性別給与ランキング ( 同順位の場合はインセンティブでランク付け ) 9 月度総合給与ランキング 1 位 king 位 smith 位 scott 位 adams 5 位 clark 6 位 allen 7 位 turner 8 位 jones 9 位 blake 10 位 martin 9 月度給与ランキング ( 男性 ) 1 位 king 位 scottt 位 adams 5300 ( 女性 ) 1 位 smith 位 allen 位 jones 4600 rownum rank Oracle Corporation Japan 20

21 分析関数 ( ランキング関数 ) 分析関数データセットのレコードに対しレコードのランクレコードのランクを計算する総合給与ランキング select ename,gender,salary,comm, rank () over (order by sal desc) as rank_sal from emp ; Ename Gender Salary comm rank_sal king 男性 smith 女性 adams 男性 clark 男性 scott 男性 blake 女性 allen 女性 turner 男性 jones 女性 ward 女性 martin 女性 rank over - order by ASC/DESC sal Oracle Corporation Japan 21

22 分析関数 ( ランキング関数 ) 分析関数データセットのレコードに対しレコードのランクレコードのランクを計算する総合給与ランキング ( 同順位の場合はインセンティブでランク付け ) 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 男性 smith 女性 adams 男性 clark 男性 scott 男性 blake 女性 allen 女性 turner 男性 jones 女性 ward 女性 martin 女性最初の式で同じ値が解決できない 2 番目の式を用いて解決 sal DESC comm DESC Oracle Corporation Japan 22

23 分析関数 ( ランキング関数 ) データセットのレコードに対しレコードのランクレコードのランクを計算する性別給与ランキング ( 同順位の場合はインセンティブでランク付け ) 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 女性 blake 女性 allen 女性 jones 女性 ward 女性 martin 女性 king 男性 adams 男性 clark 男性 scott 男性 turner 男性性別によってカテゴリー分けカテゴリー別にランク表示 partition by オプションによって rank が操作するグループにデータセットが分割されるグループが変更されるたびランクがリセットされる - partition by. partition by gender Oracle Corporation Japan 23

24 ( 参考 ) 結果セットパーティションについて 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

25 ( 参考 ) 結果セットパーティションについて select ename,gender,salary, max(salary) over(partition by gender) as max_sal from emp Ename Gender Salary max_sal smith 女性 allen 女性 ward 女性 jones 女性 martin 女性 blake 女性 clark 男性 scott 男性 king 男性 turner 男性 adams 男性結果セットパーティション問い合わせ結果セットを分析関数によって分割した行グループのこと Oracleの表のパーティションとは異なることに注意する over partition by. gender max max max Oracle Corporation Japan 25

26 3. 前月比較を表示する! 地域別売上金額の前月との差分を表示する 2000 年上半期地区別売上推移売上金額前月との差額 ( 関東 ) 1 月月月月月月 ( 東海 ) 1 月 : : : : LAG LEAD Oracle Corporation Japan 26

27 分析関数分析関数 (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 関東 /02/01 関東 /03/01 関東 /04/01 関東 /05/01 関東 /06/01 関東 /01/01 東海 /02/01 東海 /03/01 東海 /04/01 東海 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

28 分析関数分析関数 (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 関東 /02/01 関東 /03/01 関東 /04/01 関東 /05/01 関東 /06/01 関東 /01/01 東海 /02/01 東海 /03/01 東海 /04/01 東海 LAG/LEAD LAG LAG/LEAD Oracle Corporation Japan 28

29 4. 累計を計算する! 口座毎の残高を表示する Time Cname Amount Balance 00/01/01 Scott /02/01 Scott /03/01 Scott /04/01 Scott /05/01 Scott /06/01 Scott オラクル銀行横浜支店 Scott 様 2000 年 6 月 1 日支払お取扱金額 2,000 残高 6,100 SQL PL/SQL PL/SQL Oracle Corporation Japan 29

30 分析関数 ( ウィンドウ関数 ) 分析関数ウィンドウとカレント行の仕組みを用いて累計集計移動集計集中集計を計算口座毎の残高を表示する例 ( 累計集計 ) 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 /02/01 Scott /03/01 Scott /04/01 Scott /05/01 Scott /06/01 Scott /01/01 Tom /02/01 Tom /03/01 Tom /04/01 Tom ウィンドウ関数 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

31 ( 参考 ) ウィンドウとカレント行について Ename Gender hiredate Salary smith 女性 99/02/ allen 女性 99/03/ ward 女性 99/06/ jones 女性 99/06/ martin 女性 99/10/ blake 女性 99/11/ clark 男性 00/02/ scott 男性 00/02/ king 男性 00/03/ turner 男性 00/04/ adams 男性 00/06/ ウィンドウカレント行の計算に使用される行の範囲カレント行分析関数を使用した計算が実行される行のこと sum smith jones - jones - smith jones - sum smith jones jones Oracle Corporation Japan 31

32 ( 参考 ) ウィンドウとカレント行についてウィンドウ開始点 Ename Gender hiredate Salary smith 女性 99/02/ allen 女性 99/03/ ward 女性 99/06/ jones 女性 99/06/ martin 女性 99/10/ blake 女性 99/11/ clark 男性 00/02/ scott 男性 00/02/ king 男性 00/03/ turner 男性 00/04/ adams 男性 00/06/ unbounded preceding: パーティションの最初の行からウィンドウ開始ウィンドウ終了点 unbounded preceding unbounded following Oracle Corporation Japan 32

33 ( 参考 ) 累計計算に対するウィンドウの設定 Ename Gender hiredate Salary smith 女性 99/02/ allen 女性 99/03/ ward 女性 99/06/ jones 女性 99/06/ martin 女性 99/10/ blake 女性 99/11/ clark 男性 00/02/ scott 男性 00/02/ king 男性 00/03/ turner 男性 00/04/ adams 男性 00/06/ ウィンドウ開始点ウィンドウ終了点累計合計関数に対するウィンドウの場合開始点はパーティションの最初の行に固定終了点はパーティションの最初の行から最後の行までスライド sum sum Oracle Corporation Japan 33

5. 移動平均を計算する売上推移 ( 前後 1 ヶ月移動平均 ) Scott 売上推移売上金額移動平均 2000 年 1 月 1 日 3250 3325 2000 年 2 月 1 日 3400 2900 2000 年 3 月 1 日 2050 2316.

34 5. 移動平均を計算する売上推移 ( 前後 1 ヶ月移動平均 ) Scott 売上推移売上金額移動平均 2000 年 1 月 1 日年 2 月 1 日年 3 月 1 日年 4 月 1 日年 4 月 1 日年 5 月 1 日年 6 月 1 日 ! 前後一ヶ月の移動平均を用いて売上推移を調査する Scott SQL PL/SQL PL/SQL Oracle Corporation Japan 34

35 分析関数 ( ウィンドウ関数 ) 分析関数物理ウィンドウを使用した前後一ヶ月の売上移動平均の例 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 /02/01 Scott /03/01 Scott /04/01 Scott /04/01 Scott /05/01 Scott /06/01 Scott 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

36 分析関数 ( ウィンドウ関数 ) 分析関数論理ウィンドウを使用した前後一ヶ月の売上移動平均の例 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 /02/01 Scott /03/01 Scott /04/01 Scott /04/01 Scott /05/01 Scott /06/01 Scott 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

37 ( 参考 ) 物理ウィンドウと論理ウィンドウについて物理ウィンドウ行数で範囲指定 rows between 1 preceding and 1 following 前後 1 行を含めた 3 行がウィンドウ Time Ename Sales Balance 00/01/01 Scott /02/01 Scott /03/01 Scott /04/01 Scott /04/01 Scott /05/01 Scott /06/01 Scott カレント行ウィンドウ 00/04/01 Oracle Corporation Japan 37

38 ( 参考 ) 物理ウィンドウと論理ウィンドウについて論理ウィンドウ時間などの論理間隔で指定 range between interval 1 month preceding and interval 1 month following 前後一ヶ月を含めた 4 行がウィンドウ Time Ename Sales Balance 00/01/01 Scott /02/01 Scott /03/01 Scott /04/01 Scott /04/01 Scott /05/01 Scott /06/01 Scott カレント行ウィンドウ 00/04/01 Oracle Corporation Japan 38

39 ( 参考 ) 移動平均計算に対するウィンドウの設定 Time Ename Sales Balance 00/01/01 Scott /02/01 Scott /03/01 Scott /04/01 Scott /04/01 Scott /05/01 Scott /06/01 Scott ウィンドウ開始点ウィンドウ終了点移動平均に対するウィンドウの場合開始点および終了点の両方がスライド一定の物理範囲 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.

40 6. 構成比を計算する売上総計 (1 月 ~8 月 ) Scott 売上総計売上金額構成比 00/01/ /02/ /03/ /04/ /05/ /06/ /07/ /08/ ! 売上金額の構成比を表示する Scott Oracle Corporation Japan 40

41 分析関数 ( レポート関数 ) 分析関数パーティション内における集計平均カウントを計算売上総計を表示する例 select time,sum(sales) sales, sum(sum(sales)) over () as total_sales from emp group by time ; Time Sales Total_sales 00/01/ /02/ /03/ /04/ /05/ /06/ /07/ /08/ LAG/LEAD sum,avg,count,max,min sum sales total_sales Oracle Corporation Japan 41

42 分析関数 ( レポート関数 ) 分析関数パーティション内における集計平均カウントを計算売上総計に対する値の構成比を表示する例 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/ /02/ /03/ /04/ /05/ /06/ /07/ /08/ Ratio_to_report 関数値の集合の合計に対する値の割合 ( 構成比 ) を計算 ratio_to_report Oracle Corporation Japan 42

43 ( 参考 ) 分析関数の処理順序について全ての結合 WHERE/GROUP BY/HAVING 句結果セットパーティション作成分析関数の適用結合集計 WHERE 句の処理が行われた後にカテゴリー分けを行い分析関数が適用される ORDER BY 句の処理問い合わせに ORDER BY 句がある場合は最後に行うことで正確な順序付けが行われる Oracle SQL WHERE GROUP BY HAVING ORDER BY Oracle Corporation Japan 43

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 ; Time Sales Total_sales Ratio_to_report 00/07/ /04/ /05/ /06/ /08/ /02/ /01/ /03/ SQL ratio_to_report SQL Oracle Corporation Japan 44

45 ( 参考 ) 分析関数の処理順序について売上総計に対する値の構成比を表示する例 ( 構成比の降順ソート ) 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

46 Oracle Oracle8 Oracle8i SQL*Plus Oracle Corporation Japan 46

第 2 章問合せの基本操作この章ではデータベースから情報を検索する際に使用する SELECT コマンドおよび SELECT コマンドと同時に使用する句について説明します 1. 問合せとは 2. 基本的な問合せ 3. 列の別名 4. 重複行を一意にする 5. 検索行の絞込み 6. 文字パター

第 2 章問合せの基本操作この章ではデータベースから情報を検索する際に使用する SELECT コマンドおよび SELECT コマンドと同時に使用する句について説明します 1. 問合せとは 2. 基本的な問合せ 3. 列の別名 4. 重複行を一意にする 5. 検索行の絞込み 6. 文字パターはじめにコース概要と目的データベース処理に使用する SQL の基本構文と使用方法について説明します受講対象者 SQL を使用してアプリケーション開発される方管理者となられる方前提条件 Oracle 概要コースを受講された方もしくは同等の知識をお持ちの方テキスト内の記述について構文 [ ] 省略可能 { A B } A または B のどちらかを選択 n _ 数値の指定デフォルト値