ITコーディネータのシュウです。

自宅から会社に行く途中に道路のそばに咲いているポピーの花です。自転車で通るので、きれいに咲いているときには少し止まって眺めたりするこの頃ですが、若いときは、頭の中は仕事のことか、差し迫ったやるべきことなどでいっぱい（もしくはぼーっとしている？）で、見ていても記憶に残らないという感じだったと思います。妻にあそこにきれいな花が咲いているでしょう？と聞かれても、そうだっけ？という返事をするので、そういう感覚が欠落しているかわいそうな人だとあきれられていたものですが、少しずつ花などの自然も意識するようになってきました。

ちなみに、ポピーは色とりどりの花を咲かせるケシ科の植物の総称で、その実から採れる乳液には、入眠や麻痺の作用があることから、紀元前400年頃のギリシアでは麻酔薬や睡眠導入薬として用いられていたそうです。花言葉も「なぐさめ」「心の平静」「いたわり」「思いやり」などその効能にちなんだものが多くあり、相手をいたわる気持ちや、助けたい気持ちを表現するときによく贈られる花だそうです。

＜本日の題材＞
APPLY（SQL Server）

今回は、APPLYという演算子について取り上げてみます。
以前、テーブル値関数について記事として載せたことがありますが、あるテーブルのデータの値と、その値によって取得されるテーブル値関数の結果を組み合わせて抽出したいという場合に、APPLY という演算子を使用することができるようです。（SQL Server 2005から）
SQLのFROM句の後に、まずテーブルを記載し、「CROSS APLLY」、もしくは「OUTER APPLY」の後側にテーブル値関数を指定するかたちになり、SELECT のほうではテーブルの列とテーブル値関数の結果の列を含めるかたちになります。
私も今まであまり使ったことがなかったのですが、今回、試してみようと思います。

例）
使用するテーブルは、以前ブログで使ったことのある、日本の都道府県、山、川、湖などの大きさや高さや長さなどをデータにしたランキングのデータを使ってみたいと思います。
今回使用するテーブル（「ランキング」「ランキング区分」）の定義は、下記のような設定。

データを抽出してみると、
「ランキング区分」テーブルは、

「ランキング」テーブルは、

今回使用するテーブル値関数は、「区分NO」の値からランキングテーブルのデータを抽出する簡単なものです。定義は以下：

CREATE FUNCTION [dbo].[ufn_ランク上位取得]
(
         @p区分NO DECIMAL(4,0)
) RETURNS @ランク上位 TABLE (
         区分名 VARCHAR(40),
         ランキング DECIMAL(3,0),
         名称 VARCHAR(40),
         数値 DECIMAL(8,1)
)
AS
BEGIN
         INSERT @ランク上位
         SELECT
                   K.区分名
                  ,RANK() OVER (ORDER BY R.数値 DESC)
                  ,R.名称
                  ,R.数値
         FROM dbo.ランキング R
         JOIN dbo.ランキング区分 K ON R.区分NO = K.区分NO
         WHERE K.区分NO = @p区分NO;
 
        RETURN;
END;

このテーブル値関数にパラメータを直接指定して抽出すると、

SELECT * FROM dbo.ufn_ランク上位取得(1)
ORDER BY ランキング;

これを、「ランキング区分」テーブルの「区分NO」をテーブル値関数にセットして、「区分NO」毎にランキング情報を抽出するように、APPLY句を使用すると、以下のように抽出できます。

SELECT K.区分NO, K.区分名, K.単位, R.ランキング, R.名称, R.数値
  FROM dbo.ランキング区分 K
  CROSS APPLY dbo.ufn_ランク上位取得(K.区分NO) AS R
ORDER BY K.区分NO, R.ランキング;

ここで、「ランキング区分」テーブルにあっても、テーブル値関数で結果のないものは抽出されないのですが、外部結合のように、上記のようなデータも左側のランキング区分の情報は表示し、テーブル値関数側の結果は null で表示したい場合には、「OUTER APPLY」演算子を使用します。

SELECT K.区分NO, K.区分名, K.単位, R.ランキング, R.名称, R.数値
  FROM dbo.ランキング区分 K
  OUTER APPLY dbo.ufn_ランク上位取得(K.区分NO) AS R
ORDER BY K.区分NO, R.ランキング;

 上記のように、「区分NO」=2, 3 のテーブル値関数では抽出されないデータも表示されることが確認できます。

ちなみに、APPLY演算子の右側は必ずテーブル値関数である必要はなく、上記の内容は、以下のようなSQLでも抽出できます。（CROSS APPLYの例）

SELECT K.区分NO, K.区分名, K.単位, RK.ランキング, RK.名称, RK.数値
  FROM dbo.ランキング区分 K
  CROSS APPLY
   (SELECT
                   K.区分名
                  ,RANK() OVER (ORDER BY R.数値 DESC) AS ランキング
                  ,R.名称
                  ,R.数値
         FROM dbo.ランキング R
         WHERE R.区分NO = K.区分NO) AS RK
ORDER BY K.区分NO, RK.ランキング;

今まではあまり使ったことはなかったのですが、今後は、使えるときもあるのではないかと思います。

今日は以上まで

にほんブログ村