Oracle8iR8.1.6 の分析関数

Oracle8iR8.1.6 の分析関数
Oracle テクニカル・ホワイト・ペーパー
1999 年 10 月
はじめに
SQL 言語は多くの分野できわめて有能ですが、分析タスクに対するサポートは強力でありません。移動平均、ラ
ンク付け、リード/ラグ比較などの基本的なビジネス・インテリジェンス計算は、標準 SQL 範囲外のパフォーマン
ス上の課題をしばしば伴う幅広いプログラミングを必要とします。Oracle8i R8.1.6 は、こうした長年のニーズに
対応するパワフルな新しい SQL 関数群を提供しています。これらの関数は、あらゆるタイプの分析に有益なため、
"分析関数"と総称されています。分析関数は、パフォーマンスの向上と、開発作業の効率化をもたらします。さら
に、これらの新しい関数は、2000 年の SQL 標準に追加されるものとして、現在 ANSI により検討されています。
4 つの分析関数群
Oracle は、それぞれ複数の関数で構成する 4 つの分析関数群を構築しました。
•
ランク付け関数群 − "各地域の売上で上位 10 人および下位 10 人の営業担当者は?" 、または"各地域で、
売上の 25%を占める営業担当者は?"というようなビジネス上の質問に応答します。各関数は、出力結果全
体をチェックしてから答えを返します。Oracle は、RANK、DENSE_RANK、PERCENT_RANK、CUME_DIST、
および NTILE というランク付け関数を提供しています。
•
ウィンドウ集約関数群 − "13 週間における株価の移動平均は?"、または"各地域の売上累積合計は?"のよう
な質問に対応します。この新機能は、AVG、SUM、MIN、MAX、COUNT、VARIANCE、および STDDEV
を含むすべての SQL 集約関数について、移動および累積処理を提供します。
•
レポート集約関数群 − 最も一般的なタイプの計算の 1 つは、非集約値を集約値と比較することです。合
計に対する値の比率やマーケット・シェアの計算はすべて、この処理を必要とします。レポート集約関数
群は、このような計算を単純にします。異なる集約レベルで計算された値を、同一の行に配置できるので
す。結合操作を必要としないため、集約値を詳細行と比較できるようになります。この新しい関数群は、
AVG、SUM、MIN、MAX、COUNT、VARIANCE、および STDDEV を含むすべての SQL 関数に ついて、
レポート集約処理を提供します。
•
ラグ／リード関数群 − 変動や差異の考察は、分析の中心的な作業です。当然ながら、これには、表内の
異なる行の値を比較する操作が関わります。これは、一般にセルフ結合を使用して SQL でも可能ですが、
効率的でなく、計算式の作成も簡単ではありません。ラグ/リード関数群は、現在の行からのオフセットを
指定するだけで、表内の異なる行を比較できるようにします。
分析関数は、意思決定支援処理用リレーショナル・データベース・プラットフォームのパワーを補足することを
目的としており、特殊な OLAP 環境の役割に代わるものではないことに注意してください。Oracle OLAP Server の
ような OLAP 製品を使用すれば、分析関数の能力を活用して問合せパフォーマンスを強化することができます。
利点
分析関数は、データベースのパフォーマンスと開発者の効率の両方を高めます。対話型意思決定支援からバッチ・
レポート・ジョブまで、あらゆるタイプの処理に有益です。社内の開発者と、独立したソフトウェア・ベンダー
の両方が、この機能の利点を活用できます。新しい関数が提供する主な利点を紹介しましょう。
•
問合せ速度の向上 − これらの関数がサポートする処理の最適化機能により、問合せのパフォーマンスが
飛躍的に向上します。これまではセルフ結合または複雑な手続き処理を必要としていた操作が、本来の SQL
Oracle8i のための分析関数
1999 年 10 月
2
Oracle Corporation 発行の
｢Analytic Functions for Oracle8i」
の翻訳版です。
で実行できるようになりました。新しい関数が実現するパフォーマンスの強化は、Oracle の MOLAP シス
テムおよび他の ROLAP 製品に対する問合せ速度を向上させます。
•
開発作業の効率化 − 新しい関数は、開発者が、より明解で簡潔な SQL コードを使用して複雑な分析を実
行できるようにします。以前は、複数の SQL 文または手続き言語の使用を必要としていたタスクが、1 つ
の SQL 文で表現できるようになりました。新しい SQL は、以前のアプローチよりも計算式の作成およびメ
ンテナンスが容易なため、生産性が大幅に向上します。
•
最小限の学習作業 − 構文が入念に設計された分析関数は、新しいキーワードを習得する必要を最小限に
します。この構文は、SUM や AVERAGE などの既存の集約関数を活用し、使い慣れたこれらのキーワード
を広範囲の用途に使用できます。
•
標準化された構文 − ANSI SQL 標準の一部である新しい関数は、独立したソフトウェア・ベンダーにとっ
て魅力的です。新しい関数を活用するように自社製品を修正する作業を促進します。Oracle は、問合せ製
品、レポート関連製品、および OLAP 製品の各ベンダーと協力し、分析関数の活用を支援しています。こ
れまで、複数のデータベース・ベンダーがこれらの関数と同じ分野で独自の拡張機能を提供してきました。
しかし、そのような拡張機能は、大きなマーケット・シェアを獲得するには至らず、自社の関数に修正を
加えたソフトウェア・ベンダーもわずかにすぎませんでした。これに対し、新しい分析関数は、多数の独
立したソフトウェア・ベンダーによってサポートされています。「参考」の 1. ANSI 提案書をご参照くださ
い。
コンセプト
分析関数は、その動作を実行するために、いくつかの新しい要素を SQL 処理に追加しています。これらの要素は
既存の SQL を基本としており、柔軟でパワフルな計算式が可能になります。以下に、分析関数に使用されている
重要なコンセプトを紹介します。
•
処理順序 − 分析関数を使用する問合せ処理は、3 つの段階で実行されます。第 1 段階では、すべての結合
と、WHERE 句、GROUP BY 句、および HAVING 句が実行されます。第 2 段階では、結果セットが分析関
数に適用され、すべての関数計算が実行されます。第 3 段階では、問合せの最後に ORDER BY 句があれば
ORDER BY が実行され、出力データが正しい順序でソートされます。図 1 に、この処理順序を示します。
結合、
WHERE 句、
GROUP BY 句、
および
HAVING 句
パーティションの作成、
各パーティションの
各行に分析関数を適用
最終的な
ORDER
BY
図 1：分析関数の処理順序
•
結果セットのパーティション − 分析関数では、パーティションと呼ばれる、順序が指定された行グルー
プに問合せ結果セットを分割することができます。分析関数で使用される"パーティション"という用語は、
Oracle の表パーティション機能とは無関係ですので注意してください。本書では、"パーティション"を分析
関数に関連した意味でのみ使用しています。パーティションは、GROUP BY 句で定義されたグループの後
Oracle8i のための分析関数
1999 年 10 月
3
Oracle Corporation 発行の
｢Analytic Functions for Oracle8i」
の翻訳版です。
に作成されるため、SUM や AVG などの集約結果をパーティションに適用できます。パーティションへの
分割は、任意の列または式に基づいて行うことができます。問合せ結果セットは、すべての行を収容する 1
つだけのパーティションで構成しても、少数の大きなパーティションや、それぞれ数行ずつの多数の小さ
なパーティションに分割してもかまいません。
•
ウィンドウ − 各パーティションについて、データのスライド式ウィンドウが定義されます。このウィン
ドウは、"現在行"（次の黒丸付き項で定義）に対する計算の実行に使用される行の範囲を決定します。ウィ
ンドウのサイズは、物理的な行数か、時間などの論理的な間隔に基づいて設定することができます。ウィ
ンドウには、開始行と終了行があります。ウィンドウには、その定義に応じて、一端だけが移動するもの
と、両端が移動するものがあります。たとえば、累積合計関数に定義されるウィンドウは、開始行がパー
ティションの先頭行に固定され、終了行は、開始ポイントからパーティションの最終行までスライドしま
す。これに対し、移動平均に定義されるウィンドウは、物理的または論理的範囲が一定になるように、開
始ポイントと終了ポイントの両方がスライドします。
ウィンドウのサイズは、最大でパーティション内のすべての行まで拡大できます。最小サイズは、パーティ
ション内の 2 行だけで構成するスライド式セットになります。ユーザーは、一定数の行が含まれるウィン
ドウ、または指定した範囲の列値を持つすべての行が含まれるウィンドウを指定できます。また、日付の
値が前月に該当するすべての行を収容するウィンドウを定義することもできます。
•
現在行 − 分析関数で実行される各計算は、ウィンドウ内の現在行に基づいて実行されます。現在行は、
ウィンドウの開始および終了を決定するリファレンス・ポイント（基準点）として機能します。たとえば、
中心移動平均計算には、現在行、その前の 5 行、およびその後の 6 行を収容ウィンドウを定義できます。
これにより、図 2 に示す 12 行のスライド式ウィンドウが作成されます。
パーティションの開始
ウィンドウの開始
現在行：ウィンドウのコンテンツに基づく計算
ウィンドウの終了
ウィンドウの
移動方向
パーティションの終了
図 2：12 行の中心移動平均用のスライド式ウィンドウによるパーティション
Oracle8i のための分析関数
1999 年 10 月
4
Oracle Corporation 発行の
｢Analytic Functions for Oracle8i」
の翻訳版です。
特長および用例
この節では、新しい関数の主な特長を説明し、基本的な用例を示します。本書は、各関数の内容および概念の記
述を目的としており、構文図は記載いたしません。ただし、新しい関数の価値を示すために、多数の実用的なケー
スは紹介します。用例のほとんどは、各行に詳細または集約売上データが収容されている sales 表からのデータが
関わっています。
ランク付け関数群
ランク付け関数群の関数は、メジャー・セットの値に基づいて、データセット内の他の行と対比させた各行のラ
ンクを計算します。分析処理の多くのニーズに対応するため、ランク付け関数群は、複数の異なる関数を提供し
ています。これらの関数すべてに、次の機能を使用することができます。
•
複数の式に基づいてデータをランク付けする機能。たとえば、売上、利益、および年功序列の組合せに基
づいてランク付けすることができます。その場合、データは、売上、利益、および年功序列を基準として
順序が決定され、その後でランクが割り当てられます。複数の式に基づくランク付け能力により、タイ（等
値）のランクを最小化します。
•
関数に使用する式ごとに、昇順または降順のランク付け順序を指定できます。
•
順序の方向（昇順か降順）を問わず、NULL を強制して、最後または最初にランク付けすることができま
す。
•
ランク関数は、各パーティションの境界でリセットされます。
•
ランクは、GROUP BY CUBE 句および ROLLUP 句によって生成されたグループ境界でリセットできます。
以下の節では、ランク付け関数群の各関数に関する要点を記載します。次に紹介する RANK 関数は、他の関数よ
りも詳細に説明されています。RANK について記述されたコンセプトは他の関数にも適用しますので、これ以降
は繰り返しません。
RANK 関数
RANK 関数は、各行のランクをランク 1 から順にソートして表示します。ユーザーは、オプションの PARTITION
句と、必須の ORDER BY 句を指定します。PARTITION キーワードは、ランク付けをリセットする位置を定義する
ために使用されます。ランク付けされる特定の列は、ORDER BY 句によって決定されます。パーティションを指
定しないと、結果セット全体に対してランク付けが実行されます。RANK は、降順が指定されていない限り、最
小値にランク 1 を割り当てます。次の例は、各地域の営業担当者を売上金額に基づいてランク付けします。
SELECT sales_person, sales_region, sales_amount,
RANK() OVER (PARTITION BY s_region ORDER BY s_amount DESC)
FROM Sales_table;
Oracle8i のための分析関数
1999 年 10 月
5
Oracle Corporation 発行の
｢Analytic Functions for Oracle8i」
の翻訳版です。
sales_person
sales_region
sales_amount
rank
Adams
Baker
Connors
Davis
Edwards
Fitzhugh
Gariabaldi
East
East
East
East
West
West
West
100
99
89
75
74
66
45
1
2
3
4
1
2
3
上表の"rank"列には、sales_amount の降順に営業担当者が表示され、sales_region が変化した時点でランクの値がリ
セットされています。
集約関数を使用したランク付け
この例では、問合せに GROUP BY 句が含まれており、各製品の売上合計に基づいてランク付けが行われます。
SELECT r_regionkey, p_productkey, sum(s_amount),
RANK() OVER (PARTITION BY r_regionkey
ORDER BY sum(s_amount) DESC)
AS rank_of_product_per_region
FROM product, region, sales
WHERE r_regionkey = s_regionkey AND p_productkey = s_productkey
GROUP BY r_regionkey, p_productkey;
1 つの問合せ内に異なる境界を持つランク付け
1 つの問合せブロックに、それぞれ異なるグループに対して動作する（異なる境界でリセットする）複数のランク
付け関数を含めることができます。次の問合せは、各地域の売上金額に基づく製品のランク付け
（rank_of_product_per_region）と、全地域の売上金額に基づく製品のランク付け（rank_of_product_total）を行いま
す。
SELECT r_regionkey, p_productkey, sum(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;
Oracle8i のための分析関数
1999 年 10 月
6
Oracle Corporation 発行の
｢Analytic Functions for Oracle8i」
の翻訳版です。
r_region
key
p_product
key
s_amount
rank_of_product
_per_region
rank_of_
product_total
east
east
east
east
east
east
east
west
west
west
west
west
west
west
shoes
jackets
shirts
sweaters
t-shirts
ties
pants
shoes
jackets
t-shirts
sweaters
shirts
ties
pants
130
95
80
75
60
50
20
100
99
89
75
75
66
45
1
2
3
4
5
6
7
1
2
3
4
4
6
7
1
4
6
7
11
12
14
2
3
5
7
7
10
13
一番右の 2 つの列は、2 つの異なるランクを計算することに注意してください。rank_of_product_total は、すべての
行を対象としたランク付けですが、rank_of_product_per_region は、地域が west に変化した時点でリセットされて
います。また、タイ値がランク付けでどのように処理されているかも注意してください。タイ値の処理について
は、DENSE_RANK 関数の項で詳しく説明します。
キューブおよびロールアップ・グループ別のランク付け
RANK は、Oracle8i に導入された CUBE または ROLLUP 演算子が行うグループ化に基づいてリセットできます。
CUBE/ROLLUP を使用すると、GROUPING(x)関数がグループ化の境界を検知し、x が、グループ化された列の一
部である場合は"0"を返し、x が小計である場合は"1"を返します。GROUPING()フラグは、リセットをトリガーす
るために PARTITION BY 句内で使用できます。たとえば、次の問合せは、それぞれのグループ内の製品および地
域をランク付けします。キー値が NULL と等しい行は小計です。出力表を見やすくするため、グループの変り目
で行を太線で区切ってあります。
SELECT r_regionkey, p_productkey, 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;
Oracle8i のための分析関数
1999 年 10 月
7
Oracle Corporation 発行の
｢Analytic Functions for Oracle8i」
の翻訳版です。
r_regionkey
p_productkey
s_amount
rank_per_cube
east
east
east
east
east
east
east
shoes
jackets
shirts
sweaters
t-shirts
ties
pants
130
50
80
75
60
95
20
1
12
6
7
11
4
14
west
west
west
west
west
west
west
shoes
jackets
shirts
sweaters
t-shirts
ties
pants
100
99
89
75
75
66
45
2
3
5
7
7
10
13
east
west
null
null
510
549
2
1
null
null
null
null
null
null
null
shoes
jackets
shirts
sweaters
t-shirts
ties
pants
230
149
169
150
135
161
65
1
5
2
4
6
3
7
null
null
1059
1
上表の最後の 3 つのセグメントは、小計の値を示しています。製品の値が NULL と等しい 2 行は、地域ごとの全
製品を対象とした小計、地域の値が NULL と等しい 7 行は、製品ごとの全地域を対象とした小計、そして最後の
セグメントは総合計です。
DENSE_RANK 関数
DENSE_RANK 関数は、タイ値の処理方法を除いて RANK 関数と同一です。ランク付けにタイ値が含まれている
場合、「タイ値の次の値には、どのランクを割り当てるべきか?」という大きな問題があります。ランクを 1 つだ
け大きくすべきでしょうか? それとも、タイ値の数に対応するランクを飛び越すべきでしょうか?
RANK 関数は、次の計算式を使用して、タイ値の後に続くランクの飛び越しを作成します。
Next rank after a tie, using RANK = tied rank + number of tied values
Oracle8i のための分析関数
1999 年 10 月
8
Oracle Corporation 発行の
｢Analytic Functions for Oracle8i」
の翻訳版です。
たとえば、ランク 3 に対応する 2 行がタイである場合、次に割り当てられるランクは、4 ではなく 5 になります。
つまり、RANK 関数では、ランク付けリスト内でタイ値がある箇所が不連続になるのです。
これに対し、DENSE_RANK 関数は、どのランク値も飛び越さずにランクを割り当てます。タイ値が検知されると、
次の計算式を使用します。
Next rank after a tie, using DENSE_RANK = tied rank + 1
DENSE_RANK で計算されるランクには、不連続な箇所がありません。また、DENSE_RANK の最大値は、各パー
ティション内の固有値のカウントと等しくなることにも注意してください。下表は、2 つの関数の出力の違いを対
比させたものです。
person
amount
RANK
DENSE_RANK
Adams
100
1
1
Baker
100
1
1
Connors
89
3
2
Davis
75
4
3
Edwards
75
4
3
Fitzhugh
66
6
4
Garibaldi
45
7
5
CUME_DIST（累積分布）関数
累積分布関数は、パーティション内の他の値に対する、特定の値の相対位置を計算します。CUME_DIST 関数は、
特定の行のパーティション内において、現在値の前にある行、または現在値とタイである行の割合として定義さ
れます。ゼロから 1 まで（ゼロを除き 1 を含む）の小数付き数値を返します。CUME_DIST 関数の結果は、しばし
ばパーセンタイル（百分位数）値と呼ばれます。デフォルト順序は昇順です。つまり、パーティション内の最小
値が、CUME_DIST の最も低い値を割り当てられます。
地域ごとの製品に対し、売上に基づいて CUME_DIST を割り当てるには、次の問合せを使用します。この例で、
"sales"データ表は、地域内の製品別に詳細な売上データを格納しています。希望の結果を算出するには、詳細レコー
ドを合計する必要があります。
SELECT r_regionkey, p_productkey, SUM(s_amount) as 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;
Oracle8i のための分析関数
1999 年 10 月
9
Oracle Corporation 発行の
｢Analytic Functions for Oracle8i」
の翻訳版です。
r_regionkey
p_productkey
s_amount
cume_dist_per_region
east
shoes
130
1.00
east
jackets
95
0.86
east
shirts
80
0.71
east
sweaters
75
0.57
east
t-shirts
60
0.43
east
ties
50
0.29
east
pants
20
0.14
west
shoes
100
1.00
west
jackets
99
0.86
west
t-shirts
89
0.71
west
sweaters
75
0.43
west
shirts
75
0.43
west
ties
66
0.29
west
pants
45
0.14
PERCENT_RANK 関数
PERCENT_RANK は CUME_DIST と良く似ていますが、行カウントではなくランク値を分子に使用します。従っ
て、値グループに対する値のランクを返します。この関数は、一般に普及している多数のスプレッドシートで使
用できます。行の PERCENT_RANK は、次のように計算されます。
（パーティション内の行のランク−1）/（パーティション内の行数−1）
PERCENT_RANK は、ゼロから 1 までの値を返します。ASCENDING（昇順）にソートされたセットの先頭行は、
PERCENT_RANK がゼロになります。
NTILE 関数
NTILE 関数を使用すると、3 分位、4 分位、10 分位、および他の一般的な要約統計値を簡単に計算できます。この
関数は、順序が指定されたパーティションを、"バケット"と呼ばれる指定された数のグループに分割し、パーティ
ション内の各行にバケット値を割り当てます。NTILE を使用すると、データ・セットを 4 分割、3 分割などのグルー
プに分割できるので非常に便利です。
バケット値は、各バケットに割り当てられる行が完全に同数になるか、他のバケットよりも 1 行だけ多くなるよ
うに計算されます。たとえば、パーティション内に 100 行がある場合に、バケット数を 4 として NTILE 関数を実
行すると、25 行に 1、25 行に 2（...以下同様）のように値が割り当てられます。
パーティション内の行数が、所定数のバケットに等分できない（剰余なしで割り切れない）場合は、各バケット
に割り当てられる行の数に最高 1 行の差異が生じます。余った行は、バケット番号が最も小さいものから順に、
各バケットに 1 行ずつ配分されます。たとえば、103 行のパーティションに NTILE(5)を使用すると、最初のバケッ
トに 21 行、2 番目のバケットに次の 21 行、3 番目のバケットに次の 21 行、4 番目のバケットに次の 20 行、そし
て 5 番目のバケットに最後の 20 行が割り当てられます。
Oracle8i のための分析関数
1999 年 10 月
10
Oracle Corporation 発行の
｢Analytic Functions for Oracle8i」
の翻訳版です。
副問合せを使用すると、列の境界ではなく、数値の範囲に基づいてリセットが行われる複雑なランク付けを作成
できます。これにより、バケット内でのデータのランク付けが可能になります。たとえば、次の問合せは、売上
金額に基づいて製品ごとの売上を 4 つのバケットに分割し、各 4 分割内で製品をランク付けします。
SELECT p_productkey, sum_s_amount, 4_tile,
RANK() OVER (PARTITION BY 4_tile ORDER BY sum_s_amount DESC)
AS rank_in_quartile
FROM
(SELECT p_productkey,
sum(s_amount) as sum_s_amount,
NTILE(4) (ORDER BY sum(s_amount) DESC) as 4_tile
FROM product, sales
WHERE p_productkey = s_productkey
GROUP BY p_productkey);
p_productkey
sum_s_amount
4_tile
rank_in_quartile
shoes
100
1
1
jackets
90
1
2
shirts
89
2
1
sweaters
75
2
2
shirts
75
3
1
ties
66
3
2
pants
45
4
1
socks
45
4
1
注意： この例で副問合せを使用したのは、明解を期するためにすぎません。RANK 関数の PARTITION_BY 句内に、
"4_tile"を直接代入すれば、副問合せを使用せずに済みます。
ROW_NUMBER 関数
ROW_NUMBER 関数は、ORDER_BY 句で定義された順序で、固有の数字をパーティション内の各行に割り当てま
す。これは、パーティション別に各行を採番する場合に便利です。ROW_NUMBER 関数は、ROWNUM 疑似列と
は無関係ですので注意してください。行番号は、必ず 1 から始まります。以下に例を示します。
SELECT p_productkey, s_amount,
ROW_NUMBER() (ORDER BY s_amount DESC NULLS LAST) AS srnum
FROM product, sales
WHERE p_productkey = s_productkey
Oracle8i のための分析関数
1999 年 10 月
11
Oracle Corporation 発行の
｢Analytic Functions for Oracle8i」
の翻訳版です。
p_productkey
s_amount
srnum
shoes
jackets
shirts
t-shirts
sweaters
jeans
ties
pants
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
ウィンドウ集約関数群
ウィンドウ集約関数は順序が指定された行セットに対して動作し、各行について、その行を基準としたウィンド
ウに対する集約値を返します。"現在までの銀行預金の累積合計を計算する"ような無限のウィンドウと、"91 日間
における株価の移動平均を計算する"ような有限のウィンドウを指定できます。ランク付け関数と同様に、まず
データを各グループ（銀行口座など）にパーティション化し、各グループ内でウィンドウ機能関数を適用します。
たとえば、"各口座について、銀行預金の累積合計を日付順に表示する"関数を適用します。
ここでは、ウィンドウ集約関数を"関数群"と呼んでいますが、これに定義された新しいキーワードは数個だけです。
このファミリを、既存の集約関数（SUM、AVG、MIN、MAX、STDDEV、VARIANCE、および COUNT）に対応
する新しい構文および動作としてとらえるのも有益です。
ウィンドウ集約には、次の機能が使用できます。
•
すべての既存集約関数（SUM、AVG、MIN、MAX、STDDEV、VARIANCE、および COUNT）を、ウィン
ドウ集約として使用できます。同様に、分析関数とともに導入された新しい回帰分析関数もサポートされ
ています。これには、次の関数が含まれます。VAR_SAMP, VAR_POP, STDDEV_SAMP, STDDEV_POP,
COVAR_SAMP, COVAR_POP, REGR_SLOPE, EGR_INTERCEPT, REGR_R2, REGR_AVGX, REGR_AVGY,
REGR_COUNT, REGR_SXX, REGR_SXY AND REGR_SYY.
•
各ウィンドウ集約関数には、ウィンドウのサイズを定義するオプションの句を指定できます。この句を指
定しないと、無限のウィンドウになります。つまり、表またはパーティションの始まりで開始し、表また
はパーティションの終わりで終了します。
•
各ウィンドウ関数には、ランク付け関数群で使用できるような、固有の順序指定句を使用できます。
•
ウィンドウ集約関数は、各パーティションの境界でリセットされます。
•
各ウィンドウ集約関数は、複数の式に基づいてデータの順序を指定できます。複数の式に基づくランク付
け能力はタイ値を少なくし、繰返し結果が可能になります。
•
関数内で使用する式ごとに、昇順または降順を指定できます。
•
順序の方向（昇順か降順）を問わず、NULL の強制して、最後または最初にランク付けすることができま
す。
Oracle8i のための分析関数
1999 年 10 月
12
Oracle Corporation 発行の
｢Analytic Functions for Oracle8i」
の翻訳版です。
ウィンドウ・サイズの定義
ウィンドウは、ユーザーのニーズに応じてさまざまな方法で定義できます。
•
物理的 ― 行数に基づいて、物理的なウィンドウ・サイズを指定できます。たとえば、"現在行の直前の 10
行を対象とした移動平均を返してください"のように表現できます。ウィンドウのサイズを指定する数値式
に加え、いくつかのキーワードを使用することができます。
CURRENT ROW--ウィンドウが、現在行で開始または終了することを指定します。
UNBOUNDED PRECEDING--ウィンドウが、パーティションの先頭行で開始することを指定します。
UNBOUNDED FOLLOWING--ウィンドウが、パーティションの最終行で終了することを指定します。
•
時間隔 ― 時間隔に基づいて、論理的なウィンドウ・サイズを指定できます。たとえば、"現在行の日付の
直前の 91 日間を対象とした株価の移動平均を返してください"のように表現できます。あるいは、"現在行
の時刻の直前の 100 秒間を対象とした温度の移動平均を返してください"という例です。この場合、順序指
定式は、時間隔が適用できるように日付式に対して評価されます。
•
値の範囲 ― 現在の値と、順序が指定されたシーケンス内の前の値との差異に基づいて、論理的なウィン
ドウ・サイズを指定できます。差異が特定のサイズよりも小さい行は、ウィンドウに含まれます。たとえ
ば、"現在行の人より身長が低く、身長差が 3 インチ以下のすべての人の平均身長を返してください"とい
う問合せでは、"Height BETWEEN current.Height AND current.Height-3"に該当するすべての行がウィンドウ
に含まれます。
次の例は、預金日に基づいて順序が指定された口座ごとに、累積残高を表示します。
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
73829
73829
73829
82930
82930
82930
Trans_date
1998-11-01
1998-11-05
1998-11-13
1998-11-01
1998-11-21
1998-11-29
Trans_amount
113.45
-52.01
36.25
10.56
32.55
-5.02
Balance
113.45
61.44
97.69
10.56
43.11
38.09
移動集約関数の例
次に示すのは、取引ごとに、その前の 30 日間にわたる移動平均取引金額を表示する時間ベースのウィンドウの例
です。
SELECT Trans_date, Trans_amount,
AVG (Trans_amount) OVER (ORDER BY Trans_date)
RANGE INTERVAL '30' DAY PRECEDING)
FROM Ledger;
Oracle8i のための分析関数
1999 年 10 月
13
Oracle Corporation 発行の
｢Analytic Functions for Oracle8i」
の翻訳版です。
中心集約関数の例
現在行を中心としたウィンドウ機能集約関数の計算、たとえば中心平均は、比較的単純です。次の例は、現在行
を中心として、現在行の前の 2 行および現在行の後の 2 行を含む、中心移動平均を計算します。
SELECT st_timekey,
AVG(s_amount) OVER
(ORDER BY st_timekey
ROWS BETWEEN 2 PRECEDING AND 2 FOLLOWING) AS cavg
FROM stock
WHERE st_name = ‘orcl’;
FIRST_VALUE 関数と LAST_VALUE 関数
FIRST_VALUE 関数および LAST_VALUE 関数は、ユーザーがウィンドウ集約関数の能力および柔軟性をフルに活
用できるように支援します。問合せが、先頭行および最終行をウィンドウから選択できるようにします。これら
の行は、計算のベースラインとして使用されることが多いため特に重要です。たとえば、日付に基づいて順序が
指定された売上データを格納しているパーティションに対し、"各日の売上を、その期間の最初の営業日
（FIRST_VALUE）と比較する"ことができます。または、売上の昇順に順序が指定されている行セットに対し、"
地域内で最高の売上（LAST_VALUE）に対する各売上の比率"を知ることができます。
レポート集約関数群
1 つのレベルの集約を異なるレベルの集約と比較することは、分析作業の重要な部分です。たとえば、従業員の給
与を部門別平均給与と比較したり、その従業員を除く平均給与を知りたい場合があります。また、部門別平均給
与を全部門の平均給与と比較したい場合もあります。レポート集約ファミリでは、セルフ結合を使用せずに、異
なるレベルで集約した値を行に含めることができます。グループごとに 1 行ずつ（グループごとの合計など）を
返す SQL 集約関数とは異なり、レポート集約関数はウィンドウ・レベルで動作します。つまり、ウィンドウ内の
すべての行について、同一の集約値を返します。
レポート集約関数は、ウィンドウ全体の集約、または現在行を除くウィンドウ全体の集約を計算します。ほとん
どの点で、ウィンドウ集約関数と似ており、同様の機能を持っています。
レポート集約関数の例
"各製品について、売上が最高であった地域は?"という質問を考えてみましょう。レポート集約関数を使用すると、
インライン・ビューを使用して答えを見つけることができます。
Oracle8i のための分析関数
1999 年 10 月
14
Oracle Corporation 発行の
｢Analytic Functions for Oracle8i」
の翻訳版です。
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_s_amount
FROM sales
GROUP BY s_productkey, s_regionkey)
WHERE sum_s_amount = max_sum_s_amount;
インライン・ビューは、次の情報を返します。
s_productkey
s_regionkey
sum_s_amount
max_sum_s_amount
jackets
west
99
99
jackets
east
50
99
pants
east
20
45
pants
west
45
45
shirts
east
60
80
shirts
west
80
80
shoes
west
100
130
shoes
east
130
130
sweaters
west
75
75
sweaters
east
75
75
ties
east
95
95
ties
west
66
95
外部問合せは、必要な情報を返します。
s_productkey
s_regionkey
sum_s_amount
jackets
west
99
pants
west
45
shirts
west
80
sweaters
west
75
sweaters
east
75
shoes
east
130
ties
east
95
レポート集約関数を使用しないと、この問合せは、各製品について地域ごとに最高の売上を計算し、その値を sales
表と結合します。新しい関数を使用すると、結合を処理するコストが節減されます。
Oracle8i のための分析関数
1999 年 10 月
15
Oracle Corporation 発行の
｢Analytic Functions for Oracle8i」
の翻訳版です。
RATIO_TO_REPORT 関数
RATIO_TO_REPORT 関数関数は、ウィンドウ内の値セットの合計に対する値の比率を計算します。競合製品のマー
ケット・シェアや、財務分析での原価配分のような占有率を計算するために使用できます。この関数は、一般的
な計算を能率化する省略形として活用できます。比率計算で、分母として使用する値を含む列を返す必要がなく
なります。
次の例では、各製品の売上比率を RATIO_TO_REPORT で計算します。つまり、総売上に対する割合として、各製
品の占有率を計算します。レポート全体の売上合計を選択する必要がないことに注意してください。
SELECT s_productkey, SUM(s_amount) AS sum_s_amount,
RATIO_TO_REPORT(SUM(s_amount)) OVER () AS ratio_to_report
FROM sales
GROUP BY s_productkey;
s_productkey
sum_s_amount
ratio_to_report
shoes
100
0.19
jackets
90
0.17
shirts
80
0.15
sweaters
75
0.14
shirts
75
0.14
ties
10
0.01
pants
45
0.08
socks
45
0.08
ラグ/リード関数群
分析作業の主要なタスクは、データ・セット内の値を比較することです。前年に対する月間売上の変動や、予算
と原価実績との差異が分析の対象になります。分析計算で、表内の 2 行を比較する必要がある場合、SQL ユーザー
はセルフ結合を使用するのが一般的でした。LAG/LEAD 関数では、現在の行からのオフセットを指定するだけで、
別の行をアクセスできます。セルフ結合が必要でないため、パフォーマンスを大幅に向上させることができます。
LAG/LEAD 関数では、オフセットが表から逸脱した場合のデフォルト値も指定できます。次の例は、LAG/LEAD
関数を示したものです。
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;
Oracle8i のための分析関数
1999 年 10 月
16
Oracle Corporation 発行の
｢Analytic Functions for Oracle8i」
の翻訳版です。
t_timekey
s_amount
LAG_amount
LEAD_amount
92-10-11
1
NULL
2
92-10-12
2
1
3
92-10-13
3
2
4
92-10-14
4
3
5
92-10-15
5
4
NULL
追加問合せ
この節では、分析関数によって複雑な質問に対応する 2 つの例を紹介します。最初の問合せでは、電話会社の通
話詳細記録に関するものです。2 番目の問合せは、各都市を対象とした累積売上分析が関わります。
異なる期間における電話会社の顧客活動の例
この例は、当初、「参考」の「2.」に提示されたものです。この問合せは、FromAC、FromTel、ToAC、To Tel、
Date、および Length という列を持つ CALLS という 1 つの通話詳細記録表を使用します。この表は、すべての通話
について、発信元と着信先の両方の地域コードおよび電話番号を、通話日および通話時間とともに格納します。
ここに格納されたデータは、1998 年のものです。
次の問合せは、顧客ごとに、前半の 6 カ月間で、その年に発生したすべての通話の平均時間を超える通話の数と、
後半の 6 カ月間でこれと同じ平均時間を超える通話の数を返します。
SELECT FromAC, FromTel,
COUNT( CASE WHEN (date >= TODATE('01-JAN-1998') and
date <= TODATE('30-JUN-1998') and
Length > ravg_Length)
THEN 1 ELSE NULL ) as count_1,
COUNT( CASE WHEN (date >= TODATE('01-JUL-1998') and
date <= TODATE('31-DEC-1998') and
Length > ravg_Length)
THEN 1 ELSE NULL ) as count_2
FROM
(SELECT FromAC, FromTel, Length,
AVG(Length) OVER (PARTITION BY FromAC, FromTel) as ravg_Length
FROM CALLS )
GROUP BY FromAC, FromTel;
この問合せは、発信元の電話番号（FromAC、FromTel）ごとに、GROUP_BY を実行します。結果を計算するため
に、2 つのパスを処理します。最初のパスでは、各パーティションの平均時間（ravg_Length）が（副問合せ内で）
計算され、2 番目のパスでは、グループごとに、平均時間を超える通話の合計数（count_1 および count_2）が計算
されます。
Oracle8i のための分析関数
1999 年 10 月
17
Oracle Corporation 発行の
｢Analytic Functions for Oracle8i」
の翻訳版です。
分析関数を使用しない場合は、まず、通話の平均時間を一時表またはインライン・ビューから計算し、それをファ
クト表に結合しなおして、平均時間を超える通話の数をカウントしなければなりません。分析関数はより効率的
な処理を可能にします。
都市別の売上分析の例
これは、レポート集約関数を使用して売上データを分析する例です。各州について、売上が最高の都市を知りた
い場合はどうすればよいでしょう。次の問合せは、"各州について、累積売上が州全体の売上の 50%を占める都市
だけを、売上の昇順に表示してください"というビジネス上の質問に答えます。
SELECT r_state, r_city, city_sales, cum_ratio
FROM
(SELECT r_state, r_city, SUM(s_amount) as city_sales,
(SUM(SUM(s_amount)) OVER (PARTITION BY r_state ORDER BY
SUM(s_amount) DESC ROWS UNBOUNDED PRECEDING)) /
(SUM(SUM(s_amount)) OVER (PARTITION BY r_state)) as cum_pct
FROM sales, region
WHERE s_regionkey = r_regionkey
GROUP BY r_state, r_city)
WHERE cum_ratio <= 0.5;
r_state
r_city
city_sales
cum_pct
California
San Francisco
202,000
0.19
California
Los Angeles
152,000
0.33
California
San Jose
91,000
0.41
California
San Diego
74,000
0.48
Colorado
Denver
102,000
0.25
Colorado
Colorado Springs
56,000
0.39
Colorado
Boulder
39,000
0.49
この問合せは、州および都市（r_state、r_city）ごとに、GROUP BY を実行します。副問合せでは、各都市の総売
上（city_sales）を計算し、州の総売上に対する比率を、各行の累積合計（cum_pct）で表します。副問合せ内のレ
ポート集約関数"(SUM(SUM(s_amount))OVER(PARTITION BY r_state)"は、比率計算の分母として使用される州の総
売上を与えることに注意してください。外部問合せは、結果をフィルタで選別し、比率の値が、各州の総売上の
半分未満を表す 0.5 より少ない行だけを表示します。
Oracle8i のための分析関数
1999 年 10 月
18
Oracle Corporation 発行の
｢Analytic Functions for Oracle8i」
の翻訳版です。
結論
分析関数は、Oracle サーバーによるパワフルで効率的な意思決定支援処理をサポートするという Oracle のコミッ
トメントを示す重要な例です。Oracle 7.3 のビットマップ・インデックスから、Oracle 8.0 のスター結合問合せ変換、
そして Oracle8i のマテリアライズド・ビューまで、Oracle はスケーラブルな分析処理を一貫してリードしてきまし
た。世界中のデータ・ウェアハウス、ビジネス・インテリジェンス・システム、および高度 OLAP 環境は、Oracle
のコア・データベース・エンジンを基盤としています。
分析関数により Oracle は、問合せパフォーマンスの高速化および開発作業の効率化への新しい道を開きます。
Oracle は、最小限の新しい構文で、SQL での更なる表現力を可能にしました。Oracle は、新しい関数が、意思決
定支援処理のすべての範囲に多大な影響を与えると確信しています。分析関数のパワーは、ANSI 標準のステータ
スと一体になり、すべての SQL ユーザーにとって価値あるツールを実現します。
Oracle8i のための分析関数
1999 年 10 月
19
Oracle Corporation 発行の
｢Analytic Functions for Oracle8i」
の翻訳版です。
参考
1. Fred Zemke et al., Proposal for OLAP functions, ISO/IEC JTC1/SC32 WG3:YGJ-069r1
= ANSI NCITS H2-99-155r3.
2. D.Chatziantoniou and K.Ross, Querying Multiple Features of Groups in Relational Databases,
Proceedings of VLDB Conference 1996, pages 295-306.
Oracle Corporation
World Headquarters
500 Oracle Parkway
Redwood Shores, CA 94065
U.S.A.
世界共通窓口：
+1.650.506.7000
FAX：+1.650.506.7200
http://www.oracle.com/
版権 © Oracle Corporation 1999
無断転載を禁ず
この文書はあくまでも参考資料であり、掲載されている情報は予告
なしに変更されることがあります。万一、誤植などにお気づきの場
合はオラクル社までお知らせください。オラクル社は本書の内容に
関していかなる保証もいたしません。また、本書の内容に関連した
いかなる損害についても責任を負いかねます。
Oracle はオラクル社の登録商標です。Oracle8i、PL/SQL、Oracle8i
Enterprise Edition、Oracle8i Standard Edition、および Oracle8 は、オ
ラクル社の商標です。
他のすべての企業名と製品名は、識別のためにのみ掲載されており、
それぞれの所有者の商標です。
#J.Anal 2000/03/21
Oracle8i のための分析関数
1999 年 10 月
20
Oracle Corporation 発行の
｢Analytic Functions for Oracle8i」
の翻訳版です。