津島博士のパフォーマンス講座 
第47回 自己結合と分析ファンクションについて

■津島博士による解説が動画でも! セミナー動画公開中です。
津島博士のパフォーマンス講座「パフォーマンス問題はなぜ起きるのか」  【WMV】 【MP4】 【PDF

皆さんこんにちは、今年も梅雨の時期になりましたね。沖縄では梅雨明けしたようですが、関東はこれからが本番ですので、じめじめした気候に負けずに頑張りましょう。
今回は、データ分析などで使用する機会が多い自己結合と分析ファンクションについて説明します。過去のデータと比較するような場合などに使用しますが、大きな表での自己結合は非常に時間が掛ってしまいます。そのため、多くの分析ファンクションが提供されていますが、難しいと思っている方も多いのではないでしょうか。そこで今回は、分析ファンクションを効果的に使用できるように、自己結合や分析ファンクションについて説明しますので、参考にしてください。

1. 自己結合

まずは、これまでも何度か登場している自己結合について説明しましょう。
自己結合は、同じ表のそれぞれのデータを横展開して(別表のように結合して)比較するデータ分析などによく使用します。例えば、売上データをあるカテゴリで比較するような場合です。ただし、同一表に何度もアクセスする必要があるので、データ量が多い場合には大量の全表スキャンによって性能が悪くなってしまいます(このときアクセス行数が多いと効果的な索引スキャンもできません)。そのため、ここでは主に自己結合を行う以下の二つについて説明します。

  • カテゴリ別に比較
  • 自分以外と比較(非等価結合)

(1)カテゴリ別に比較
これが最も単純で多く使用されている自己結合になると思います。
年齢や性別などのカテゴリ毎に比較するような場合になるので、その単位にインライン・ビューで分割してから外部結合を行います。そのため、パーティション表を使用することでアクセスを最小限にすることができます。例えば、以下のように全体と男/女に集計したものを比較するなどです(この例では、性別でパーティションすればデータ・アクセスを削減できますが、パーティションは二つまでなので、このような列でパーティションできない場合もあります)。

pic 1

これについては、CASE文やPIVOT句などの機能が提供されているので、使い分けるのが良いでしょう(これは第24回の「横展開する」で説明しましたよね)。

(2)自分以外と比較
これはそれぞれの行とそれ以外を結合する必要がある少し複雑な自己結合になります。
自分以外のデータと比較するような場合になるので、それぞれの行と別データ(過去データなど)を結合する必要があります(>,<などの非等価条件で自己結合する必要があります)。そのため、第29回で説明した相関副問合せを使用する場合が多いので、ネスト解除できないと更に効率が悪くなります。
例えば、2015年6月1日に売上があった商品に対して、直近で購入した日付を出力するために、以下のSQLのような相関副問合せを実行する場合です(いつ購入されたか判断できないので、過去のすべてのデータに行っています)。

pic 2

このSQLは、第42回の「Scalar Subquery Unnesting(スカラー副問合せのネスト解除)」で説明したように、以下のような外部結合に変換することができます。こちらの方がハッシュ結合になり効果的に結合できますが、全表スキャンを複数回するのは変わりません。

pic 3

そのため、このような処理では、自己結合を回避するために分析ファンクションやユーザー定義集計ファンクションを使用します。

2. 分析ファンクション

次に、これまであまり説明していなかった分析ファンクションについて説明しましょう。
大きなテーブルに対する自己結合は効率が悪いので、ユーザー定義集計ファンクションなどを作成できれば良いのですが、慣れない方には大変な場合もあります。そのため、できるだけ提供されている分析ファンクションを使用するようにします。ただし、第24回でも少し説明しましたが、まだ難しいと感じている方も多いと思うので、代表的なものについて基本的な使い方や使用時の注意点、そしてまだ説明していないWINDOWING句について説明していきます。

(1)基本的な使い方
分析ファンクションの基本的な構文は、ANALYTIC句(OVERのカッコ内)で結果セットを操作するために、以下のPARTITION BY句、ORDER BY句、WINDOWING句を指定して行います。

pic 4

  • PARTITION BY句
    ファンクションを実行するときに、商品などの同一データを行グループ(ウィンドウともいい、このような関数をウィンドウ関数とも呼びます)として扱います。
  • ORDER BY句
    行グループの順序付け方法を指定します(PARTITION BY句を指定しないとすべてのデータが対象になります)。
  • WINDOWING句
    ファンクションの処理対象範囲を指定します。これで累積集計(最初から現在まで集計する)、移動集計(それぞれの日付に対して過去1年間分などを集計する)などを簡単に行うことが可能になります。

分析ファンクションは、それぞれの行に対する行グループ内での集計処理になるので、非等価条件で自己結合する代わりに使用できます。例えば、自己結合の「自分以外と比較」で使用したSQLを分析ファンクションのSQLにすると以下のようになります。このようにLEADファンクション(1つ後のデータを求める)を使用することで1回の全表スキャンで実行することができます(商品毎に売上日を降順に並べているので、2015年6月1日の次に大きい売上日になります)。

pic 5

最近のOracleデータベースでは、行グループ単位に集計(単純なSUMから複雑なパーセンタイルなどまで)、ランキング(第24回で説明した通番を振る)、前後n番目の値を取得する(LEAD、LAG)などの多くのファンクションが提供されています。そのため、使用できる場合も多いと思うので、検討してみてください。

