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

写真は、群馬県の榛名山の写真です。群馬県の高崎にいる知人が写真を送ってくれました。
群馬県には、赤城山、榛名山、妙義山の上毛三山と言われる有名な山があり、榛名山は中央に位置する火山であり、複数の山で構成される山体の名称です。山頂には、カルデラ湖である榛名湖と榛名富士があり、それらを複数の山が囲む形になっています。火山としては、5世紀から6世紀の頃に、大規模な噴火が度々発生していたとの記録があります。
以前は関越自動車道に乗ることがよくあって、榛名山、赤城山を始めたくさんの山々の自然を見ながらの走行はとても気分がいいものですね。榛名山の側には伊香保温泉もあるということなので、いつか行けたらいいと思います。

＜本日の題材＞
FIRST_VALUE / LAST_VALUE（SQL Server）

かなり以前のブログで、ORACLEでの FIRST / LAST関数について取り上げましたが、SQL Serverにおいても同様の機能があるのを見つけましたので、試してみたいと思います。なお、SQL Serverについては、OVER句についても取り上げたことがありますが、今回の FIRST_VALUE / LAST_VALUE関数は、OVER句と一緒に使用するかたちになります。

例)
以前ブログで取り上げたときと同様に、商品マスタに登録されたデータについて、分類ごとの値段が最も高いものと低いものを、商品名などの情報とともに出力するということをしてみたいと思います。

CREATE TABLE syomst(
 syo_cd   NVARCHAR(10)
, syo_name NVARCHAR(20)
, bnrui    NVARCHAR(20)
, price    NUMERIC(10)
, CONSTRAINT PK_syomst PRIMARY KEY (syo_cd));

データを登録します。

INSERT INTO syomst VALUES('A0001', 'チョコレート', 'お菓子', 120);
INSERT INTO syomst VALUES('B0001', 'りんご', '果物', 100);
INSERT INTO syomst VALUES('C0001', 'キャベツ', '野菜', 160);
INSERT INTO syomst VALUES('A0002', 'ビスケット', 'お菓子', 200);
INSERT INTO syomst VALUES('B0002', '桃', '果物', 160);
INSERT INTO syomst VALUES('C0002', 'にんじん', '野菜', 150);
INSERT INTO syomst VALUES('A0003', 'ガム', 'お菓子', 100);
INSERT INTO syomst VALUES('B0003', 'みかん', '果物', 80);
INSERT INTO syomst VALUES('C0003', 'じゃがいも', '野菜', 100);
INSERT INTO syomst VALUES('A0004', 'スナック', 'お菓子', 140);
INSERT INTO syomst VALUES('B0004', '梨', '果物', 120);
INSERT INTO syomst VALUES('C0004', '玉ねぎ', '野菜', 150);

それでは、商品マスタのデータを抽出するとともに、FIRST_VALUE、LAST_VALUEを使用して、同じ商品の分類の値段が最も低いものと高いものの商品名も右側に並べて抽出してみます。

SELECT bnrui, syo_cd, syo_name, price,
FIRST_VALUE(syo_name) OVER
(PARTITION BY bnrui ORDER BY price ASC 
 ROWS UNBOUNDED PRECEDING
 ) AS 分類で最も安い商品名,
LAST_VALUE(syo_name) OVER
(PARTITION BY bnrui ORDER BY price ASC
 RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
 ) AS 分類で最も高い商品名
FROM dbo.syomst
ORDER BY bnrui, price, syo_cd;

商品マスタの各項目の値が左側に、その右に、分類で最も安い価格の商品の名前と、分類で最も高い価格の商品の名前を正しく抽出することができました。

さて、FIRST_VALUE、LAST_VALUE を使う場合の構文は、以下になります。

FIRST_VALUE / LAST_VALUE ( [scalar_expression ] ) 
[ IGNORE NULLS | RESPECT NULLS ]
OVER ( [partition_by_clause] order_by_clause [rows_range_clause] )

上記のパラメータについては、
scalar_expression：
返される値：今回は、商品名称を表示したいので、syo_name。

IGNORE NULLS - パーティションの最初の値の計算時に、データセット内の null 値を無視します。
RESPECT NULLS - パーティションの最初の値の計算時に、データセット内の null 値を使用します。
（今回は商品名称はNULLデータがなく関係がないので指定してはいません）

partition_by_clause は、指定した項目でパーティションに分割して、パーティション毎の結果を出すときに使用しますが、今回は、分類(bunrui)毎に抽出するため、設定しています。
order_by_clause は、今回は価格(price)の最も安い価格と最も高い価格を出すために使用します。

rows_range_clause は始点と終点を指定することによって、パーティション内の行をさらに条件付けることが可能になります。
今回の例について説明すると、FIRST_VALUE句のほうで設定している「ROWS UNBOUNDED PRECEDING」は、分類毎の最初の行から開始することを指定しています。
LAST_VALUE句のほうの「RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING」は、現在の行の値から最後の行の値までということを表わしています。

注意点として、LAST_VALUEのときに、上記の範囲の設定を省略すると、結果が異なってきます。
試してみます。

SELECT bnrui, syo_cd, syo_name, price,
FIRST_VALUE(syo_name) OVER
(PARTITION BY bnrui ORDER BY price ASC 
 ) AS 分類で最も安い商品名,
LAST_VALUE(syo_name) OVER
(PARTITION BY bnrui ORDER BY price ASC
 ) AS 分類で最も高い商品名
FROM dbo.syomst
ORDER BY bnrui, price, syo_cd;

分類で最も安い商品名のほうは問題ないですが、分類で最も高い商品名の結果が、前回の結果と異なり、分類毎の最も高い価格の商品名ではなく、抽出した行の商品名が抽出されていることがわかります。これは、rows_range_clause のデフォルトの設定が、「RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW」ということで、分類毎の最初の行の値から現在の行の値までということになり、分類毎、price順にソートしているため、LAST_VALUEとしては、結局、現在の行の値になってしまっているということです。

そのため、LAST_VALUEを使用する場合には、ROWSやRANGEで、UNBOUNDED FOLLOWINGまでというように、範囲を適切に指定するようにしたほうがよいようですね。

今日は以上まで

にほんブログ村