皆さんこんにちは、今年の梅雨は朝晩が少し涼しくて過ごしやすいように思っていましたが、7月中旬くらいから急に猛暑がやってきて、今年も気温の変化が激しい身体に辛い梅雨ですね。
今回は、第47回の分析ファンクションの続きとして、ユーザー定義の分析ファンクションについて説明します。後半に、分析ファンクションの補足についても説明していますので、参考にしてください。
1. ユーザー定義の分析ファンクション
まずは、ユーザー定義の分析ファンクションについて説明しましょう。
多くのファンクションが提供されるようになったとはいっても、それだけでは必要な処理を行うことができない場合もあります。そのような場合には、PL/SQLプロシージャーを作成するのではなく、ユーザー定義の分析ファンクションを作成することを検討してください。
ユーザー定義の分析ファンクションは、第24回で説明したユーザー定義集計ファンクションと同じように、ODCIAggregateルーチンを使用してオブジェクト型として定義します（どちらもユーザー定義集計ファンクションと呼びますが、ここでは区別するためにユーザー定義分析ファンクションと呼ぶことにします）。そのため、第24回の例で使用したファンクション’nzcnt’（0以外の件数を求める）を、以下のように分析ファンクションとしても実行することができます。
ここでは、第24回であまり詳しく説明していない以下の三つのルーチンについて、ファンクション’nzcnt’を使用して分析ファンクションとしてどのように使用されるのかを説明していきます。
- ODCIAggregateDelete（削除）
- ODCIAggregateMerge（マージ）
- ODCIAggregateWrapContext（外部コンテキストの統合）
（１）ODCIAggregateDelete（削除）
ODCIAggregateDeleteは、第24回で分析ファンクションの削除を効果的にするために使用するオプション・ルーチンと説明しましたが、ここではもう少しどのような場合に使用されるかも含めて説明します。
このルーチンは、以下のようなSQL（範囲の開始が固定でないなど）のときに作成すると、効果的に動作することが可能になります。
このSQLは、分析ファンクションの開始が現在行になるので、現在行が変わるごとに開始を変更する必要があり、それぞれの行の先頭の1行を削除するときに、このルーチンを使用することができます。そのため、ODCIAggregateDeleteについては、ODCIAggregateIterateの更新処理に対して削除するように処理を作成します（この例では、以下のように0以外のときに-1をするように作成します）。
このファンクションで先程のSQLを実行すると、それぞれの行で集計コンテキスト（前回実行されたときの結果）から1行の削除を行うようになります。ただし、このルーチンは、オプションなので作成しなくても問題ありませんが、作成しないとそれぞれの行で常に対象行を再集計することになってしまいます（つまり、このSQLでは現在行から最後の行までに対して、ODCIAggregateIterateだけを実行して求めるようになります）。
分かりやすいように、以下のSQLの実行結果を使用して、ODCIAggregateDeleteがない場合とある場合を説明します。右側の図は、それぞれのルーチンをODCIAggregateInitialize：I、ODCIAggregateIterate：A、ODCIAggregateDelete：D、ODCIAggregateTerminate：Tとして、どのようにルーチンがコールされたかを示しています（カッコ内はそのときのデータになります）。例えば、c2が0（赤字）のときは、ODCIAggregateInitialize、0のときのODCIAggregateIterate、1のときのODCIAggregateIterate、ODCIAggregateTerminateがコールされるという意味です。
ODCIAggregateDeleteがない方は、ODCIAggregateIterateだけで求める必要があるので、それぞれの行でODCIAggregateInitializeと対象行数分のODCIAggregateIterateをコールしています（この例では、現在行と前後の行で3回コールされています）。それに対して、ODCIAggregateDeleteがある方は、それぞれの行で必要な行数のODCIAggregateDeleteとODCIAggregateIterateをコールしています（この例では、それぞれ1回だけコールされています）。そのため、このように削除が必要な場合では、必要最小限のコールで済むようになり、効果的な実行が可能になります。この例では、最大3回までの実行でそんなに変わらない感じですが、対象がもっと多くなるような範囲（例えば、最初のSQLのように最後の行までの範囲）だと大きな違いになってしまうので、分析ファンクションの場合はODCIAggregateDeleteを常に作成するようにしてください。
（２）ODCIAggregateMerge（マージ）
ODCIAggregateMergeは、パラレル実行のときに使用されるルーチンですが、これも作成するときの参考のためにどのような場合に使用されるか簡単に説明します。
これは、ファンクションの実行結果をマージするような場合になるので、以下のようなSQLを実行すると使用されます（このSQLは、最終的に1行にする必要があるので、そのときの集計結果のマージで使用します）。つまり、以下の実行計画のように、ファンクション（SORT AGGREGATEなど）を2回実行される場合になります（1回目はPQプロセスで集計して、2回目の実行はコーディネータ・プロセスによって集計がマージされます）。
そのため、ODCIAggregateMergeについては、以下のような二つの結果をマージするように作成します（この例の場合は、件数を求める処理なので二つの値を加算することになります）。
以下のようなSQL（集計ファンクションや分析ファンクション）では、パラレル実行しても使用されることはありません。このような処理のパラレル実行は、ハッシュ分散（PX SEND HASH）またはレンジ分散（PX SEND RANGE）になるので、同じデータは同一PQプロセスで処理されてマージする必要がないからです。そのため、実行計画ではファンクション（SORT GROUP BY、WINDOW SORT）が1回だけしか実行されていません。ただし、第20回で説明したGroup-by PushDown(GPD)が動作した場合は、GROUP BYを2回実行するので、使用されることになります。
このルーチンについては、分析ファンクションで使用されることはありませんが、ODCIAggregateDeleteのように別のルーチンで行うことができないので、オプションにはなっていません。
（３）ODCIAggregateWrapContext（外部コンテキストの統合）
ODCIAggregateWrapContextは、C++やJavaなどのような外部言語で実装して（外部メモリに集計コンテキストを作成して）パラレル実行すると、次のPQプロセスまたはコーディネータ・プロセスに転送するときに（第20回や第39回で説明したデータの再分散をするときに）、外部メモリからコピーするために使用されるオプション・ルーチンです。そのため、外部言語で実装していないと必要がないので、あまり使用する機会は少ないと思い、詳細な説明はまたの機会とします。
2. 分析ファンクションの補足
ここからは、分析ファンクションの補足として参考になることを少し説明しましょう。
（１）自己結合との違い
第47回で自己結合の代わりに分析ファンクションを使用できると説明しましたが、データによっては実行結果が一緒にならない場合があるので、それについて少し説明します。
第47回の自己結合の「自分以外と比較」と分析ファンクションの「簡単な使い方」で使用したSQLの場合には、売上日に重複値が存在すると（同じ日に商品を複数回購入するような場合だと）結果が異なるので注意してください。これは、自己結合では検索の対象にはならないでNULLになりますが（条件が’B.売上日 < A.売上日’なので同一データは対象になりません）、この分析ファンクションでは現在行を対象に検索してしまうからです。例えば、2015/06/01に商品’AA001’が二つ存在する場合に、それぞれのSQLを実行すると以下のようになります。左側の自己結合は検索されませんが、右側の分析ファンクションは検索されています（赤字の部分が異なるところになります）。
そのため、このようなデータが存在している場合には、以下のようにWINDOWING句でRANGEの範囲（現在行の１つ後の行から最後の行まで）を使用することで、列’売上日’の重複値の問題を解決することができます。WINDOWING句のRANGEをこのように使用すると、RANGEは同じデータを同一行として扱うので、現在行と同一データを対象外にするような使い方が可能になります（本当に、使い方によっていろいろなことが可能になります）。
（２）文字列の使用について
分析ファンクションといっても複雑なデータ分析だけではなく、文字列などに対する処理としても使用することができるので、ここでは文字列に対する分析ファンクションの使用について説明します。
以下のサンプルプログラムは、第24回で説明したLISTAGGファンクションのような文字列を結合する処理になり、分析ファンクションとしても使用することが可能です（私には、先頭以外の文字列を削除する分析ファンクションの使い方が思いつかなかったので、削除ルーチンは先頭の文字列だけを削除するようにしています）。
このファンクション’mylist’を分析ファンクションと集計ファンクションとして実行すると、それぞれ以下のようになります。ただし、このように分析ファンクションのORDER BY句の列が文字列データ型では、WINDOWING句のRANGEに対して’n PRECEDING’と’n FOLLOWING’は指定できないので注意してください。
3. おわりに
今回はユーザー定義分析ファンクションについて説明しましたが、少しは参考になりましたでしょうか。これからも頑張りますのでよろしくお願いします。質問をお待ちしています（このような内容を取り上げて欲しいというご要望などもお待ちしております）。
それでは、次回まで、ごきげんよう。