(2)使用時の注意点
分析ファンクションの注意点は、ソート処理(WINDOW SORT)を行うので、メモリ不足によるTEMP領域の使用になります(第36回で説明したように、処理行数が多いとTEMP領域へのアクセスが多くなり逆に遅くなる場合もあります)。そのため、大きな表全体が対象になるファンクションには注意が必要です。また、効果的に行うには、第9回の「ソート処理はできるだけ少ない件数で行う」で説明したように、GROUP BYなどの行数を削減する処理はできるだけ早い段階で行います。
例えば、商品に対して直近3つまでの大きい値(売上日)を求めるとします。このとき以下のSQLを実行すると(必要ない売上日をなくすためにGROUP BYを使用する)、'SORT GROUP BY NOSORT'(ソートを行わないGROUP BY)になりますが、WINDOW SORTはすべての行数が対象になるので、行数が多いとTEMP領域を使用してしまいます(これは商品でGROUP BYするので、その後から商品を行グループとして分析ファンクションを行うことができないからです)。そのため、このような処理には、GROUP BYは効果的ではないと言えます。

pic 6

以下のようにGROUP BYを行わないようにもできますが(売上日をなくすためにROW_NUMBERファンクションを使用する)、WINDOW SORTですべての行を処理するのは変わらないので、これもメモリ使用量を削減することはできません。

pic 7

このような場合には、以下のようにROW_NUMBERファンクション(通番を振るファンクション)だけで行うSQLにすることで、WINDOW SORTで行数の削減ができるようになります。このようなメモリ使用量を削減するには効果的なので、覚えておくと良いと思います。

pic 8

このように分析ファンクションを使用するとしても様々な方法があるので、いろいろな使い方を検討してみてください。機会があればまた紹介したいと思います。

(3)WINDOWING句
最後に、WINDOWING句(スライド・ウィンドウ)について説明します。
分析ファンクションの中には、行グループにスライド・ウィンドウ(対象範囲)を指定できるファンクション(SUN、MAX、MIN、AVG、FIRST_VALUE、LAST_VALUE、NTH_VALUEなど)があります。
スライド・ウィンドウは、RANGE(値で指定する)とROWS(行番号で指定する)を使用して、様々な値を1回のアクセスで取得できるようにするので、使用できる範囲がより多くなります。
ROWSとRANGEの違いが分かるように、以下のSQL(列'c3'の現在と次のデータをROWSとRANGEで合計する)を使用して説明します。注目して欲しいのは、列'c3'で重複値データ(103)と値が飛んでいるデータ(201)です。

pic 9

ROWS'A1'は、値を意識しないので、すべてのデータで同じように次の行と合計しています。それに対して、RANGE'A2'は、値によって合計値が異なっています。つまり、重複値は同じ行として扱い、値が飛んでいると次の行データとして扱いません(201は104の次のデータになりません)。この違いを考慮して分析する内容によって使い分けます。基本は、日付などの値を使用した範囲指定はRANGEを使用しますが、重複値を個別に扱いたい場合にはROWSを使用するようにします。
範囲は、以下の指定をBETWEEN条件と組み合わせて行います(省略時には、最初の行から現在の行が設定されます)。少し複雑なように思えますが、慣れるとそうでもないので使用してみてください。

  • CURRENT ROW(現在の行)
  • UNBOUNDED PRECEDING(最初の行)
  • UNBOUNDED FOLLOWING(最後の行)
  • n PRECEDING(現在行に対してn個前の行)
  • n FOLLOWING(現在行に対してn個後の行)

ここからは、慣れていただくために、いくつかの使用例を使用して説明していきます。
一つ目の例は、2015年1月の売上金額に対する過去1ヶ月と過去半年の平均売上金額を求める処理です。以下のSQLで行うことが可能です(BETWEEN条件を指定しないと現在行からの範囲になります)。このようにINTERVAL YEAR TO MONTHリテラルなどを使用することで、日付型のスライド・ウィンドウも指定できます。また、スライド・ウィンドウの指定では、アクセスする範囲も調整される訳ではないので、無駄なデータにアクセスしないようにする必要があります。この例では、WHERE句で"売上日 >= TO_DATE('2014/07/01','YYYY/MM/DD') "を指定して、半年以前にアクセスしないようにしています。

pic 10

以下のようにNUMTOYMINTERVALファンクションを使用して、数字からINTERVAL YEAR TO MONTHリテラルに変換することもできます(ある値を計算して求めた数字を期間にしたい場合などに使用できます)。

pic 11

二つ目の例は、商品毎の累積合計(最初の売上日からの金額の合計)を求める処理です。この場合は、以下のSQLのようにUNBOUNDED PRECEDING(最初の行から)を使用して行います。また、商品に対する売上日が一つになるようにGROUP BYを行っています。このようなSQLにすると、GROUP BYを分析ファンクションより先に実行するので、間違わないようにしてください(実行計画でSORT GROUP BYがWINDOW BUFFERの下に出力されていることからも分かります)。

pic 12

このようなことを参考に、様々な処理に使用してみてください。これも機会があればまた紹介したいと思います。

3. おわりに

今回は自己結合と分析ファンクションについて説明しましたが、少しは参考になりましたでしょうか。これからも頑張りますのでよろしくお願いします。質問をお待ちしています。
それでは、次回まで、ごきげんよう。