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

この写真も、先回に続き、鳥の写真を撮るのに凝り始めたという知人の方から頂いたものです。たぶん白鷺だと思いますが、電線の上に止まっているところをきれいに撮っています。よく田んぼとかで白鷺を見かけることはありますが、電線に乗ることもあるんですね。

 そういえば、先日これはおもしろいと知人に紹介されたドラマで「夢をかなえるゾウ」のスペシャル男の成功編というのをDVDを借りて見ました。その話では人間の体にゾウの鼻、4本の腕を持ったインドのガネーシャという神様が、主人公の男性（小栗旬）に与える様々な課題を実践していく中で、人生をよい方向へと切り開いていく様子が描かれていますが、笑いあり、感動ありの内容でとてもよかったです。そこで出される課題は、以下のようなものだったと思います。（覚えている範囲内で）
・靴を磨く
・コンビニで（お釣りを）募金する
・食事は腹八分目にする
・人の欲しがる物を先取りしてあげる
・会った人を笑わせる
・トイレ掃除をする
・まっすぐ帰宅する
・その日がんばった自分を褒める
・一日何かをやめてみる
・毎朝、全身鏡を見て身なりを整える
・夢を楽しく想像する
・運が良いと口に出して言う
・明日の準備をする
・身近にいる大切な人を喜ばせる
・人のいい所を見つけ褒める
・人の長所を盗む
・サプライズをして喜ばせる

全ての課題に取り組んで、最後には、神様がそばにいなくてもやって行けるまで、どんどん主人公が成長していくという内容でしたが、それぞれの課題に対して、主人公が素直に取り組むのがとてもえらいと思いながら見ていました。
そのドラマの中で、仕事の会議中、最悪のように思える状況で、「運がいい」と口に出して言い、実際にそう思うことで発想を転換できて、厳しいと思っていた状況がよい方向に変わっていくシーンがありました。フィクションだからな！という思いが湧きつつも、確かにやってみる価値はあるなと考えさせられるところもいろいろとありました。

家族で一緒にこのビデオを見たので、妻や子供も、この中で出された課題を紙に書きとめて、自分も実践しようと意気込んでいましたが、さて、やっているのやら。

本題に移りましょうか。今回は、FIRST/LAST関数について取り上げてみたいと思います。

＜本日の題材＞
FIRST/LAST関数 (ORACLE)

以前、順位付の関数として、RANK関数やDENSE_RANK関数を取り上げたことがありましたが、本日は、DENSE_RANK関数と一緒に使うかたちで使用するFIRST関数、LAST関数を取り上げてみたいと思います。（ORACLEの環境）

構文は、以下のようになります。

グループ関数 KEEP
　( DENSE_RANK FIRST/LAST ORDER BY ソート列１,[ソート列２,・・・] )
      OVER( [ PARTITION BY 項目１,[項目２,・・・]] )

以前にDENSE_RANK関数を取り上げたときに使ったのが商品マスタでしたので、今回もそれを使ってみます。今回はORACLEで試します。

CREATE TABLE syomst(
  syo_cd   VARCHAR2(10)
, syo_name VARCHAR2(20)
, bnrui    VARCHAR2(20)
, price    NUMBER(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);
COMMIT;

ここで、商品の分類毎の値段が最も高いものと低いものを出す場合、金額だけ抽出すればよいのであれば、普通は以下のようにします。

SELECT bnrui, MIN(price), MAX(price)
  FROM syomst
 GROUP BY bnrui
 ORDER BY bnrui;

ここで、商品の分類毎の値段が最も高いものと低いものの金額とともに商品も抽出したいという場合には、例えば以下のようにすることができます。

SELECT
  bnrui AS 分類
, MIN(syo_name) KEEP (DENSE_RANK FIRST ORDER BY price) AS 商品名_最安
, MIN(price) KEEP (DENSE_RANK FIRST ORDER BY price) AS 最安価格
, MIN(syo_name) KEEP (DENSE_RANK LAST ORDER BY price) AS 商品名_最高
, MIN(price) KEEP (DENSE_RANK LAST ORDER BY price) AS 最高価格
  FROM syomst
 GROUP BY bnrui
 ORDER BY bnrui;

実際のデータを確認してみると、

SELECT
  bnrui AS 分類
, syo_name AS 商品名
, price AS 価格
  FROM syomst
 ORDER BY bnrui, price;

確かに、各分類の最も安い価格のものと高い価格のものが抽出されていたことが確認できます。

次に、各商品の金額を表示するとともに、各分類の最も安い金額と高い金額を同じ行で表示するということを行ってみます。先ほどの、FIRST/LAST関数に、OVER(PARTITION BY )句を使用することで可能になります。

SELECT
  bnrui AS 分類
, syo_cd AS 商品CD
, syo_name AS 商品名
, price AS 価格
, MIN(price) KEEP (DENSE_RANK FIRST ORDER BY price)
   OVER(PARTITION BY bnrui) AS 分類の最安価格
, MIN(price) KEEP (DENSE_RANK LAST ORDER BY price)
   OVER(PARTITION BY bnrui) AS 分類の最高価格
  FROM syomst
 ORDER BY bnrui, syo_cd;

それぞれの商品の価格を、同じ分類の最も安い金額と高い金額と比較して見ることができるようになりました。

今日は以上まで

にほんブログ村