[2] データ操作 アドバンストSQL編 2-1．共通表式 ＷＩＴＨ句 2-2．再帰的ＳＱＬ

DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
アドバンストSQL編
[2] データ操作
お断り：当資料は、DB2 UDB V7.1（AIX,NT,OS2)　をベースに作成されています。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
[2] データ操作
2-1．共通表式 ＷＩＴＨ句
2-2．再帰的ＳＱＬ
2-3．ＯＬＡＰ
2-4．ＵＮＩＯＮ・ＪＯＩＮ view経由の更新 (参考）
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 1-2 )
＜第１．００版＞２００１年２月
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
[2] データ操作
2-1. 共通表形式
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
内容
1．共通表式 ＷＩＴＨ句とは
２．共通表式 ＷＩＴＨ句の例
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 3-4 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
１．共通表式 ＷＩＴＨ句とは
共通表式 ＷＩＴＨ句とは
SQL文の中で何度も参照できるローカル一時表
共通表式が参照されるたびに同じ結果が得られる
参照されるたびに再処理されない
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： １．共通表式 ＷＩＴＨ句とは
SELECT文の結果も「表」なので、表名を書くところにはSELECT文をそのまま書くことができるという概念を「表式」といいます。
同じ表を１個のSELECT文中で何度も参照したい場合に、一度だけ記述すればいいようにWITH句が導入されました。
共通表式は、必要なだけ何度でも使用できます。ただし、その共通表式を定義しているSQL文の中でしか使えません。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 5-6 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
２．共通表式 ＷＩＴＨ句の例
WITH句を使用しない場合
販売実績表
地域名
月 製品名
売上額
------------------------------日本
11 ビデオ
100
日本
11 テレビ
40
日本
12 ビデオ
150
日本
12 テレビ
80
米国
11 ビデオ
70
米国
11 テレビ
60
米国
12 ビデオ
120
米国
12 テレビ
90
SELECT S.地域名, S.月, S.製品名, S.売上額
CAST(S.売上額 AS DECIMAL(5,2))*100/T, 合計売上額 as 売上比率(%)
FROM 販売実績表 S,
(SELECT 地域名, SUM(売上額) FROM 販売実績表 GROUP BY 地域名)
AS T(地域名, 合計売上額)
WHERE S.地域名 = T.地域名
ORDER BY S.地域名, S.月, S.製品名;
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： ２．共通表式 ＷＩＴＨ句の例
WITH句を使用しない場合
SELECT文のFROM句の中にもSELECT文を書くことができます。（表式）
販売実績表から地域ごとに月・製品別売上額のその地域の売上に占める比率（百分率）を求めています。
比率を計算するための分母（その地域の売上額合計）をFROM句中のSELECT文で計算していることに注目してください。
問題点
結合や副照会などのように同じ表を１個のSELECT文で何度も参照したい場合は、やはりその回数分だけ繰り返し書かなけ
ればならないという煩雑さがあります。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 7-8 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
２．共通表式 ＷＩＴＨ句の例（続き）
販売実績表
地域名
月 製品名
売上額
------------------------------日本
11 ビデオ
100
日本
11 テレビ
40
日本
12 ビデオ
150
日本
12 テレビ
80
米国
11 ビデオ
70
米国
11 テレビ
60
米国
12 ビデオ
120
米国
12 テレビ
90
②
①
WITH 地域別合計売上額表(地域名, 合計売上額) AS
(SELECT 地域名, SUM(売上額) FROM 販売実績表 GROUP BY 地域名)
SELECT S.地域名, S.月, S.製品名, S.売上額
CAST(S.売上額 AS DECIMAL(5,2))*100/T, 合計売上額 AS 売上比率(%)
FROM 販売実績表 S, 地域別合計売上額表 T
WHERE S.地域名 = T.地域名
ORDER BY S.地域名, S.月, S.製品名;
地域名
月 製品名
売上額
売上比率(%)
-------------------------------------------日本
11 ビデオ
100
27.02
日本
11 テレビ
40
10.81
日本
12 ビデオ
150
40.54
日本
12 テレビ
80
21.62
米国
11 ビデオ
70
20.58
米国
11 テレビ
60
17.64
米国
12 ビデオ
120
35.29
米国
12 テレビ
90
26.47
②
①
100/370 =27.02%
地域別合計売上額表
地域名
合計売上額
-------------------日本
370
米国
340
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： ２．共通表式 ＷＩＴＨ句の例（続き）
前の例をWITH句を使って書き換えました。
WITH句には、このSQL文だけに有効な（一時的な）表の名前と、それに続く括弧の中には同様の列名を指定します。
WITH句のSELECT文が先に実行されて結果行が一時的に名前をつけた表に保管されます。それが新たな表として最後の
SELECT文で参照できると考えます。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 9-10 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
[2] データ操作
2-2. 再帰SQL
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
内容
1．再帰的ＳＱＬとは
２．再帰的ＳＱＬの例（部品構成表）
２－１．再帰的SQLを使用しない場合
２－２．単一レベルの展開
２－３．合計型展開
２－４．深さの制御
３．再帰的ＳＱＬの例（航空経路）
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 11-12 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
1. 再帰的SQLとは
再帰的SQLとは
SQL文が結果セットを繰り返し使用して、さらに結果を導出する場合に使用
自分自身を参照する共通表式を使用
階層式ツリー、航空路線の経路、部品表のようなタイプの照会に効果的
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： 1．再帰的SQLとは
再帰的SQLとは
SQL文が結果セットを繰り返し使用して、さらに結果を導出する場合に使用します。
自分自身を参照する共通表式を使用します。
階層式ツリー、航空路線の経路、部品表のようなタイプの照会に効果的です。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 13-14 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
２．再帰的SQLの例（部品構成表）
部品構成表
親品番
子品番
所要数
------------------------------自転車
ハンドル
1
自転車
フレーム
1
自転車
車輪
2
自転車
ブレーキ
2
ハンドル
グリップ
2
ハンドル
留め具
4
フレーム
サドル
1
フレーム
ペダル
2
車輪
スポーク
10
ペダル
留め具
3
自転車
ハンドル
１個
グリップ
２個
留め具
４個
フレーム
１個
サドル
１個
ペダル
２個
ブレーキ
２個
車輪
２個
スポーク
１０個
留め具
３個
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： ２．再帰的SQLの例（部品構成表）
製造業のアプリケーションで使われる表に部品構成表があります。
ある製品がどのような部品がいくつで構成されているかを表していて、生産や部品調達の計画を立てるときなどに使われます。
部品構成表で注目すべき点は、部品が別の部品を子部品として持ち、さらにその子部品が別の子部品を持っているかもしれず、
その部品構成の深さ（構成レベルとも言う）は固定ではなく、製品や部品によって可変です。
ここでは、ある自転車がフレーム、ハンドル、ブレーキをそれぞれ各１個と車輪２個から構成され、フレームはサドル１個とペダル２
個から構成されています。ペダルはさらに留め具を３個必要としている。ハンドルや車輪についてもそれぞれ子部品があります。
このようにペダルとハンドルに共通部品として使用されているので、１台の自転車を生産するのに共通部品の留め具が一体全体
で何個必要なのかが、部品構成表を見ただけではすぐにはわかりません。また例に挙げていないが、製品によっては構成展開
の深さがさらに深いかもしれません。
自転車を構成する全部品と各部品の所要数の一覧表を作成することを考えます。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 15-16 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
２－１．再帰的SQLを使用しない場合
再帰的SQLを使用しない場合
１
SELECT 親品番, 子品番, 所要数 FROM 部品構成表
WHERE 親品番 = '自転車';
２
SELECT 親品番, 子品番, 所要数 FROM 部品構成表
WHERE 親品番 = 'ハンドル';
結果行
親品番
子品番
所要数
-----------------------------------自転車
ハンドル
1
自転車
フレーム
1
自転車
ブレーキ
2
自転車
車輪
2
結果行
親品番
子品番
所要数
-----------------------------------ハンドル
グリップ
2
ハンドル
留め具
4
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： ２－１．再帰的SQLを使用しない場合
再帰的SQLを使用しない場合
１のように親品番の比較値に'自転車'を指定してSELECT文を実行します。
結果行の子品番は、'ハンドル'と 'フレーム'、'ブレーキ'、'車輪'です。
次に、'ハンドル'を同じ部品構成表の親品番の比較値に設定して２回目のSELECT文を実行します。（２）
今度の結果行の子品番は'グリップ'、'留め具'です。
次に'グリップ'を親品番の比較値に設定して３回目のSELECT文を実行します。この場合はこれ以上子品番がないので、'留め
具'を親品番とする子品番があるどうかの４回目のSELECT文を実行します。この場合も見つからないので、'ハンドル'の次の'
フレーム'を親品番とする子品番を探す５回目のSELECT文を実行します。
このようにして全部の子部品についてもうこれ以上その下に子部品がなくなるまで繰り返せば、全部品を取り出し、部品の所
要量を計算することができます。
問題点
部品の数だけSELECT文を発行しなければなりません。
構成の深さが一定でないのでどうしてもプログラムが複雑にならざるをえません。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 17-18 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
２－２．単一レベルの展開
単一レベルの展開
WITH 中間表(親品番, 子品番, 所要量) AS
(
SELECT root.親品番, root.子品番, root.所要量
FROM 部品構成表 root
WHERE root.親品番 = '自転車'
UNION ALL
SELECT child.親品番, child.子品番, child.所要量
FROM 中間表 parent, 部品構成表 child
WHERE parent.子品番 = child. 親品番
)
SELECT DISTINCT 親品番, 子品番, 所要量
FROM 中間表;
(1) 初回に、このSELECT文を１回だけ実行し
て、その結果行を中間表に保管する。つまり
レベル２の子部品が保管される
(2) 二回目に、(1)で保管した中間表と部品構
成表を結合するSELECT文を実行して、レベ
ル３の子部品を取り出す。
(3) その結果行を中間表に追加する。この処
理を子部品が見つからなくなるまで繰り返
す。
(4) 上記の(3)までにできた中間表を取り出
す。
結果行
親品番
子品番
所要数
-----------------------------------ハンドル
グリップ
2
ハンドル
留め具
4
フレーム
サドル
1
フレーム
ペダル
2
ペダル
留め具
3
自転車
ハンドル
1
自転車
フレーム
1
自転車
ブレーキ
2
自転車
車輪
2
車輪
スポーク
10
部品構成表
(3)
部品構成表
(1)
中間表
(2)
(4)
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： ２－２．単一レベルの展開
単一レベルの展開
これは「自転車を作成するにはどの部品が必要か?」という質問に答えるものです。
このリストには、直接の子部品、子部品の子部品、・・・が含まれます。しかし、ある部品が何回も使用される場合でも、その子
部品は 1 回しかリストに示されません。
UNION の第 1 オペランド (全選択) は 初期化全選択 と呼ばれるもので、それによって自転車 の直接の子が求まります。こ
の全選択の FROM 文節では元の表が参照されていますが、それ自身 (この場合は 中間表) を参照することはありません。こ
の最初の全選択の結果が、共通表式 中間表 (Recursive 部品構成表) の中に入れられることになります。この例の場合、
UNION は常に UNION ALL でなければなりません。
UNION の第 2 オペランド (全選択) は、中間表 を使って、子部品の子部品を計算しています。これは、FROM 文節で共通表
式 中間表 とソース・テーブル (CHILD: 子) の部品を 中間表 (PARENT: 親) に含まれている現行の結果の子部品に結び付け
ることによります。この結果は、再度 中間表 に入れられます。このようにして、UNION の第 2 オペランドは、子が存在しなくな
るまで繰り返し使用されます。
この照会の主要な全選択の SELECT DISTINCT では、同じ親部品/子部品が 2 回以上リストに現れることがないようにして
います。
再帰的共通表式では、 無限ループ になる可能性を必ず考慮に入れてください。この例で、親表と子表を結合する第 2 オペラ
ンドの探索条件を以下のようにコーディングしたとすると、無限ループが作成されることになります。
PARENT.子品番 = CHILD.子品番
無限ループが発生するこの例は、意図したとおりにコーディングされていない場合であることは明らかです。しかし、再帰サイ
クルが必ず終了するようにコーディングすることにも注意を払ってください。
この例の照会によって得られる結果は、再帰的共通表式を使用しなくても、アプリケーション・プログラム内で作成することが
できます。しかし、そのような方法では、すべての再帰レベルごとに新しい照会を開始する必要があります。さらに、すべての
結果をデータベースに入れ、その結果を並べ替えることを、アプリケーションで行う必要があります。そのような方法では、アプ
リケーションのロジックが複雑になり、パフォーマンスはよくありません。合計型展開やインデント型展開の照会など、その他
の部品構成表の照会では、アプリケーションのロジックがさらに複雑で効率の悪いものとなってしまいます。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 19-20 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
２－３．合計型展開
合計型展開
WITH 中間表(親品番, 子品番, 所要量) AS
(
SELECT root.親品番, root.子品番, root.所要量
FROM 部品構成表 root
WHERE root.親品番 = '自転車'
UNION ALL
SELECT child.親品番, child.子品番, parent.所要量*child.所要量
FROM 中間表 parent, 部品構成表 child
WHERE parent.子品番 = child. 親品番
)
SELECT 親品番, 子品番, SUM(所要量) AS "部品全体所要量"
FROM 中間表
GROUP BY 親品番, 子品番
親部品の個数と直属の子部品の個数を乗じ
ORDER BY 親品番
て、子部品の個数を求める。２個のペダル
結果行
親品番
-------ハンドル
ハンドル
フレーム
フレーム
ペダル
自転車
自転車
自転車
自転車
車輪
子品番
部品全体所要量
-------- -------------グリップ
2
留め具
4
サドル
1
ペダル
2
留め具
6
ハンドル
1
フレーム
1
ブレーキ
2
車輪
2
スポーク
20
に、それぞれ３個の留め具が付いているの
で、合計2*3=6個の留め具が必要である。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： ２－３．合計型展開
合計型展開
ここでの質問は、「自転車 の作成には各部品が合計どれくらい必要か」というものです。
単一レベル展開と異なる主な点は、数量を集計する必要があるということです。例 1 は、部品が必要になった時にそれに必
要な子部品の数量を示すものです。自転車 を作成するのに、子部品が結局どれだけ必要かは示されていません。
中間表 という名前で指定されている再帰的共通表式の中の UNION の第 2 オペランドの選択リストによって、数量の合計が
示されています。
子部品の使用量を求めるには、親の数量に、親 1 個当たりの子の数量を乗算します。 1 つの部品が異なる複数の場所で何
回も使用される場合は、もう 1 つ最終的な集計が必要になります。これは、共通表式 中間表 をグループ化し、主要全選択の
選択リストの中で SUM 列関数を使用することによって行います。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 21-22 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
２－４．深さの制御
深さの制御
WITH 中間表(level, 親品番, 子品番, 所要量) AS
(SELECT 1,root.親品番, root.子品番, root.所要量
FROM 部品構成表 root WHERE root.親品番 = '自転車'
UNION ALL
SELECT parent.level+1, child.親品番, child.子品番, child.所要量
FROM 中間表 parent,部品構成表 child
WHERE parent.子品番 = child. 親品番 AND parent.level < 2
)
SELECT 親品番, level, 子品番, 所要量
FROM 中間表
親のレベルに 1 ずつ加算
親のレベルが 2 未満が条件
結果行
親品番
LEVEL
-------- ----------自転車
1
自転車
1
自転車
1
自転車
1
ハンドル
2
ハンドル
2
フレーム
2
フレーム
2
車輪
2
子品番
所要量
-------- ----------ハンドル
1
フレーム
1
車輪
2
ブレーキ
2
グリップ
2
留め具
4
サドル
1
ペダル
2
スポーク
10
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： ２－４．深さの制御
深さの制御
この表の中に存在する部品のレベルが、とりあえず照会で必要なレベルより深い場合はどうなるのでしょうか。つまり、
「自転車を作成するために必要な部品の最初の 2 つのレベルはどんなものか?」という質問に答えるためには、
例をわかりやすいものにするため、レベル番号を結果に含めることにします。
元の部品からのレベルを示すために、列 LEVEL を使っています。初期化全選択では、 LEVEL 列の値を 1 に初期化していま
す。それ以降の全選択では、親のレベルに 1 ずつ加算します。次に、結果のレベル数を制御するため、2 番目の全選択に、
親のレベルが 2 未満でなければならないという条件を含めています。これによって、2 番目の全選択では、子の処理が第 2
レベルまでしか行われないことになります。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 23-24 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
３．再帰的SQLの例（航空経路）
航空経路の例
航空便表
出発地
目的地
費用
--------------------------------------パリ
ニューヨーク
6
パリ
デンバー
9
パリ
ダラス
8
ニューヨーク
シカゴ
2
シカゴ
サンフランシスコ
4
デンバー
サンフランシスコ
4
ダラス
ニューヨーク
3
ダラス
サンフランシスコ
6
再帰的
ＳＱＬ
パリからサンフランシ
スコまでの最少費用
の経路を調べたい
シカゴ
ニュー
ヨーク
4
サンフ
ランシ
スコ
2
6
3
4
デン
バー
6
結果行
経路
合計費用 乗継回数
--------------------------------------------------------------パリ.ニューヨーク.シカゴ.サンフランシスコ
12
2
パリ.デンバー.サンフランシスコ
12
2
パリ.ダラス.サンフランシスコ
12
2
パリ.ダラス.ニューヨーク.シカゴ.サンフランシスコ
12
2
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
ブランク・ページです
AdvSQL-2 ( 25-26 )
ダラス
9
パリ
8
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
[2] データ操作
2-3. OLAP機能
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
内容
１．ＯＬＡＰとは
２．SQLのＯＬＡＰ拡張
３．グループ化関数
3-1. GROUPING SETS
3-2. ROLLUP
3-3. CUBE
４．OLAP関数
4-1. ランキング関数
RANK()
DENSE_RANK()
4-2. ナンバリング関数
ROW_NUMBER()
4-3. 集計関数
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 27-28 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
１．ＯＬＡＰとは
ＯＬＡＰ（On-Line Analytical Processing）とは
多次元データベースをオンラインで分析する処理
営業分析／戦略立案／意思決定支援
非定型／複雑な検索
多次元データベース
製品
乗用車
販売実績表
月
1
1
1
1
2
2
2
2
3
3
3
3
地域名
日本
日本
米国
米国
日本
日本
米国
米国
日本
日本
米国
米国
製品名
乗用車
バイク
乗用車
バイク
乗用車
バイク
乗用車
バイク
乗用車
バイク
乗用車
バイク
トラック
売上
3290
2134
1611
3210
1392
2350
1100
2011
3980
3109
2212
3410
バイク
日本
米国
欧州
アジア
販売地域
全体
1
時間軸次元
販売地域軸次元
2
3
Q1
時間
製品軸次元
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： １．ＯＬＡＰとは
ＯＬＡＰ（On-Line Analytical Processing）とは
多次元データベースをオンラインで分析する処理です。
日々の大量の販売実績データから販売傾向を分析する、これによって効果的な販売戦略を立案する、など意思決定支援に
応用されています。
OLAPでは一般に、OLTP処理とは異なりデータの更新はほとんどなく、複雑かつ非定型な検索が多いのが特徴であるとされ
ています。
多次元データベース
データを分析するときの切り口が次元です。
たとえば、電機会社の販売分析を考えてみましょう。月ごと、製品ごと、地域ごとなどの角度からデータを分析したいとします。
これらの月（時間）、製品、販売地域が分析の軸、すなわち次元になります。
OLAP分析用のデータはこれらの軸を持った多面体と考えることができます。これを多次元データベースまたはキューブと呼
びます。
OLAPでは次元の軸に沿った分析（たとえば月別の売上）、軸と軸の組み合わせによる（たとえば月別、地域別の売上）集計な
どを簡単に行うことができます。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 29-30 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
2. SQLのOLAP拡張
RDBでOLAP機能をサポートするための拡張
グループ化関数
GROUPING SETS
ROLLUP
CUBE
OLAP関数
ランキング関数
RANK()
DENSE_RANK()
ナンバリング関数
ROW_NUMBER()
集計関数
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： SQLのOLAP拡張
以上のようなOLAP分析をRDBでも行いやすくするために、SQL99にOLAP拡張機能が追加されました。
UDBでもこれに合わせてV6.1およびV7.1でOLAP機能の拡張を行っています。
OLAP拡張には大きく分けてグループ化関数とOLAP関数があります。
グループ化関数
グループの組み合わせによってデータを集計するための機能です。
GROUP BY節と組み合わせて使われます。
以下の３つがあります。
GROUPING SETS
ROLLUP
CUBE
OLAP関数
指定された値により順位をつけたり、行番号をつけたり、指定された範囲のデータに対して演算を施したりするために使われ
る機能です。
以下の３つがあります。
ランキング関数
ナンバリング関数
集計関数
これらの関数には処理範囲を指定するためのウィンドウ・パーティション節、ウィンドウ順序節などを付けます。
集計関数はUDB V7.1から、それ以外のOLAP関数はUDB V6.1から使用できます。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 31-32 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
3. グループ化関数
3つの節
GROUPING SETS
ROLLUP
CUBE
複数の軸(次元)にそって値を集計
小計、中計、大計などのレベルごとの集計
GROUP BY節で使用
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： 3. グループ化関数
グループ化関数はOLAPの多次元分析を行うため、複数の軸（次元）にそってデータを集計するために使用します。
グループ化の単位、範囲を指定するために以下の３つの節があります。
GROUPING SETS
ROLLUP
CUBE
これらの節をGROUP BY節と合わせて使うことによって、データをさまざまなグループの組み合わせ、小計、中計、大計などさまざ
まなレベルで集計することができます。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 33-34 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
3-1. GROUPING SETS
GROUPING SETS
異なるグループ基準に基づいて集計データを生成
GROUPING SETS文節をGROUP BY文節と組み合わせて使用
SELECT 月, 地域名, 製品名, Sum(売上) AS 売上合計額
FROM 販売実績表
GROUP BY GROUPING SETS((月, 製品名), (製品名, 地域名), ())
ORDER BY 月, 地域名
GROUPING SETSの結果行
月
地域名
------- ------------1 1 2 2 3 3 - 日本
- 日本
- 米国
- 米国
- -
製品名
売上合計
---------------- ----------バイク
5344
乗用車
4901
バイク
4361
乗用車
2492
バイク
6519
乗用車
6192
バイク
7593
乗用車
8662
バイク
8631
乗用車
4923
29809
月、製品単位に合計
地域、製品単位に合計
全体の合計
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： 3-1. GROUPING SETS
GROUP BY句のGROUPING SETSオプションに特定の集計単位を指定することによって、指定したグループごとの集計をすること
ができます。
このSELECT文は、月・製品別の合計売上額と製品・地域別の合計売上額と全体の売上合計額の３種類を求めています。
GROUPING SETSの括弧中の最後の'()'は、全体の集計を意味しています。
結果行のカラムの値がNULL(-)のものは集約結果の行です。
たとえば、地域名がNULLのものは地域の合計です。
月名、地域名、製品名すべてがNULLのものは全体の合計です。
集計単位が次にご紹介するROLLUPでは少なすぎるが、CUBEでは多過ぎる場合、特定の組み合わせでのみ集計したい場合に
使用します。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 35-36 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
3-2. ROLLUP
ROLLUP
スーパーグループ化された集計（大計、中計、小計）を生成
GROUP BY文節と組み合わせて使用
ROLLUPする順序が重要
SELECT 月, 地域名, 製品名, Sum(売上) AS 売上合計額
FROM 販売実績表
GROUP BY ROLLUP(月, 地域名, 製品名)
ORDER BY 月, 地域名, 製品名
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
ブランク・ページです
AdvSQL-2 ( 37-38 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
３-2．ROLLUP（続き）
ROLLUPの結果行
月
----------1
1
1
1
月、地域単位に合計
1
1
1
2
2
2
2
2
2
月単位に合計
2
3
3
3
3
3
3
3
全部を合計
-
地域名
製品名
売上合計
-------------------- -------------------- ----------日本
バイク
2134
日本
乗用車
3290
日本
5424
米国
バイク
3210
米国
乗用車
1611
米国
4821
10245
日本
バイク
2350
日本
乗用車
1392
日本
3742
米国
バイク
2011
米国
乗用車
1100
米国
3111
6853
日本
バイク
3109
日本
乗用車
3980
日本
7089
米国
バイク
3410
米国
乗用車
2212
米国
5622
12711
29809
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： 3-2．ROLLUP
ROLLUP句はGROUP BY節と組み合わせて使用されます。
ROLLUPを指定すると、一般のGROUP BYで表示される列に加えROLLUPの()で指定した単位での小計の列が追加表示されま
す。
月、地域、製品別に、この順番で売上額を集約して分析したい場合、GROUP BY節にROLLUPオプションを指定します。
結果行の列値がNULLである列が集約された列であることを示しています。例えば、
製品名列の値がNULLになっている行の合計売上額は、月・地域別の合計売上額を示します。
地域名列と製品名列がNULLになっている行の合計売上額は、月別の合計売上額を示します。
ROLLUPはGROUPING SETSの組み合わせとして書くことができます。
たとえば、以下の二つの表現は同じ結果をもたらします。
GROUP BY ROLLUP(月, 地域名, 製品名)
GROUP BY GROUPING SETS( (月, 地域名, 製品名)
(月,地域名)
(月),
() )
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 39-40 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
3-3. CUBE
CUBE
グループの全ての組み合わせを入手し、順列して、そのリストで可能な全てのグループを計
算
データのCubeを構築するのに必要な全てのデータを生成
GROUPING SETSを使用して全てのグループを表現したものと同じ
GROUP BY節と組み合わせて使用
SELECT 月, 地域名, 製品名, SUM(売上) AS 売上合計
FROM 販売実績表
GROUP BY CUBE(月, 地域名, 製品名)
ORDER BY 月, 地域名, 製品名
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
ブランク・ページです
AdvSQL-2 ( 41-42 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
3-3. CUBE（続き）
CUBEの結果行
CUBEによって追加された結
果行
・月・製品別
CUBEによって追加された結
果行
・月・地域別
CUBEによって追加された結
果行
・地域・製品別
CUBEによって追加された結
果行
・製品別
月
-------1
1
1
1
1
1
1
1
1
2
2
2
2
2
2
2
2
2
3
3
3
3
3
3
3
3
3
-
地域名
-------------------日本
日本
日本
米国
米国
米国
日本
日本
日本
米国
米国
米国
日本
日本
日本
米国
米国
米国
日本
日本
日本
米国
米国
米国
-
製品名
売上合計
-------------------- ----------バイク
2134
乗用車
3290
5424
バイク
3210
乗用車
1611
4821
バイク
5344
乗用車
4901
10245
バイク
2350
乗用車
1392
3742
バイク
2011
乗用車
1100
3111
バイク
4361
乗用車
2492
6853
バイク
3109
乗用車
3980
7089
バイク
3410
乗用車
2212
5622
バイク
6519
乗用車
6192
12711
バイク
7593
乗用車
8662
16255
バイク
8631
乗用車
4923
13554
バイク
16224
乗用車
13585
29809
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： 3-3. CUBE
CUBEは地域・製品別、製品・月別といった違った組み合わせの集約を行いたい場合に使用されます。
月・地域・製品の全ての組み合わせの集計を１回のSELECT文によって得ることができます。
CUBEPはGROUPING SETSの組み合わせとして書くことができます。
たとえば、以下の二つの表現は同じ結果をもたらします。
GROUP BY CUBE(月, 地域名, 製品名)
GROUP BY GROUPING SETS( (月, 地域名, 製品名)
(月, 地域名)
(月, 製品名)
(地域名, 製品名)
(月)
(地域名)
(製品名)
() )
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 43-44 )
CUBEによって
追加された結果行
・月別
CUBEによって
追加された結果行
・地域別
総計
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
4. OLAP関数
指定された範囲"ウィンドウ"に対し演算を行い結果を返す
ランキング関数
行中の指定された値に応じて順位をつける
RANK()
DENSE_RANK()
ナンバリング関数
行に番号をふる
ROW_NUMBER()
集計関数
既存のカラム関数を範囲指定で実施する
SELECTリストまたはSELECT文のORDER BY節中に指定する
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： 4. OLAP関数
OLAP関数は指定された範囲に対し演算を行いスカラー値の結果を返します。
OLAP関数の演算を適用する範囲をウィンドウと呼びます。
以下の３種類の関数があります。
ランキング関数
行中の指定された値に応じて順位をつけます。以下の２つの関数があります。
RANK()
DENSE_RANK()
ナンバリング関数
検索結果行に番号をふります。
ROW_NUMBER()
集計関数
AVG, CORRELATION, COUNT, COVARIANCE, MAX, MIN, SUM, STDDEV, VARIANCEなど, 既存のカラム関数を範囲指
定で実施します。
演算範囲としてカレント行に対する相対的な範囲を指定することにより、移動演算を行うことができます。
これらの関数はSELECTリストまたはSELECT文のORDER BY節中に指定します。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 45-46 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
4-1. ランキング関数
構文
RANK()
DENSE_RANK()
OVER (
| window-order-clause | )
| window-partition-clause |
売上の上位から順位をつける(地域、製品にこだわらない）
SELECT 月, 地域名, 製品名, 売上,
RANK() OVER ( ORDER BY 売上 DESC) AS 売上順位
FROM 販売実績表
ORDER BY 月, 地域名, 製品名
RANK()の結果行
月
-------1
1
1
1
2
2
2
2
3
3
3
3
地域名
----------------日本
日本
米国
米国
日本
日本
米国
米国
日本
日本
米国
米国
製品名
売上
売上順位
-------------------- ----------- --------------バイク
2134
8
乗用車
3290
3
バイク
3210
4
乗用車
1611
10
バイク
2350
6
乗用車
1392
11
バイク
2011
9
乗用車
1100
12
バイク
3109
5
乗用車
3980
1
バイク
3410
2
乗用車
2212
7
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： 4-1. ランキング関数
ランキング関数にはRANK(), DENSE_RANK()の二つがあります。構文は上に示したとおりです。
ランキング関数はSELECTリストまたはORDER BY文節の中で使われ、OVERキーワードに続けて演算範囲(ウィンドウ)の指定を
行います。
windows-partition-clauseでは PARTITION BY を使ってどの範囲のランキングを行うかを指定します。
windows-order-clauseでは ORDER BY を使ってどの値に基づきどの順序でランキングするかを指定します。
ランキング順序の指定はASC（昇順）、DESC（降順)があります。
上の例では、ランキング範囲の指定(windows-partiotion-clause)がないので、表全体に対して売上の値でランキングしています。
ランク順に出力するためには以下の方法をとります。
最後のORDER BY (ORDER BY 月, 地域名, 製品名) を指定しない。
最後に'ORDER BY 売上順位'と指定する。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 47-48 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
4-1. ランキング関数(つづき)
地域別に売上の上位から順位をつける
SELECT 月, 地域名, 製品名, 売上,
RANK() OVER ( PARTITION BY 地域名 ORDER BY 売上 DESC) AS 売上順位
FROM 販売実績表
ORDER BY 地域名, 月, 製品名
RANK()の結果行
月
-----1
1
2
2
3
3
1
1
2
2
3
3
地域名
------------日本
日本
日本
日本
日本
日本
米国
米国
米国
米国
米国
米国
製品名
売上
売上順位
-------------------- ----------- --------------バイク
2134
5
乗用車
3290
2
バイク
2350
4
乗用車
1392
6
バイク
3109
3
乗用車
3980
1
バイク
3210
2
乗用車
1611
5
バイク
2011
4
乗用車
1100
6
バイク
3410
1
乗用車
2212
3
日本の中での順位
米国の中での順位
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： 4-1. ランキング関数(つづき)
RANK()関数でランキング範囲の指定(windows-partiotion-clause)を行うと、指定した範囲ごとにランキングします。
例では, windows-partition-clauseとして'PARTITION BY 地域名'を指定して、地域ごと(日本,米国別)にランキングしています。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 49-50 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
4-1. ランキング関数 - RANK()とDENSE_RANK()
RANK()
同じ値があると同一順位になる
次のレコード順位は同一順レコードの数だけとぶ
SELECT 名前, 体重, RANK() OVER ( ORDER BY 体重 ASC) AS 順位
FROM 体重表
RANK()の結果行
名前
体重 順位
-------------------- ------ ---------名波　浩
68
1
中山　雅史
72
2
城　彰二
72
2
川口　能活
75
4
秋田　豊
78
5
DENSE_RANK()
同じ値のレコードがあった後の順位の番号はとばない
SELECT 名前, 体重, DENSE_RANK() OVER ( ORDER BY 体重 ASC) AS 順位
FROM 体重表
DENSE_RANK()の結果行
名前
体重 順位
-------------------- ------ ---------名波　浩
68
1
中山　雅史
72
2
城　彰二
72
2
川口　能活
75
3
秋田　豊
78
4
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： 4-1. ランキング関数 - RANK()とDENSE_RANK()
RANK()とDENSE_RANK()では、同一順位のレコードが出現した後の順位のふり方に違いがあります。
RANK()では同一順位のレコードの後の順位は、重なったレコードの個数だけとばされます。
DENSE_RANK()では、同一順位のレコードがあっても順位の番号はとびません。
DENSE_RANK()はDENSERANK()と書くこともできます。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 51-52 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
4-2. ナンバリング関数
構文
ROW_NUMBER()
OVER (
| window-order-clause | )
| window-partition-clause |
照会結果に行番号をふる
SELECT ROW_NUMBER() OVER ( ORDER BY 売上 DESC) AS 番号,
月, 地域名, 製品名, 売上
FROM 販売実績表
RANK()の結果行
番号
月
------------ --------1
3
2
3
3
1
4
1
5
3
6
2
7
3
8
1
9
2
10
1
11
2
12
2
地域名
-------------------日本
米国
日本
米国
日本
日本
米国
日本
米国
米国
日本
米国
製品名
売上
-------------------- ----------乗用車
3980
バイク
3410
乗用車
3290
バイク
3210
バイク
3109
バイク
2350
乗用車
2212
バイク
2134
バイク
2011
乗用車
1611
乗用車
1392
乗用車
1100
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： 4-2. ナンバリング関数
ナンバリング関数ROW_NUMBER()は照会結果に行番号をふります。
構文、範囲(ウィンドウ)指定のためのwindow-partition-clause, window-order-clauseの使い方は、RANK()と同じです。
ROW_NUMBER()はROWNUMBER()と書くこともできます。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 53-54 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
4-3. 集計関数
構文
カラム関数
OVER (
| window-order-clause |
| window-partition-clause |
RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
)
| window-aggregation-group-clause |
カラム関数をOVER以下に指定した範囲に対して実施する
windows-aggregation-group-clauseを利用してカレント行を基準にした
範囲を指定して移動演算が実行できる
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： 4-3. 集計関数
集計関数は既存のカラム関数の適用範囲を指定できるようにするものです。
カラム関数をOVER以下に指定した範囲に対して実施します。
集計範囲(ウィンドウ）の指定は、ランキング関数、ナンバリング関数と同じwindow-partition-clause, window-order-clauseに
加え、window-aggregation-group-clauseで行います。
window-aggregation-group-clauseでは演算範囲としてROW(行)またはRANGE（キーの範囲)に基づく指定ができます。
とくにカレント行を基準に範囲を指定した移動演算が実行できます。
移動演算は演算対象範囲をずらしながら演算していくもので、代表的なものに移動平均があります。
window-aggregation-clauseの指定方法の詳細は「SQL解説書」をご覧ください。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 55-56 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
4-3. 集計関数例 - 移動平均
細かい変動のあるデータの長期傾向を把握したい場合
ある時点の値をその時点の前後一定範囲の平均で代表させる
例
百貨店の月次売上高
季節変動が激しい　例：12月はよく売れる、2月は売上が少ない
3月の売上は前月に比べて上昇しているが傾向として本当に売上が伸びていると言えるのか？
12ヶ月移動平均で季節変動を除去する
株価
日々の値動きが激しい
現在は上昇局面なのか下降局面なのか？
7日間移動平均で傾向を掴む
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： 4-3. 集計関数例 - 移動平均
移動平均は細かい変動のあるデータの長期傾向を把握したい場合に使用します。
移動平均ではある時点の値をそのまま使うのではなく、その時点の前後一定範囲の平均に代表させます。
たとえば、次のようなデータがあったとします。
売上
1月
２月
３月
４月
５月
６月
７月
８月
９月
１０月
１１月
１２月
52
28
35
40
45
41
62
55
47
44
45
59
変動が大きいので傾向を掴むために3ヶ月移動平均をとることにします。
3ヶ月移動平均では一般に、ある月のデータとしてその月のデータと前後１ヶ月づつ合計3ヶ月分のデータを平均してその月の値
とします。たとえば、２月のデータとしては生データの28を使わずに、1月の52, 2月の28, 3月の35の三か月分を平均した値38を使
います。同様に、３月のデータとしては２,３,４月の平均をとります。
このようにすると、変動がならされて傾向値がつかめます。
使用例
百貨店の月次売上高
百貨店の売上は季節変動が激しいので、3月の売上は前月に比べて上昇しているが傾向として本当に売上が伸びている
と言えるのか知るためには12ヶ月移動平均で季節変動を除去します。（季節変動の場合は一般に12ヶ月移動平均をとり
ます。）
株価
日々の値動きが激しいが現在は上昇局面なのか下降局面なのか知りたいという場合は、複数日（たとえば7日間）移動平
均で傾向を掴みます。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 57-58 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
4-3. 集計関数 - 移動平均例
移動平均使用例
完全失業者数12ヶ月移動平均
一般に４月が多く１２月にかけて少なくなるという季節変動
季節変動の影響を除去
SELECT 月, 人数,
AVG(人数) OVER (ORDER BY 月 ROWS BETWEEN 6 PRECEDING AND 5 FOLLOWING)
AS 移動平均値
FROM 失業者
AVG()の結果行
月
人数
移動平均値
---------- ----------- ----------1999-01-01
298
305
1999-02-01
313
310
1999-03-01
339
311
1999-04-01
342
313
1999-05-01
334
315
1999-06-01
329
315
1999-07-01
319
317
1999-08-01
320
318
1999-09-01
317
319
1999-10-01
311
320
1999-11-01
295
320
1999-12-01
288
319
:
:
:
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： 4-3. 集計関数 - 移動平均例
完全失業者数
完全失業者数には一般に４月が多く１２月にかけて少なくなるという季節変動があります。この季節変動の影響を除去するため
12ヶ月移動平均をとります。
SQL文は例のとおりです。
この例では12ヶ月を取るのに先行6ヶ月、後続5ヶ月の計12ヶ月を使っています。
UDBは先頭からすべての列に対し移動平均値を返しますが、先行６か月分のレコードがない月のデータは統計的に移動平均
として意味がないことに注意してください。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 59-60 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
4-3. 集計関数 - 移動平均例(つづき)
移動平均結果
400
350
300
250
200
150
100
50
0
完全失業者数
移動平均値
Ja
n9
Ap 8
r9
Ju 8
l9
O 8
ct
-9
Ja 8
n9
Ap 9
r9
Ju 9
l-9
Oc 9
t9
Ja 9
n0
Ap 0
r0
Ju 0
l-0
Oc 0
t0
Ja 0
n01
人数(万）
完全失業者数(総務省統計局労働力調査より）
月
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： 4-3. 集計関数 - 移動平均例(つづき)
例で使用したデータをグラフ化すると上のようになります。
季節変動がならされてなだらかな曲線になっていることがわかります。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 61-62 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
4-3. 集計関数 - 累計
累計
集計関数の範囲指定を使って、カレント行までの値を累計する
使用例
あるイベントの累積入場者数を出す
SELECT 日, 入場者数,
SUM(入場者数) OVER (ORDER BY 日
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)
AS 累積入場者数
FROM 入場者
SUM()の結果行
日
入場者数
累積入場者数
---------- ----------- -----------2001-03-01
5821
5821
2001-03-02
4230
10051
2001-03-03
4998
15049
2001-03-04
5532
20581
2001-03-05
3416
23997
2001-03-06
3692
27689
2001-03-07
3307
30996
2001-03-08
3253
34249
2001-03-09
3194
37443
2001-03-10
4830
42273
2001-03-11
5271
47544
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： 4-3. 集計関数 - 累計
集計関数の範囲指定を使って、カレント行までの値を累計することができます。
使用例では、あるイベントの累積入場者数を出しています。
累計なので、範囲指定の始まりをUNBOUNDED PRECEDINGと指定しています。
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROWは次のように書くこともできます。
ROWS UNBOUNDED PRECEDING
終わりを指定しないときはカレント行までの演算となります。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 63-64 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
[2] データ操作
2-4. （参考）
UNION/JOIN View経由の更新
当項目は、SQL99で規定されたUNION/JOIN View経由の更新について紹介
するもので、UDB V7ではまだ当項目で紹介する機能のすべてがサポート
されている訳ではありません。
現在UDB V6，V7で可能なのは、UNION ALLを使用したVIEW経由でのUPDATE
とDELETEのみです。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
内容
１．ＵＮＩＯＮ・ＪＯＩＮ経由の更新
２．UNION経由の更新の例
３．JOIN経由の更新の例
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 65-66 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
１．UNION・ＪＯＩＮ経由の更新
UNION経由の更新
UNIONを含むビューにおいて更新が可能
但し UDB V6，V7では使用上の制約あり
JOIN経由の更新
JOINを含むビューにおいて更新が可能
但し UDB V7ではまだサポートされていません。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： １．UNION・JOIN経由の更新
SQL99では、これまでUNION、JOINを含むビューは、参照のみの操作だけが許されていたが、更新が可能となりました。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 67-68 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
２．UNION経由の更新の例
UNION経由のUPDATE文の例
社員表
社員氏名 社員名前 職種
--------------------------田中
花子
プログラマ
鈴木
愛
アナリスト
高橋
美香
経理事務
CREATE VIEW 名簿表（氏名, 名前） AS
SELECT 社員氏名, 社員名前
FROM 社員表
UNION ALL
SELECT 会員氏名, 会員名前
FROM 同好会会員表;
同好会会員表
会員氏名 会員名前 趣味
----------------------田中
花子
読書
鈴木
愛
映画
山崎
陽子
音楽
名簿表（UNIONの結果行）
氏名
名前
---------------田中
花子
鈴木
愛
高橋
美香
山崎
陽子
社員表
社員氏名 社員名前 職種
--------------------------山田
花子
プログラマ
鈴木
愛
アナリスト
高橋
美香
経理事務
UPDATE 名簿表
SET 氏名 = '山田'
WHERE 氏名 = '田中' AND 名前 = '花子';
同好会会員表
会員氏名 会員名前 趣味
----------------------山田
花子
読書
鈴木
愛
映画
山崎
陽子
音楽
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： ２．UNION経由の更新の例
名簿表は、社員表と同好会会員表を併合（UNION）したビューであり、田中一郎さんの「氏名」を更新する必要があるとします。
ビューである名簿表に対してUPDATE文を実行すれば、ビューの定義元の実表である社員表と同好会会員表のそれぞれの対応
する列値が１回の操作で更新することができます。
なお、UDB V6、V7レベルでは UNION ALLで作成したVIEWに対するUPDATEとDELETEのみ使用可能です。
UNION ALLの場合のINSERT、またUNIONの場合のUPDATE,DELETE,INSERTは使用不可です。
SQLCODE = -150 のエラーになります。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 69-70 )
DB2 UDB(PC & Unix)デザイン・ガイド
別冊 アドバンストSQL編
2．データ操作
３．JOIN経由の更新の例
JOIN経由のINSERT文の例
社員表
社員氏名 社員名前 職種
--------------------------田中
花子
プログラマ
鈴木
愛
アナリスト
高橋
美香
経理事務
同好会会員表
会員氏名 会員名前 趣味
----------------------田中
花子
読書
鈴木
愛
映画
山崎
陽子
音楽
CREATE VIEW 名簿表2 AS
SELECT *
FROM 社員表
INNER JOIN 同好会会員表
ON 社員氏名 = 会員氏名 AND 社員名前 = 会員名前
WHERE 趣味 = '読書'
WITH CHECK OPTION;
INSERT INTO 名簿表2
VALUES(T中村','良子','総務','中村','良子','読書');
名簿表2（JOINの結果行）
社員氏名
社員名前
職種
会員氏名
会員名前
趣味
-----------------------------------------------------------------田中
花子
プログラマ
田中
花子
読書
社員氏名
社員名前
職種
会員氏名
会員名前
趣味
-----------------------------------------------------------------田中
花子
プログラマ
田中
花子
読書
中村
良子
総務
中村
良子
読書
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
解説： ３．JOIN経由の更新の例
本来は一つの表であるべきものが、何らかの理由で、複数の表に物理的に分割しなければならない場合があります。
表を分割した場合、それまで作成したSELECT文はUNIONやJOINを含んだビューを利用して、分割されていることをある程度、見
えないようにすることができるが、UNIONやJOINを含んだビューを通しての更新はできなかったので、結局SQL文を変更せざるを
えないという大きな影響がありました。
UNIONやJOINを含むビューが更新可能になったために、これ以上に柔軟な表の設計を行うことができるようになりました。
なお、UDB V6,V7レベルではJOIN経由の更新（UPDATE,DELETE,INSERT）はサポートされていません。
SQLCODE = -150 のエラーになります。
(C)日本IBMｼｽﾃﾑｽﾞ･ｴﾝｼﾞﾆｱﾘﾝｸﾞ(株) ﾃﾞｰﾀｼｽﾃﾑ部
AdvSQL-2 ( 71-72 )