SQLピボット・プロシージャー
ピボットの基本概念は、行の値が列の値に変換されるということです。私は、DB2 にピボット・プロシージャーがあればいいのにと、よく考えていました。しかしその日が来るまで、あなたにも役立つと思われるような次善策で、間に合わせなくてはならないでしょう。
次善策は、任意の表の任意の列を転回する際に使用できる、ストアード・プロシージャーの形式で行います。ストアード・プロシージャーについてお話しする前に、簡単なピボットの例を見てみましょう。
これらの例は DB2 サンプル・データベースで SALES 表を使用しています。DB2 サンプル・データベースをシステムで作成するには、SQL ストアード・プロシージャー CREATE_SQL_SAMPLE を呼び出し、作成したいスキーマの名前を指定します。
単純なピボット
サンプル・コード 1 は、販売員別売上の集計を選択する select ステートメントと結果セットを示しています。
サンプル・コード 2 に示すように、このデータを転回すると一行になります。
残念ながら、サンプル・コード 3 に示すように、その一行の結果セットを生成するのに必要な select ステートメントは相当な作業です。
この select ステートメントをすぐに詳しく調べますが、まずはもう少し複雑な (また、より意味がある) 例を見てみましょう。
より複雑なピボット
サンプル・コード 4 は、拡張された select ステートメントとサンプル・コード 1 で使用された結果セットを示しています。地域ごとに小計を追加しています。
サンプル・コード 5 に示すように、このデータの転回には実にメリットがあります。
また、サンプル・コード 6 に示すように、必要な select ステートメントはオリジナルとさほど違いません。
問題は
サンプル・コード 3 と 6 で select ステートメントを調べてみると、SALES_PERSON 列で考えられる、それぞれの値について列が定義されていることがわかります。ステートメントを実行すると、SALES 列の値は SALES_PERSON 列の値に基づいて、該当の列 (GOUNOT、LEE または LUCCHESSI) に累積されます。
しかし、4 人目の販売員が登場するとどうなるでしょうか。そうです。select ステートメントを変更し、新しい販売員の列を追加しなければならなくなります。
個人的には、メンテナンスフリーのソリューションが希望です。
ストアード・プロシージャーを使う
ストアード・プロシージャー DO_PIVOT は、以下の 6 つのパラメーターを許可します。
- スキーマの名前 (ライブラリー)
- 表の名前
- 転回する列の名前
- ピボットのために集約する列の名前
- 実行する集約関数 (デフォルトは SUM)
- グループ化する列の名前
(デフォルトではヌルになり、それが一行の結果セットになります)
以下のプロシージャー呼び出しは、サンプル・コード 2 に示す結果セットを提供します。
また、以下のプロシージャー呼び出しは、サンプル・コード 5 に示す結果セットを提供します。
DO_PIVOT プロシージャー
DO_PIVOT プロシージャーは必要な select ステートメントを動的に構築し、必要な結果セットを生成します。
まず、プロシージャーは、要求されたピボット列のディスティンクト値のリストを生成します。動的な select ステートメントが構築され、生成されたリストの各ディスティンクト値の列定義が追加されます。
最後に、プロシージャーは生成されたステートメントを実行し、結果セットを返します。
サンプル・コード 7 は、 DO_PIVOT プロシージャーの作成に必要なコードを示しています。スキーマ名をコピー・アンド・ペーストし、変更だけして、ステートメントを実行します。
ちょっと遊ぶのによいもの
これが何度か便利だと思ったプロシージャーです。あなたにも役に立つと期待しています。しかし、DB2 でピボット関数があれば良かったのにと心底思っています。
Paul Tuohy: iSeries コンサルティング会社である ComCon の CEO であり、RPG & DB2 Summit 会議を主催する System i Developer の共同設立者でもある。
COMMON 会議で定期的に講演する受賞歴のある弁士で、「Re-engineering RPG Legacy Applications」、「The Programmers Guide to iSeries Navigator」、また「iSeries Navigator for Programmers」という自己学習コースの著者。Paul への質問やコメントは IT Jungle Contact ページ経由で Ted Holt にお願いします。
